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?
 
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
 
hnasrCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.