Query needed

[subs]

Table I

consignee dcity dstate dzip ocity ostate ozip
A d ak 560 b c 789
B e ck 869 de ef 970
C o lk 970 bh mk 975

table 2

consignee dcity dstate dzip ocity ostate ozip
A d ak 560 bl ct 7891
C o lk 970 bhe mtk 9751
F g mk 1075 hi lt 965

I have two tables as above. I need a query which can list all the
records from both the tables by combining the consignee, dcity, dstate
and dzip fields. I need the data in the following format---- Covering
all the records even if they are absent in one of the tables.------

table 1 table2
consignee dcity dstate dzip ocity ostate ozip ocity
ostate ozip
A d ak 560 b c
789 bl ct 7891
B e ck 869 de ef
970 - - -
C o lk 970 bh mk
975 bhe mtk 9751
F g mk
1075 hi lt
965

Pls help with query- i need the result in the above format.

Thanks much

[John W. Vinson]

Word wrap has made a total hash of your desired output. Please repost it with
semicolons rather than tabs or blanks delmiiting the fieldnames and values -
as it is I can't make any sense of it.
--

John W. Vinson [MVP]

[access-programmer via AccessMonster.com]

SELECT Table1.consignee, Table1.dcity, Table1.dstate, Table1.dzip, Table1.
ocity, Table1.ostate, Table1.ozip, Table2.ocity, Table2.ostate, Table2.ozip
FROM Table1 INNER JOIN Table2 ON (Table1.dzip = Table2.dzip) AND (Table1.
dstate = Table2.dstate) AND (Table1.dcity = Table2.dcity) AND (Table1.
consignee = Table2.consignee)
UNION

SELECT Table1.consignee, Table1.dcity, Table1.dstate, Table1.dzip, Table1.
ocity, Table1.ostate, Table1.ozip, Table2.ocity, Table2.ostate, Table2.ozip
FROM Table1 LEFT JOIN Table2 ON (Table1.dzip = Table2.dzip) AND (Table1.
dstate = Table2.dstate) AND (Table1.dcity = Table2.dcity) AND (Table1.
consignee = Table2.consignee)
WHERE Table2.consignee IS NULL and Table2.dcity IS NULL and Table2.dstate IS
NULL and Table2.dzip IS NULL

UNION
SELECT Table2.consignee, Table2.dcity, Table2.dstate, Table2.dzip, Table2.
ocity, Table2.ostate, Table2.ozip, Table2.ocity, Table2.ostate, Table2.ozip
FROM Table1 RIGHT JOIN Table2 ON (Table1.dzip = Table2.dzip) AND (Table1.
dstate = Table2.dstate) AND (Table1.dcity = Table2.dcity) AND (Table1.
consignee = Table2.consignee)
WHERE Table1.consignee IS NULL and Table1.dcity IS NULL and Table1.dstate IS
NULL and Table1.dzip IS NULL

--
<a href="http://www.access-programmers.com/">Access Programmers and
Consultants</a>

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200912/1

[vanderghast]

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

"subs" <subb...@gmail.com> wrote in message
news:d92bd214-a134-48fd...@g18g2000vbr.googlegroups.com...

[Ram]

On Dec 28 2009, 9:57 am, "vanderghast" <vanderghast@com> wrote:
> 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
>

> "subs" <subbu1...@gmail.com> wrote in message

Whenever the consignee fields are common in both tables, and if i get
all the fields from both tables into the grid in the final query, i
get dcity, dstate, dzips repeated for that particular field(where
consignee fields are common). how to give dcity, dstate, dzip not
repeated in one row. pls help thanks