Query multiple fields

[663scott]

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] & "*"),

[willakawill]

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

[663scott]

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

[willakawill]

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

[663scott]

Hi,

Thank you for helping me with this, the film Id is text