Query, query.....

[Guest]

Hi everybody,
I wonder if anybody could give any solution with this problem that I have
right here:
I have two different tables that contain similiar type of information:
Table #1:
Acount # Amount
001 $25
002 $20
004 $50

Table #2:
Acount # Amount
001 $25
002 $100
003 $10

I wonder if anyone could tell how to make a query that be able to show
records that are the same (001-$25) on the top of the list and records that
have the same Account# but different Amount(002-$100/$20) and records that
either only in one table to be at the bottom of the list? It is not
neccessarily to be in the same query/table, my purpose is just to have them
separated so I could do data analyze easier!!!
I've made a query that able to show all of the records that have exact
Account# and Amount, but I could make those that are different of show only
in one table?
And I've also try to use the first query as FILTER to filter out those that
have exact same Account# and Amount, but Access wouldn't let me do that!!!

I really need help on this ASAP!
Any info/resource to solve this is truly appreciated!
Thanks in advance
Best Regard,
Neon520

 

[Steve Schapel]

Neon,

Something like this (untested!)...

SELECT Table1.Account, Table1.Amount, Table2.Account, Table2.Amount,
Table1.Amount=Table2.Amount As AreTheyTheSame
FROM Table1 INNER JOIN Table2 On Table1.Account = Table2.Account
UNION SELECT Table1.Account, Table1.Amount, Table2.Account,
Table2.Amount, 1 As AreTheTheSame
FROM Table1 LEFT JOIN Table2 On Table1.Account = Table2.Account
WHERE Table2.Account Is Null
UNION SELECT Table1.Account, Table1.Amount, Table2.Account,
Table2.Amount, 1 As AreTheTheSame
FROM Table2 LEFT JOIN Table1 On Table2.Account = Table1.Account
WHERE Table1.Account Is Null
ORDER BY AreTheyTheSame, Table1.Account, Table2.Account

By the way, as an aside, it is not a good idea to use a # as part of the
name of a field or control or table or other database object.