Filter Multiple Access Tables With Parameter Query

Hello Experts,

I have inherited three (3) different access tables.  At this time, no relationships have been set up between the tables. I am trying to use a parameter query to search or filter thru all three tables at once and return those with the parameter input.  For example, all of them have some name and address information.  I would like to create a parameter query where I can input the last name and return information from the three tables where the last name matches the parameter input.  For example, if I enter "Smith"; I would like to return or filter thru all the records from the three tables and filter or return those results with "Smith" as the last name.

Thanks...
idejjedi2Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GozrehCommented:
You can use a union query to combine all 3 tables.
Combine the results of several select queries by using a union query
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Hamed NasrRetired IT ProfessionalCommented:
In this issue: Assume you have a Form: theForm, with a field: txtName

1 create a join query using the proper fields to relate the tables, where that creates the equivalent of one table.
Example: SELECT * FROM (A INNER JOIN B ON A.f1=B.f1) INNER JOIN C ON A.f2=C.f2
2 set the criteria for the name field, as: Like "*" & Forms!theForm!txtName & "*"

Result query:
SELECT * FROM (A INNER JOIN B ON A.f1=B.f1) INNER JOIN C ON A.f2=C.f2
WHERE A.theName LIKE "*" & FORMS!theForm!txtName & "*"
0
PatHartmanCommented:
As Gozreh has already suggested, you would use a Union query to search the three tables.  Union queries are like stacked lists so you must select the same columns in the same order from each of the three tables.  You also need to include a calculated column that identifies the source of the data so you can tell in which table it was found.

Select LastName, FirstName, Addr, City, State, Zip, "tblA" as Source From tblA
Union LastName, FirstName, Addr, City, State, Zip, "tblB" as Source From tblB
Union LastName, FirstName, Addr, City, State, Zip, "tblC" as Source From tblC;
0
idejjedi2Author Commented:
Experts,

Thank you as always.  Sorry it took so long to get back to this.  I gave points to Gozreh and PatHartman as the calculated column was definitely needed, so that I knew which table the information was being retrieved.  In retrospect, that information was provided in "Tips for using union queries" at the bottom of the link Gozreh provided, but PatHartman's explanation helped. I am not sure if it really matters, but the link Gozreh provided was for Access 2007 and not Access 2013.  Not sure if there are any differences, just wanted to let anyone reading this know, as the Access 2013 explanation is broken down in more detail and may help someone reading this in future.  

I added a parameter query to search for certain values in my tables. Here is a copy of the basics of my code that worked for me:

SELECT LastName, FullName, Address1, City, State, ZipCode,"TableA" AS Source
FROM TableA
WHERE LastName Like "*" & [Enter Last Name] & "*"
UNION
SELECT LastName, FullName, Address1, City, State, ZipCode, "TableB" AS Source
FROM TableB
WHERE LastName Like "*" & [Enter Last Name] & "*"
UNION
SELECT LastName, FullName, Address1, City, State, ZipCode, "TableC" As Source
FROM TableC
WHERE LastName Like "*" & [Enter Last Name] & "*";

If someone knows of a better or more efficient way, please let me know.

Thanks again Experts!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.