Access Question

[Guest]

Can someone please help me. The trick to this one is eluding me.

I have two tables that I need to combine in access. Both tables contain
the same basic information, but I need to print a report to see what the
amount differences between the two tables. The only thing is that the
Entity, Group, and ID columns may differ in the number of items.

Table 1 and table 2 basic layouts:

Entity Group ID Amount1 Amount2 Amount3
00001 AAA Z 0000 0000 0000
00001 BBB Y 0000 0000 0000
00002 CCC X 0000 0000 0000
00002 DDD X 0000 0000 0000

Items in the ID field may be the same so I need combine each amount to total
amount for each for each ID for each entity and group. Once I get a total of
each ID in each entity and group, I need to combine the two tables in one
query that contains all of the data from each of the two initial tables.
This is my tricky part, how do I write a query to combine ALL of the data
from each table, even though the data in each table may differ? I can write
expressions in a report to give me differences, that is no problem, to give
me the amount differences are between the two tables for each entity, group,
and ID.

Basically, how do I combine like ID items in the same table and then combine
the two tables, with combined ID #s, where the end result query contains all
of the data from each table?

 

[Guest]

Oh yeah, here is what I need the final query result to look like:
Table1 Table1 Table1 Table2
Table2 Table2
Entity Group ID Amount1 Amount2 Amount3 Amount1 Amount2 Amt3
00001 AAA Z 0000 0000 0000 0000
0000 0000
00001 BBB Y 0000 0000 0000 0000
0000 0000
00002 AAA Z 0000 0000 0000 0000
0000 0000
00002 BBB Y 0000 0000 0000 0000
0000 0000
00002 CCC X 0000 0000 0000 0000
0000 0000

 

[Pieter Wijnen]

Not so tricky

SELECT A.Entity,A.Amount1, B.Amount1 FROM Table1 A Left Join Table2 B On
A.Entity = B.Entity
UNION
SELECT A.Entity,A.Amount1, B.Amount1 FROM Table1 A Right Join Table2 B On
A.Entity = B.Entity

HTH

Pieter

 

[Guest]

I appreciate your help, but it did not work. Let me see if I can explain
better. I need to join two tables which are formatted like the first table
below. The column headers are the same, but they have different numbers of
items and values. Eeach group may contain different numbers of Cusps, and
each table may or may not have items missing from the other table. I need
to joint the two tables together into one query lising each ACCT, GRP, CUSP,
and the values of BOTH tables as seen in the objective table below. Once I
get this result, then I can see what the differences are between the two
initial tables in my report.

Table 1

ACCT GRP CUSP ParValue Principal BookValue
0001 10 31359FWZ7 50,000 55,000 54,000
117497BG1 25,00 26,000 25,500
25 31282SDM1 56,000 56,500 56,500
25808MAA0 73,000 73,000 74,000
263687HH1 19,200 19,600 21,000
0002 10 96859FBN8 50,000 55,000 54,000
337497BG1 25,000 26,000 25,500
68 41582SPIC1 56,000 56,500 56,500
25808BWS2 73,000 73,000 74,000

Objective Table

Table1 Table1 Table1 Table2
Table2 Table2
ACCT GRP CUSP ParValue Principal BookValue ParValue Principal BookValue
0001 10 31359FWZ7 50,000 55,000 54,000 50,000 55,000
54,000
117497BG1 25,000 26,000 25,500 25,000 26,000 25,500
4485HGT45 47,000
48,000 47,800
25 31282SDM1 56,000 56,500 56,500 5,000 5,500
6,000
25808MAA0 73,000 73,000 74,000 73,000 73,000 74,000
263687HH1 19,200 19,600 21,000
0002 10 96859FBN8 50,000 55,000 54,000 50,000 55,000
54,000
337497BG1 25,000 26,000 25,500 25,000 26,000 25,500
68 41582SPIC1 56,000 56,500 56,500 19,200 19,600
21,000
25808BWS2 73,000 73,000 74,000 25,000 26,000 25,500
74 987TGR457 33,000 34,000 30,000
85 987TGR457 33,000
34,000 30,000
92 7802JEDH9 50,000 55,000 54,000 50,000 55,000
54,000

 

[Pieter Wijnen]

I just didn't give you the whole answer
The simplest way is to first create a outer join query for table1 & table2
linking all the relevant fields & adding the output fields in the normal way
(click on the joins to 'select all records from table1 & the matches from
table2')

now switch to SQL view & Copy the SQL into a new Query
Go back to the first query & change Left Join to Right join
in the 2'nd query remove the ; & add UNION
paste the modified SQL after the UNION & You should be up & running

HTH

Pieter