Query combining the tables

S

subs

cons dcity dst dzip ocity ost ozip
e f jk 7789 fg cd 989
c o lk 970 sf9 cdf 9890
cdd yf mh 979 hgg mkhi 7699


cons dcity dst dzip ocity ost ozip
a d ak 560 b c 789
b e ck 869 de ef 970
c o lk 970 bh mk 976

the output qery should combine data from both tables, should include
all rows from both tables and should have the following fields.------
the data should be combined from both tables across consignee, dcity,
dstate and dzip fields

cons
dcity
dst
dzip
table1.ocity
table1.ost
table1.ozip
table2.ocity
table2.ost
table2.ozip

for example c,o, lk,970( consignee, dcity, dst,dzip) row is common in
both tables . So in the output query- one of the rows will be
c o lk 970 sf9 cdf 9890 bh mk 976

the other rows in the o/p will be
e f jk 7789 fg cd 989 - - -
b e ck 869 - - - de ef 970

and so on until all the records in both tables are accounted for.

Pls help with such a query

Thanks much for the assistance
 
G

Gina Whipp

S

subs

Subs,

You got three great answers back on 12.27.2009 when you originally posted
this question.  Did those answers not work for you?  Perhaps the results
weren't as expected?  OR maybe you didn't see the replies?  Just in case...

http://groups.google.com/group/microsoft.public.access.queries/browse...

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm
Click to expand...
I did not get the answers i wanted. i want the output with the fields
exactly as i posted below. thanks
 
G

Gina Whipp

Subs,

Did you try this one? (See following), it should give you exactly what you
requested...

That is the job of a full outer join, not directly supported by Jet. One
indirect way to solve it is :

Make a query which will return all the desired 'key' (once, no dup) , here,
something like:


SELECT consignee FROM table1
UNION
SELECT consignee FROM table2


Call this query q1.


Next, in a second query, bring q1, and table1. Link them with an outer join
(all rows from q1, matching ones from tabel1) over the common field
consignee.
Bring table2 to the query, also link q1 and table2 with an outer join (all
rows again from q1, matching ones from table2) again over their common
field, consignee.


In the grid, bring the required fields (consignee from q1, other fields from
table1 or table2, as you want them).


Vanderghast, Access MVP


--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Subs,

You got three great answers back on 12.27.2009 when you originally posted
this question. Did those answers not work for you? Perhaps the results
weren't as expected? OR maybe you didn't see the replies? Just in case...

http://groups.google.com/group/microsoft.public.access.queries/browse...

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm
Click to expand...
I did not get the answers i wanted. i want the output with the fields
exactly as i posted below. thanks
 
S

subs

Subs,

Did you try this one? (See following), it should give you exactly what you
requested...

That is the job of a full outer join, not directly supported by Jet. One
indirect way to solve it is :

Make a query which will return all the desired 'key' (once, no dup) , here,
something like:

SELECT consignee FROM table1
UNION
SELECT consignee FROM table2

Call this query q1.

Next, in a second query, bring q1, and table1. Link them with an outer join
(all rows from q1, matching ones from tabel1)  over the common field
consignee.
Bring table2 to the query, also link q1 and table2 with an outer join (all
rows again from q1, matching ones from table2) again over their common
field, consignee.

In the grid, bring the required fields (consignee from q1, other fields from
table1 or table2, as you want them).

Vanderghast, Access MVP

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm






I did not get the answers i wanted. i want the output with the fields
exactly as i posted below. thanks
Click to expand...

when i bring the required fields onto the grid, i have to bring the
dcity,dstate,dzip twice because this row below does not come up as
required (where there is commonality in both the tables)
c o lk 970 sf9 cdf 9890 bh mk 976

all i need is the following fields in the o/p just once and cons ,
dcity, dst, dzip cannot be empty in any of the rows.

cons
 
G

Gina Whipp

Subs,

Please post the SQL of your query here.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Subs,

Did you try this one? (See following), it should give you exactly what you
requested...

That is the job of a full outer join, not directly supported by Jet. One
indirect way to solve it is :

Make a query which will return all the desired 'key' (once, no dup) ,
here,
something like:

SELECT consignee FROM table1
UNION
SELECT consignee FROM table2

Call this query q1.

Next, in a second query, bring q1, and table1. Link them with an outer
join
(all rows from q1, matching ones from tabel1) over the common field
consignee.
Bring table2 to the query, also link q1 and table2 with an outer join (all
rows again from q1, matching ones from table2) again over their common
field, consignee.

In the grid, bring the required fields (consignee from q1, other fields
from
table1 or table2, as you want them).

Vanderghast, Access MVP

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm






I did not get the answers i wanted. i want the output with the fields
exactly as i posted below. thanks
Click to expand...

when i bring the required fields onto the grid, i have to bring the
dcity,dstate,dzip twice because this row below does not come up as
required (where there is commonality in both the tables)
c o lk 970 sf9 cdf 9890 bh mk 976

all i need is the following fields in the o/p just once and cons ,
dcity, dst, dzip cannot be empty in any of the rows.

cons
 
S

subs

Subs,

Did you try this one? (See following), it should give you exactly what you
requested...

That is the job of a full outer join, not directly supported by Jet. One
indirect way to solve it is :

Make a query which will return all the desired 'key' (once, no dup) , here,
something like:

SELECT consignee FROM table1
UNION
SELECT consignee FROM table2

Call this query q1.

Next, in a second query, bring q1, and table1. Link them with an outer join
(all rows from q1, matching ones from tabel1)  over the common field
consignee.
Bring table2 to the query, also link q1 and table2 with an outer join (all
rows again from q1, matching ones from table2) again over their common
field, consignee.

In the grid, bring the required fields (consignee from q1, other fields from
table1 or table2, as you want them).

Vanderghast, Access MVP

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm






I did not get the answers i wanted. i want the output with the fields
exactly as i posted below. thanks
Click to expand...

SELECT table1.*, table2.ocity, table2.ostate, table2.ozip
FROM (table1 LEFT JOIN query18 ON table1.consignee =
query18.consignee) LEFT JOIN table2 ON query18.consignee =
table2.consignee;

query18 is query1 as suggested by you
 
G

Gina Whipp

Subs,

Does Query18 look like the below? Please provide the SQL of that query?

SELECT consignee, ocity, ostate, ozip FROM table1
UNION
SELECT consignee, ocity, ostate, ozip FROM table2

In the query below you should have only added Table1. It appears as if you
added Table2 as well.
--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Subs,

Did you try this one? (See following), it should give you exactly what you
requested...

That is the job of a full outer join, not directly supported by Jet. One
indirect way to solve it is :

Make a query which will return all the desired 'key' (once, no dup) ,
here,
something like:

SELECT consignee FROM table1
UNION
SELECT consignee FROM table2

Call this query q1.

Next, in a second query, bring q1, and table1. Link them with an outer
join
(all rows from q1, matching ones from tabel1) over the common field
consignee.
Bring table2 to the query, also link q1 and table2 with an outer join (all
rows again from q1, matching ones from table2) again over their common
field, consignee.

In the grid, bring the required fields (consignee from q1, other fields
from
table1 or table2, as you want them).

Vanderghast, Access MVP

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm






I did not get the answers i wanted. i want the output with the fields
exactly as i posted below. thanks
Click to expand...

SELECT table1.*, table2.ocity, table2.ostate, table2.ozip
FROM (table1 LEFT JOIN query18 ON table1.consignee =
query18.consignee) LEFT JOIN table2 ON query18.consignee =
table2.consignee;

query18 is query1 as suggested by you
 
S

subs

Subs,

Does Query18 look like the below?  Please provide the SQL of that query?

SELECT consignee, ocity, ostate, ozip FROM table1
UNION
SELECT consignee, ocity, ostate, ozip FROM table2

In the query below you should have only added Table1.  It appears as ifyou
added Table2 as well.
--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm














SELECT table1.*, table2.ocity, table2.ostate, table2.ozip
FROM (table1 LEFT JOIN query18 ON table1.consignee =
query18.consignee) LEFT JOIN table2 ON query18.consignee =
table2.consignee;

query18 is query1 as suggested by you
Click to expand...

SELECT consignee FROM table1
UNION SELECT consignee FROM table2;

This is what i gave in query18. Can you give me query 18 and Sql query
for the final o/p as well. What fields should i drag in the final
query.
 
G

Gina Whipp

Subs,

Please copy and paste this into your Query18...

SELECT consignee, ocity, ostate, ozip FROM table1
UNION
SELECT consignee, ocity, ostate, ozip FROM table2;

Make sure it works and return to report results...

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Subs,

Does Query18 look like the below? Please provide the SQL of that query?

SELECT consignee, ocity, ostate, ozip FROM table1
UNION
SELECT consignee, ocity, ostate, ozip FROM table2

In the query below you should have only added Table1. It appears as if you
added Table2 as well.
--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm














SELECT table1.*, table2.ocity, table2.ostate, table2.ozip
FROM (table1 LEFT JOIN query18 ON table1.consignee =
query18.consignee) LEFT JOIN table2 ON query18.consignee =
table2.consignee;

query18 is query1 as suggested by you
Click to expand...

SELECT consignee FROM table1
UNION SELECT consignee FROM table2;

This is what i gave in query18. Can you give me query 18 and Sql query
for the final o/p as well. What fields should i drag in the final
query.
 
S

subs

Subs,

Please copy and paste this into your Query18...

SELECT consignee, ocity, ostate, ozip FROM table1
UNION
SELECT consignee, ocity, ostate, ozip FROM table2;

Make sure it works and return to report results...

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm











SELECT consignee FROM table1
UNION SELECT consignee FROM table2;

This is what i gave in query18. Can you give me query 18 and Sql query
for the final o/p as well. What fields should i drag in the final
query.
Click to expand...

CAN YOU TELL ME HOW SHOULD I JOIN - THREE TABLES ON THE GRID- AND
OUTER JOIN? WHICH JOIN ? cAN YOU GIVE ME FINAL SQL QUERY? THANKS
 
G

Gina Whipp

Subs,

Now, now... no yelling... You are not joining three tables anywhere. First
a union query with the two tables then the next query you would only need
one table and your union query.

Query1..

SELECT consignee, ocity, ostate, ozip FROM table1
UNION
SELECT consignee, ocity, ostate, ozip FROM table2;


UNTESTED Final Query....

SELECT Table1.consignee, Table1.ocity, Table1.ostate, Table1.ozip,
Query1.ocity, Query1.ostate, Query1.ozip
FROM Query1 INNER JOIN Table1 ON Query1.consignee = Table1.consignee
GROUP BY Table1.consignee, Table1.ocity, Table1.ostate, Table1.ozip,
Query1.ocity, Query1.ostate, Query1.ozip;

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Subs,

Please copy and paste this into your Query18...

SELECT consignee, ocity, ostate, ozip FROM table1
UNION
SELECT consignee, ocity, ostate, ozip FROM table2;

Make sure it works and return to report results...

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm











SELECT consignee FROM table1
UNION SELECT consignee FROM table2;

This is what i gave in query18. Can you give me query 18 and Sql query
for the final o/p as well. What fields should i drag in the final
query.
Click to expand...

CAN YOU TELL ME HOW SHOULD I JOIN - THREE TABLES ON THE GRID- AND
OUTER JOIN? WHICH JOIN ? cAN YOU GIVE ME FINAL SQL QUERY? THANKS
 
S

subs

Subs,

Now, now... no yelling...  You are not joining three tables anywhere.  First
a union query with the two tables then the next query you would only need
one table and your union query.

Query1..

SELECT consignee, ocity, ostate, ozip FROM table1
UNION
SELECT consignee, ocity, ostate, ozip FROM table2;

UNTESTED Final Query....

SELECT Table1.consignee, Table1.ocity, Table1.ostate, Table1.ozip,
Query1.ocity, Query1.ostate, Query1.ozip
FROM Query1 INNER JOIN Table1 ON Query1.consignee = Table1.consignee
GROUP BY Table1.consignee, Table1.ocity, Table1.ostate, Table1.ozip,
Query1.ocity, Query1.ostate, Query1.ozip;

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm










CAN YOU TELL ME HOW SHOULD I JOIN - THREE TABLES ON THE GRID- AND
OUTER JOIN?  WHICH JOIN ? cAN YOU GIVE ME FINAL SQL QUERY? THANKS
Click to expand...

no gina it is not working- sorry but i am not getting what i need. i
would need all the data from both the tables- but only use only one
table?
 
G

Gina Whipp

Subs,

I have removed the Group By... see if this works...

SELECT Table1.consignee, Table1.ocity, Table1.ostate, Table1.ozip,
Query1.ocity, Query1.ostate, Query1.ozip
FROM Query1 INNER JOIN Table1 ON Query1.consignee = Table1.consignee;

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Subs,

Now, now... no yelling... You are not joining three tables anywhere. First
a union query with the two tables then the next query you would only need
one table and your union query.

Query1..

SELECT consignee, ocity, ostate, ozip FROM table1
UNION
SELECT consignee, ocity, ostate, ozip FROM table2;

UNTESTED Final Query....

SELECT Table1.consignee, Table1.ocity, Table1.ostate, Table1.ozip,
Query1.ocity, Query1.ostate, Query1.ozip
FROM Query1 INNER JOIN Table1 ON Query1.consignee = Table1.consignee
GROUP BY Table1.consignee, Table1.ocity, Table1.ostate, Table1.ozip,
Query1.ocity, Query1.ostate, Query1.ozip;

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm










CAN YOU TELL ME HOW SHOULD I JOIN - THREE TABLES ON THE GRID- AND
OUTER JOIN? WHICH JOIN ? cAN YOU GIVE ME FINAL SQL QUERY? THANKS
Click to expand...

no gina it is not working- sorry but i am not getting what i need. i
would need all the data from both the tables- but only use only one
table?
 
S

subs

Subs,

I have removed the Group By... see if this works...

SELECT Table1.consignee, Table1.ocity, Table1.ostate, Table1.ozip,
Query1.ocity, Query1.ostate, Query1.ozip
FROM Query1 INNER JOIN Table1 ON Query1.consignee = Table1.consignee;

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm









no gina it is not working- sorry but i am not getting what i need.  i
would need all the data from both the tables- but only use only one
table?
Click to expand...

With this i am getting only table1 records- i am not getting any
table2 records at all - I need all records from both tables- if they
match- then in same row, if not then blanks in that particular section
of the row.
 
G

Gina Whipp

Subs,

You are getting both tables they are in the Union query. Might also help if
I do a LEFT JOIN, I must be tired!


Query1..

SELECT consignee, ocity, ostate, ozip, "Table1" AS WhichTable FROM table1
UNION
SELECT consignee, ocity, ostate, ozip, "Table2" FROM table2;


UNTESTED Final Query....

SELECT Table1.consignee, Table1.ocity, Table1.ostate, Table1.ozip,
Query1.ocity, Query1.ostate, Query1.ozip, Query1.WhichTable
FROM Query1 LEFT JOIN Table1 ON Query1.consignee = Table1.consignee;

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Subs,

I have removed the Group By... see if this works...

SELECT Table1.consignee, Table1.ocity, Table1.ostate, Table1.ozip,
Query1.ocity, Query1.ostate, Query1.ozip
FROM Query1 INNER JOIN Table1 ON Query1.consignee = Table1.consignee;

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm









no gina it is not working- sorry but i am not getting what i need. i
would need all the data from both the tables- but only use only one
table?
Click to expand...

With this i am getting only table1 records- i am not getting any
table2 records at all - I need all records from both tables- if they
match- then in same row, if not then blanks in that particular section
of the row.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query needed 4
deleting duplicates but with a condition 5
Pls help with SQL query 12
SQL 2
SQL 1
Query Help 2
Outlook connector crashes outlook 0
Outlook crashes with Outlook connector 2002 0

Top