435,157 Members | 891 Online
Need help? Post your question and get tips & solutions from a community of 435,157 IT Pros & Developers. It's quick & easy.

# Multiple Record Matching

 P: 84 I have two tables that contain identical fields. (Account, Amount, Customer ID). I have a query that joins on those three fields. For the most part when matching records I get a one for one match. However, there are incidents where let say in table one I have 3 records that contain they following values (Account= 1234, Amount = 100.00, Customer ID = 9870. In table two I have 4 records that have Account = 1234, Amount = 100.00, Customer ID = 9870.) When I run the query I get 12 responses for this customer ID. When this condition exists, I only want the system to return the total of the lowest number (ie in the case above I expect to receive 3 matches since table 1 only has 3 records). Is there a way to limit the results in this type of query? Feb 3 '09 #1
4 Replies

 Expert 2.5K+ P: 2,653 So ... Could you clarify a rule according to which certain combinations are defined to be within the "limit"? Kind regards, Fish Feb 3 '09 #2

 P: 84 I would think the rule would be...for each record in table 1, create only one match in table 2 where the Account, Amount and Customer ID match Feb 3 '09 #3

 Expert 100+ P: 1,287 This should work, though there may be a more efficient query possible: SELECT * FROM Table1 WHERE EXISTS (SELECT Table2.Account FROM Table2 WHERE Table1.Account = Table2.Account AND Table1.Amount = Table2.Amount AND Table1.[Customer ID] = Table2.[Customer ID]) Feb 3 '09 #4

 P: 84 That solution worked great...THX!!! Feb 3 '09 #5