Selecting More Than 1 Table in A Single Query

Question

I have 5 Tables in MS Access as

Logs [Title, ID, Date, Author]
Tape [Title, ID, Date, Author]
Maps [Title, ID, Date, Author]
VCDs [Title, ID, Date, Author]
Book [Title, ID, Date, Author]

I tried my level best through this code

SELECT Logs.[Author], Tape.[Author], Maps.[Author], VCDs.[Author], Book.[Author]
FROM Logs
   , Tape
   , Maps
   , VCDs, Book
WHERE ((([Author] & " " & [Author] & " " & [Author] & " " & [Author]& " " & [Author])        
Like "*" & [Type the Title or Any Part of the Title and Press Ok] & "*"));

I want to select all of these fields in a single query. Suppose there is Adam as author of works in all tables. So when i put Adam in search box it should result from all tables.

I know this can be done by having single table or renaming fields names but that's not required.

Please help.

Answer 1

Try creating a new query that UNIONS all the tables and then change your existing query to select from that rather than each underlying table independently. So something like:

SELECT * 
  FROM NewUnionQuery 
 WHERE NewUnionQuery.Title LIKE "*" & [Enter a Title] & "*";

Also, get a good book on SQL and read it.

Answer 2

You could try this:

   select 'Logs', Title, ID, Date, Author from Logs where Author like 'Adam'
union
   select 'Tape' ,Title, ID, Date, Author from Tape where Author like 'Adam'
union
   select 'Maps',Title, ID, Date, Author from Maps where Author like 'Adam'
union
   select 'VCDs', Title, ID, Date, Author from VCDs where Author like 'Adam'
union
   select 'Book', Title, ID, Date, Author from Book where Author like 'Adam'

or better even you could create a view to 'union' the tables and then select from the view.

Answer 3

I second what lexu and Trevor Tippins suggest and these will solve your immediate problem.

Still, please re-examine your attitude towards redesigning the tables. The use of UNION queries as a core part of application is an indicator of problems in the database design.

If your application often works with all five tables then I would say that the database model should show that. Also, the performance of UNION queries is not stellar, especially in MS Access and you'll get other penalties such as non-updateable result set.

If you had another table called Media which would hold all the common fields from the five tables plus the media type you could still have tables for each of the particular media which would have specific fields and would refer to the main Media table through IDs.

Be sure to understand all concepts mentioned, for example here, before your databases and applications get too complex because redesign is going to impact all the queries, forms and code, and later it happens the more work it will require.