Data with no relationship, fall-out data from query

[Scott]

Hello,

I need some help with a query to find records in my first data table that
don't match my second reference table. I'm trying to map account information
to account description but I don't know how to get the query to show new
transactions.

Example:

Table 1
Field 1 Field 2
AccountA $100
AccountB $200
AccountC $500

Table 2
AccountA Travel
AccountB Airfare

My Current Query Results From Tables 1 and 2 Below:
Travel $100
Airfare $200

My Desired Query Results From Tables 1 and 2 Below:
Travel $100
Airfare $200
AccountC $500 <---- How do I get this new transaction into my
query so I know to add it to my reference table? Since this new transaction
is "fall out" data, my totals don't match between Table 1 and my query
results.

Thanks for your help.

Scott

 

[Gina Whipp]

Scott,

Without seeing the query I would say you are using and INNERJoin, try
changing to a LEFT join.

--
Gina Whipp

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

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

 

[Dale Fye]

Scott,

If you want the results you mentioned, then try:

Select NZ(T2.Field2, T1.Field1) as Account, T1.Field2
FROM Table1 as T1 LEFT JOIN Table2 as T2
ON T1.Field1 = T2.Field1

If you really just want to identify Accounts (field1) from Table1 that don't
have a matching record in Table2 then try:

SELECT T1.Field1
FROM Table1 as T1 LEFT JOIN Table2 as T2
ON T1.Field1 = T2.Field1
WHERE T2.Field1 IS NULL

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.