Searching more than one table

[Rebecca]

I have a member database. In this database I have a tbl Memberfile and a
tbl RemovedMembers
When a new member joins I currently need to do a search in both the
memberfile and then go to removedmembers and search to check and see if they
are already listed(due to many situations they could be listed in either of
the tables).
Could I write a macro to do this? A separate form with a search button?
that I can set up that I can click on and it will search both tables for
the name so that I don't have to go into both tbls?

Any ideas would be greatly apprecaited!!

Thank you in advance!

Rebecca

 

[James Goodman]

Ok,

You will need to create a UNION query to return a complete list of current &
existing members.
e.g.
SELECT Surname, Forename FROM tblMemberFile
UNION
SELECT Surname, Forename from tblRemovedMembers

Save this as a query. It will return a complete list of members, new & old.


You can then execute some SQL against it to search:
SELECT & FROM [UnionQueryName]
WHERE Surname = 'SomeSurname'
AND Forename = 'SomeForename'

 

[Rebecca]

I'm sorry but I don't understand what a UNION SELECT is. How do I go about
doing that?

Rebecca

 

[James Goodman]

A union select is like performing one query returning results, & then
performing another query & adding those results to the output of the first.

The easiest way to create your query is to create two seperate queries using
the grid designer.
Make sure the output columns are the same.
Then switch to the SQL view, & copy the SQL into another, blank query.
Add the UNION operator, & then copy the SQL for the second query below it.


This should create a union query, which you can then query...

 

[John Vinson]

Could I write a macro to do this? A separate form with a search button?
that I can set up that I can click on and it will search both tables for
the name so that I don't have to go into both

A UNION query would allow you to string the two tables together into
one longer recordset. See UNION in the online help - post back if it
isn't clear what to do.

 

[Rebecca]

GREAT!! I believe that worked..

Thanks so much!!! Your answer was very easy to follow and understand.

Rebecca