name search with multiple tables 1

[cthai]

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.

 

[dhookom]

Can I ask why you have 5 tables with Last Names in them?
Can you share anything about your table and field names?
What is the SQL view of your union query?


Duane
Hook'D on Access
MS Access MVP

 

[cthai]

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

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] & "*"));

 

[MajP]

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.

 

[dhookom]

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:

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

 

[dhookom]

I should have questioned the normalization as did MajP. Also, "First" and "Last" are reserved words and shouldn't be object/field names.

Duane
Hook'D on Access
MS Access MVP

 

[cthai]

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!