Querying non-key fields in unrelated tables for 1-to-1 match 1

[pbbriggs]

I have searched Access help, as well as the FAQs and Access forum on this site, but am still unclear on how to do this...

I have two tables, which are initially unrelated (and thus have no relationship defined in the relationship window). Just as background, they are both tables of financial data.

I would like to design a query which will look at two fields in each table (amount and department code) and return only those which are an exact one-to-one match (Then, I would put the combined matched record into a third table). The problem is, neither the amount nor the department code contains unique values. Therefore, it is entirely plausible (and quite likely) that there may be multiple records in one table with an amount-department combo that matches a single amount-department combo of one record in the other table (or multiple records in the second table). And, in those circumstances, I don't want the query to return all of these "uncertain" matches.

The following query has the right idea (matches based on department and amount), but if there is one record in Table 1 with 5 potetial matches in Table2 - or vice versa - I get five results, whereas I don't want any (because it's not a positive match).

SELECT DISTINCT Table1.ID, Table1.Amount, Table1.Dept, Table2.ID, Table2.Amount, Table2.Dept
FROM Table1 INNER JOIN Table 2 ON (Table2.Dept = Table1.Dept) AND (Table1.Amount = Table2.Amount);

Does anyone know if there is a way to do what I am looking for?

Thanks in advance. I've seen some really great responses in this forum so obviously there are some fantastic Access developers out there.

 

[markgrizzle]

You need to create an aggregate query to concatenate and group on department and amount, filter the result to those rows where the count for the concatenated field = 1.

You could then join your query to this query for the matching rows.

This technique will require the fields involved to have at least an autonumber primary key for the final join.

 

[pbbriggs]

Markgrizzle,
Works perfectly - thank you! This solution would never have occurred to me. And, when I read your suggestions I was worried about the concatenation of fields of different formats, etc. (whether they would "match" properly) but it appears to pose no problem.
Thanks so much for the quick and helpful reply.

 

[markgrizzle]

Glad I could help.