Search all tables within database 1

[geekcertified]

I'm not sure if this is the right forum to post this in, if not, forgive me. I have an Access Database with 10 Tables of names and addresses. One table would be Medical, one for Dental, one for Nurses, etc.. When I get return mail, I need to be able to search through all of these tables rather quickly for a Name and/or Address without going in and out of each table. Is there an easy way to do this?

 

[PHV]

Create an UNION query with 10 select statements (one for each table).

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dhookom]

In addition to PHV's comment, did you consider placing all the records into a single table with a field that stores values like "Medical", "Dental",... This method is generally much preferred to several similar tables.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[ZmrAbdulla]

Hi PHV,
Sorry to intruding !!
How could one can create a Union Query if the tables are having different counts of fields?
Is that possible to create dummy fields to balance the fields?
thanks?


Zameer Abdulla

http://www.geocities.com/zameerabd/

 

[BNPMike]

Select Doc_Firstname,Doc_Surname,Doc_HouseNum
From TbleDoctors
Where Doc_email = (incoming email)
UNION
Select Firstname,Surname,NumberofHouse
From TbleNurse
Where Nurs_email = (incoming email)
UNION
etc


Just make sure you are selecting similar fields for each table.

 

[ZmrAbdulla]

Hi Mike,
My question was not of creating the union Query but creating a union query from tables that have different count of fields.
If tblDoctors have 5 fields, tblNurses have 4 fields
and tblHospital have 10 fields then how can I create a union query of 10 fields. so that I can include all fields of tblHospital in the query.
thanks


Zameer Abdulla

http://www.geocities.com/zameerabd/

 

[BNPMike]

If you insist then create empty fields in the missing positions. I thought the idea was to get a physical name and address from an email address. I would have thought the name and address fields would be common in each table.

 

[ZmrAbdulla]

Hi,
Can you please tell me, How can I create empty fields?



Zameer Abdulla

http://www.geocities.com/zameerabd/

 

[BNPMike]

Go into the QBE Query Design Screen and add a new field to a query eg Doctor_Number:' '. Look at the SQL it has created in the SQL view. You can build the first select in QBE but sadly after that you've got to do it manually in the SQL screen. Even Access isn't perfect.

 

[ZmrAbdulla]

Thanks mike,
I was confused to make an extra field. Now I have got the idea.
I don't normally type into the SQL View. What I do is create different Queries and merge them into one by cut & paste after a Union statement.

Access has many good things and bad too..

thanks again

Zameer Abdulla

http://www.geocities.com/zameerabd/