Search multiple tables 1

[dmoran]

I have a co-worker who wants to query two tables using a unique control number as criteria. These tables are identical(one is an archive)in structure and have thousands of records each. Could someone please help me with a way to search both tables for a record?

Thanks,
David

 

[Rolliee]

Open them one at a time and search them with vb.


rollie@bwsys.net

 

[BNPMike]

Apply your WHERE criterion to a UNION query.

Create the query for the first table in the Query Screen. Switch to SQL view and cut/paste the SQL with UNION in between the two selects. Amend the fieldnames where necessary. Now name and run the query in the normal way.

 

[dmoran]

Thanks for the replies -
I was successful with union query but am still unsure about the "Where" clause for this. Ultimately, I would like to enter the "control number" on a form which then populates the form with the selected record. I understand the "Where" when you have a defined criteria, but haven't grasped how to enter the criteria once for both tables. Hope that's no too confusing-
David

 

[BNPMike]

Just for starters, if you use something like this:


WHERE (((TABNAME.ControlID)=[Enter Control ID]));

The form will put up a dialogue box and then display just the appropriate records.

 

[dmoran]

Thanks a lot for the help - that did it. Also, I just added a union statement to a combo-box that got the same results - although it takes a second to load the combo box if you have a lot of records.

David

 

[BNPMike]

Thanks for the star.