Consolidating multiple queries into one single result

[danikuper]

I couldn't find a similar question already answered, so here it goes. I have multiple tables used to store information on different research materials:

table Books:
- BookID
- Author
- Title
- Year
- Cabinet

table Papers:
- PaperId
- Author
- Title
- Year
- Cabinet

table Catalog:
- CatalogID
- Author
- Title
- Year
- Cabinet

And I have a query for each of those tables that helps me find information. What I'm trying to do is to consolidate the result of those individual queries into one single query that later I can insert into a form to display a search result.

So the resulting query would look like:

Type.....ID....Author.....Title....Year....Cabinet
Book.....001...John.....Good book....2005....C01
Book....002.....Mary....Other book...2006...C01
Paper....001....Albert...PaperABC....1987....C01
Paper...002....John.....PaperXYZ.....2006....C02
Catalog...001...Mark....Cat00A....1989.....C02
Catalog...002...Bill......Cat00B.....2004.....C03
etc.

Is this possible?

Any help is appreciated!

Thanks.
Daniel

 

[john471]

The type of query you are looking for is termed a Union Query. MS Access help is reasonably good on this, once you know what to search for ("union query").

HTH

Regards

John.

 

[john471]

Are the fields you listed the only fields in each of your tables, or are there others? If they are the only fields, then you should consider reading up on database normalisation. Even if they aren't, you should perhaps still read up on it. A better structure may be:-

tblResearchMaterialType
-------------------
ResearchMaterialTypeID (AutoNumber, PK)
ResearchMaterialType

tblResearchMaterial
-------------------
ResearchMaterialID (Autonumber, PK)
ResearchMaterialTypeID (FK to tblResearchMaterialType)
Author
Title
Year
Cabinet

From the details you posted above, your tblResearchMaterialType would have three records:-
1, Book
2, Paper
3, Catalog

You could add other fields in tblResearchMaterial, even if they are not used for every record, or every type of research material.

HTH

Regards

John.

 

[danikuper]

Thanks! I'll check the union queries help and see if I can figure out how to use it. As for your point with normalization, the tables do have some additional fields. In addition to that, I'd like to speed up queries since that's the purpose of the database 90% of the time and the user will usually want to search on 'catalogs' or 'books' only anyways.

Thanks for the help!
daniel

 

[john471]

Good luck with it. I still suggest you look into normalisation - it will save you headaches in the long run; and the sooner you do it the easier it will be. Add an index to the FK field, and I'm sure you'll find performance is acceptable.