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.