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.
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.