By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,493 Members | 1,176 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,493 IT Pros & Developers. It's quick & easy.

Query multiple fields

P: 22
Hi

I have the following table in a database:

Name: Table 1

Field 1: Account ID
Field 2: FILM ID 1
Field 3: FILM ID 2
Field 4: FILM ID 3
Field 5: FILM ID 4
Field 6: FILM ID 5

I am not looking to change the table design at all. I am trying to create an SQL query that will search each of these fields for the same film ID, rather than having to type in each id five times to search each field individually. I also want to ensure that if I type in "ermina" it brings up "terminator" for example. This is what I have but it doesnt seem to work:

SELECT * FROM TABLE1 WHERE (Film 1 LIKE "*" & [SearchString] & "*") OR (Film 2 LIKE "*" & [SearchString] & "*") OR (Film 3 LIKE "*" & [SearchString] & "*") OR (Film 4 LIKE "*" & [SearchString] & "*") OR (Film 5 LIKE "*" & [SearchString] & "*"),
Feb 24 '07 #1
Share this Question
Share on Google+
4 Replies


100+
P: 1,646
Hi

I have the following table in a database:

Name: Table 1

Field 1: Account ID
Field 2: FILM ID 1
Field 3: FILM ID 2
Field 4: FILM ID 3
Field 5: FILM ID 4
Field 6: FILM ID 5

I am not looking to change the table design at all. I am trying to create an SQL query that will search each of these fields for the same film ID, rather than having to type in each id five times to search each field individually. I also want to ensure that if I type in "ermina" it brings up "terminator" for example. This is what I have but it doesnt seem to work:

SELECT * FROM TABLE1 WHERE (Film 1 LIKE "*" & [SearchString] & "*") OR (Film 2 LIKE "*" & [SearchString] & "*") OR (Film 3 LIKE "*" & [SearchString] & "*") OR (Film 4 LIKE "*" & [SearchString] & "*") OR (Film 5 LIKE "*" & [SearchString] & "*"),
Hi. Could you just clarify something please.
Does table 1 have 6 fields?
Is the name of the first field Account ID?
Is the name of the second field Film 1? etc
What type of data is stored in the second field?
thanks
Feb 25 '07 #2

P: 22
Hi

Yes the name of the first field is Account ID and the name of the second field is Film 1 etc. The fields Film 1 - 5 store the reference Id for a film taken out by a customer, this is in order to keep track of each film the customer takes out.

I am aware of other ways that I could restructure the tables in my database to make the storing of each film Id much easier. The purpose of posting this thread is to learn how to create a query that will search each of these fields for the same film ID, rather than having to type in each id five times to search each field individually.

thank you
Feb 25 '07 #3

100+
P: 1,646
Nearly there. Just need you to tell me the type of data. I know that you call it the film ID That could be text and it could be numeric. Which one is it
Feb 25 '07 #4

P: 22
Hi,

Thank you for helping me with this, the film Id is text
Feb 25 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.