Query question about partial matching fields

[mmiram]

Hi all:

I have two tables...the first table has the following fields

Name - Name of the client
Count - # of Transactions
Total - Total Value of Transactions

The second table has the following fields

SalesName - Name of the client
Count - # of Transactions
Total - Total Value of Transactions

The two tables are joined on "Name" and "SalesName" (primary key).

This is the issue - there are some clients who fall in both the tables and some who fall in one of the two tables but not in both.

I need to write a query that compares both the tables and if the names match....takes the name...sums up the "count" and "total" fields in both the tables and places the new values in the resultant query as a record. At the same time, if the names don't match, it should just copy the records from the respective tables into the resultant query.

So the resultant data set would have:

1.) For names that match, the name, sum of the "count" fields in both the tables and sum of the "Total" fields from both the tables.

2.) In the first table for names that dont match, it will just copy the "name", "count" and "total" fields for all records that dont match.

3.)In the second table for names that dont match, it will just copy the "Salesname", "count" and "total" fields for all records that dont match.

Hope this is clear. If not, please let me know and I will try to be clearer.

Thanks.

 

[PHV]

SELECT A.Name, A.Count+Nz(B.Count,0) AS [# of Transactions], A.Total+Nz(B.Total,0) AS TotalValue
FROM table1 AS A LEFT JOIN table2 AS B ON A.Name=B.SalesName
UNION SELECT B.SalesName, B.Count, B.Total
FROM table1 AS A RIGHT JOIN table2 AS B ON A.Name=B.SalesName
WHERE A.Name Is Null;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244