 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Reports / Printing / April 2008Search Query of two tables
I am trying to create a query that will enable me to search my client list against another list to see if there are any matches. Basically in my business I have to “scrub” my client list against a “blocked persons” list, which is sent to me in a spread sheet. For example lets say I had two tables. Table 1 would be the “block persons” list with a field called FULL NAME. The 2nd table would be my client list with a field called FULL NAME as well. Now I want to create a query that will show me if any of my customers show up on the “blocked persons” list. Or can I show another column next to the name of my client that has a count of matches (hits), if any.  Signaturebrandon Try this. the following query will select names from clients which are in the blockedlist SELECT Clients.FullName FROM Clients WHERE Clients.FullName IN (SELECT BlockedList.FullName FROM BlockedList) >I am trying to create a query that will enable me to search my client list >against another list to see if there are any matches. Basically in my [quoted text clipped - 8 lines] >on the “blocked persons” list. Or can I show another column next to the name >of my client that has a count of matches (hits), if any. Signaturespread the WORD maybe im typing it in wrong, these are the fields i am using. for my customers: Table = ccList ID ClientID CCName CCaddress CCbusinessName For blocked list: (this is a query of a table to merge the names. Query name = INDIVIDUAL ID Fin_Date Full Name Alias street city state zip country >Try this. the following query will select names from clients which are in the >blockedlist [quoted text clipped - 8 lines] >>on the “blocked persons” list. Or can I show another column next to the name >>of my client that has a count of matches (hits), if any. Signaturebrandon >>Now I want to create a query that will show me if any of my customers show up on the “blocked persons” list. Create a query in design view by adding both tables. Click on 'FULL NAME' in first table and drag to 'FULL NAME' in the second table. Double click on the asterisk in your table to select all fields for display. When you run the query it will display those records that are in both tables. SignatureKARL DEWEY Build a little - Test a little > I am trying to create a query that will enable me to search my client list > against another list to see if there are any matches. Basically in my [quoted text clipped - 8 lines] > on the “blocked persons” list. Or can I show another column next to the name > of my client that has a count of matches (hits), if any. ok o have got this far already, but how do i show only the matching records in from each query/table. i dont want or need to see everything i have thousands of names. >>>Now I want to create a query that will show me if any of my customers show up >on the “blocked persons” list. [quoted text clipped - 7 lines] >> on the “blocked persons” list. Or can I show another column next to the name >> of my client that has a count of matches (hits), if any. Signaturebrandon If you had tried as I suggested you would have seen that when you join the two tables the ONLY records that are displayed in the results ARE IN BOTH tables! SignatureKARL DEWEY Build a little - Test a little > ok o have got this far already, but how do i show only the matching records > in from each query/table. i dont want or need to see everything i have [quoted text clipped - 11 lines] > >> on the “blocked persons” list. Or can I show another column next to the name > >> of my client that has a count of matches (hits), if any. can you do the same thing with a query, if so how? >If you had tried as I suggested you would have seen that when you join the >two tables the ONLY records that are displayed in the results ARE IN BOTH [quoted text clipped - 4 lines] >> >> on the “blocked persons” list. Or can I show another column next to the name >> >> of my client that has a count of matches (hits), if any. Signaturebrandon >> can you do the same thing with a query, if so how? From my earlier post --- Create a query in design view by adding both tables. Click on 'FULL NAME' in first table and drag to 'FULL NAME' in the second table. Double click on the asterisk in your table to select all fields for display. When you run the query it will display those records that are in both tables. SignatureKARL DEWEY Build a little - Test a little > can you do the same thing with a query, if so how? > [quoted text clipped - 6 lines] > >> >> on the “blocked persons” list. Or can I show another column next to the name > >> >> of my client that has a count of matches (hits), if any. I have it working when i use two tables in one query. However, My issue is this... both tables have different layouts. One table has the first and last name in one cell and the other has them separated. this is just one example. So i had to create a query for each of the tables so they both have the same layout. After i did this i had to create another query and based it on the aforementioned. when i do that... dupes in each do not come up, any suggestions. and thank you for your help! :) bl >>> can you do the same thing with a query, if so how? >From my earlier post --- [quoted text clipped - 8 lines] >> >> >> on the “blocked persons” list. Or can I show another column next to the name >> >> >> of my client that has a count of matches (hits), if any. Signaturebrandon >>when i do that... dupes in each do not come up, any suggestions. To me 'dupes' are duplicate records in the same table and I would use a totals query to count records that are >1. But this is in a single table. SignatureKARL DEWEY Build a little - Test a little > I have it working when i use two tables in one query. However, My issue is > this... both tables have different layouts. One table has the first and last [quoted text clipped - 20 lines] > >> >> >> on the “blocked persons” list. Or can I show another column next to the name > >> >> >> of my client that has a count of matches (hits), if any. i have two sets of tables. each has a list of names and other information. Im looking to see if each table has a matching name in the other table, thats all. >>>when i do that... dupes in each do not come up, any suggestions. >To me 'dupes' are duplicate records in the same table and I would use a [quoted text clipped - 5 lines] >> >> >> >> on the “blocked persons” list. Or can I show another column next to the name >> >> >> >> of my client that has a count of matches (hits), if any. Signaturebrandon >>So i had to create a query for each of the tables so they both have the same layout. After i did this i had to create another query and based it on the aforementioned. when i do that... dupes in each do not come up, any suggestions. If you join the two queries you do not get any records? Post SQL of the queries you are trying to use. SignatureKARL DEWEY Build a little - Test a little |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.