Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

name search with multiple tables 1

Status
Not open for further replies.

cthai

Technical User
Apr 19, 2011
53
US
Hello -

I am trying to do a name search from a button. It works fine when I only have 1 table to search the last name by… however now I am ask to add 5 other tables to the search, is there a way I can have 1 button search through all 5 tables and show me anyone with the last name “Smith”

I try to do a “UNION” and it’s now showing me the names on any other table except for the first one.
 
Hi dhookom -

Each tables have different information - they would like to do a search to for all tables with the last name "Smith" and show the case ID - each case ID is a unique ID.

below is my attemp to do the union

Code:
SELECT InsiderTracker_Main.[Case ID], InsiderTracker_Main.Last, InsiderTracker_Main.First, InsiderTracker_Main.Middle
FROM InsiderTTracker_Main
WHERE ((((InsiderTracker_Main.Last) Like [Forms]![Name History Search]![txtName] & "*"));
UNION     

SELECT Matters_Main.[Case ID], Matters_Main.Last, Matters_Main.First, Matters_Main.Middle
FROM Matters_Main
WHERE (((Matters_Main) Like [Forms]![Name History Search]![txtName] & "*"));
UNION   

SELECT Relations_Main.[Case ID], Relations_Main.Last, Relations_Main.First, Relations_Main.Middle
FROM Relations_Main
WHERE (((Relations_Main) Like [Forms]![Name History Search]![txtName] & "*"));

UNION 

SELECT Employee_Main.[Case ID], Employee_Main.Last, Employee_Main.First, Employee_Main.Middle
FROM Employee_Main
WHERE (((Employees_Main) Like [Forms]![Name History Search]![txtName] & "*"));
 
Your tables do look normalized unless you have a primary compositte key made from first,last and middle. Normally you do not repeat the same info in multiple tables.

Normally it would be something like

tblPersons
personID 'primary key to uniquely describe a person. autonumber works great here
lastName
firstName
MiddleInitial

then in all of your tables there is simply a foreign key relating back to tblPersons.personID.

With that said. You may want to look at a UNION ALL if you are expecting to get duplicates for multiple finds.
 
Any query in Access can have only one ";" at the end. You are also missing the field name in most of the WHERE clauses. Try:
Code:
SELECT [Case ID], [Last], [First], [Middle], "InsiderTTracker_Main" as Source
FROM InsiderTTracker_Main
WHERE [Last] Like [Forms]![Name History Search]![txtName] & "*"

UNION ALL
SELECT [Case ID], [Last], [First], [Middle], "Matters_Main"
FROM Matters_Main
WHERE [Last] Like [Forms]![Name History Search]![txtName] & "*"

UNION ALL
SELECT [Case ID], [Last], [First], [Middle], "Relations_Main"
FROM Relations_Main
WHERE [Last] Like [Forms]![Name History Search]![txtName] & "*"

UNION ALL
SELECT [Case ID], [Last], [First], [Middle], "Employee_Main"
FROM Employee_Main
WHERE [Last] Like [Forms]![Name History Search]![txtName] & "*";

Duane
Hook'D on Access
MS Access MVP
 
Hi dhookom

Thank you! I change First to FNAME and Last to LNAME. I'm not sure why there is 5 different tables... but that was how they were created. Now I have to go back to each table and change the name to FNAME and LNAME and relink all the fields.

Thank you for ALL your help!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top