Search code examples
ms-accessms-access-2007records

Access -- Query to return all unique records from Table 2 for each unique record in Table 1?

Long time listener, first time caller...

In Access 2007 I have a column named Account Name and one named Client Name that have the same information, though sometimes with different wording (ex. United Programmers in one, The United Programmers, Inc. in the other), and I need to return every qualified, unique Client Name for each unique Account Name. The tables are keyed on Account Name and Client Name, respectively, and the other information in them is numeric.

Since I'm trying to roughly match the two, I have a parameter (Name) and the criteria for returning both the Account Name and Client Name is LIKE "*"&[Name]&"*" so I'm only getting similar names returned. However, since the user may type in "Programmers" as the Name, I need the resultant table to display as follows:

United Programmers      The United Programmers, Inc.
.                       Programmers Coalition, LLC
.                       Programmers Guild
Programmers Coalition   The United Programmers, Inc.
.                       Programmers Coalition, LLC
.                       Programmers Guild
Programmers Guild       The United Programmers, Inc.
.                       Programmers Coalition, LLC
.                       Programmers Guild

I wouldn't normally do this with a query only, or necessarily even in Access, but this is a small part of a much bigger process. As a result, if it is possible, I need to do everything using the Query Builder in Access 2007 and not manually coded SQL that someone else will have to maintain once I hand this over.

I know this is about as clear as mud, but I'm not sure how to make it clearer. I'll happily answer any questions you have about it.

ANSWERED: It turns out I'm not allowed to answer my own questions yet, but here's what I discovered a few minutes ago:

I created a query that imported both client and account names from the main table using LIKE "*"&[Name]&"*" and allowed it to import the data in any way it wanted to. I then created another query that populates Account Name and Client Name with data from the first query, but under this query's Property Sheet I changed "Unique Values" to "Yes."

It now spits out a very nice table, that has each unique Client Name listed for every unique Account Name, all of which were returned based on their similarity to the [Name] parameter.

I did have to import all of my data into a new Access DB to clean up some of the damage from my experiments, but I'm finally getting somewhere.

Solution

  • I created a query that imported both client and account names from the main table using LIKE ""&[Name]&"" and allowed it to import the data in any way it wanted to. I then created another query that populates Account Name and Client Name with data from the first query, but under this query's Property Sheet I changed "Unique Values" to "Yes."

    It now spits out a very nice table, that has each unique Client Name listed for every unique Account Name, all of which were returned based on their similarity to the [Name] parameter.

    I did have to import all of my data into a new Access DB to clean up some of the damage from my experiments, but I'm finally getting somewhere.