Searching multiple tables (1 Viewer)

Michelangelo

Registered User.
Local time
Today, 10:44
Joined
Dec 20, 2002
Messages
37
Hi,

I want to make a query that searches multiple tables for a specific string, f.i. Like '*an*'

The tables have to be seperated because they contain different data. It's a database where I keep my collected info on a project. I have a table called "Literatuur" and a table called "Informatie". I have several others but if this works I can expand it.

I've been searching on this forum and found several useful threads. What I've come up with so far is a SQL statement using the UNION statement. It's like this

SELECT Literatuur.ID, Literatuur.Titel, Literatuur.Omschrijving, Literatuur.Uitgever
FROM Literatuur
WHERE (((Literatuur.ID) Like '*an*')) OR (((Literatuur.Titel) Like '*an*')) OR (((Literatuur.Omschrijving) Like '*an*')) OR (((Literatuur.Uitgever) Like '*an*'));
UNION
SELECT Informatie.ID, Informatie.Titel, Informatie.Omschrijving, Informatie.Website1
FROM Informatie
WHERE (((Informatie.ID) Like '*an*')) OR (((Informatie.Titel) Like '*an*')) OR (((Informatie.Omschrijving) Like '*an*')) OR (((Informatie.Website1) Like '*an*'));

This has however several characteristics I don't want.

1. My tables don't have the same number of fields. As I want to search all fields in all the tables this must be possible. When I put another field in a part of one select statement I get the error saying all queries have to have the same number of fields. Is it possible to make this work for a different number of fields?

2. The resulting table fieldnames are taken from the first query, even if the second select statement has different field names. I want to have a resulting table which contains all the original field names. This would lead to some fields in a record to be empty as that field doesn't exist in that record. But that's not bad.

3. It would be easier if instead of typing all the field names I could use f.i. Informatie.*. But I can't get it working because Access doesn't allow a criterium for an asterix field.

Any help is greatly appreciated as i'm working on it for quite a while now. Also if there is a completely different way of approaching this matter please tell me.

M.
 

Mile-O

Back once again...
Local time
Today, 10:44
Joined
Dec 10, 2002
Messages
11,316
Can you use SELECT * FROM in a UNION query if both tables have the same number of fields?
 
Last edited:

Michelangelo

Registered User.
Local time
Today, 10:44
Joined
Dec 20, 2002
Messages
37
I know, but this doesn't work with the UNION statement. I get the error message that the tables should have the same number of fields.

M.
 

neileg

AWF VIP
Local time
Today, 10:44
Joined
Dec 4, 2002
Messages
5,975
Why not run a separate query on each table and then combine the results.

You can format the individual queries so that the results have compatible fields that are enough to identify the records that match your criteria, or pad the results out on the smaller tables to match those with the most fields. Which you do depends on what you want to do with the results, I suppose.
 
Last edited:

Users who are viewing this thread

Top Bottom