Search query in access

[663scott]

Hi

An Access database I am creating which is designed for a rental shop, I have the following table :

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

The account id of course refers to the customers account, and FILM ID 1-5 is the customers top five most rented films. This would help the staff determine possible trends in film choices with a particular customer.

When I attempt to create a query in the QBE pane, I would like to know if it is possible for the user to type in a FILM ID once but fields 2-6 are all checked in order to find the correct result. Currently If i want to search fields 12-6 I need to search each field individually and type in the FILM ID five times.

Yours gratefully

[ADezii]

Hi

An Access database I am creating which is designed for a rental shop, I have the following table :

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

The account id of course refers to the customers account, and FILM ID 1-5 is the customers top five most rented films. This would help the staff determine possible trends in film choices with a particular customer.

When I attempt to create a query in the QBE pane, I would like to know if it is possible for the user to type in a FILM ID once but fields 2-6 are all checked in order to find the correct result. Currently If i want to search fields 12-6 I need to search each field individually and type in the FILM ID five times.

Yours gratefully

Your current Table structure is definately not conducive to extracting data such as you are describing. Ideally, you should have a Customers Table (tblCustomers) and a Rental Table (tblRentals) linked in a 1 to MANY Relationship via an AccountID. All Rentals would now reside in Child Table and requested statistics on Rented Films would be a breeze.

[663scott]

Hi

Thank you for your post. I could rearrange the table structure, however I really would like to know if it would be possible to search multiple fields using one entry however ? is this possible ?

thank you

[NeoPa]

Hi

An Access database I am creating which is designed for a rental shop, I have the following table :

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

The account id of course refers to the customers account, and FILM ID 1-5 is the customers top five most rented films. This would help the staff determine possible trends in film choices with a particular customer.

When I attempt to create a query in the QBE pane, I would like to know if it is possible for the user to type in a FILM ID once but fields 2-6 are all checked in order to find the correct result. Currently If i want to search fields 12-6 I need to search each field individually and type in the FILM ID five times.

Yours gratefully

Could you perhaps post an example.
I'm afraid I'm struggling to understand what you're after exactly.
Your last sentence confuses me particularly with a reference to fields 12-6?

[663scott]

Hi

whoops I meant 2-6, sorry.

Before you post your response I just want to say I am aware of Normalisation, I just purely want to know for my knowledge if it is possible for Access to search more than one field for the same information. I created a statement in SQL, however it still asks me for my search criteria for each field 5 times.

SELECT Table1.*
FROM TABLE1
WHERE (Table1.[Film 1] LIKE '*" & SearchString & "*') " & _
   "OR (Table1.[Film 2] LIKE '*" & SearchString & "*') " & _
   "OR (Table1.[Film 3] LIKE '*" & SearchString & "*') " & _
   "OR (Table1.[Film 4] LIKE '*" & SearchString & "*') " & _
   "OR (Table1.[Film 5] LIKE '*" & SearchString & "*')"

[NeoPa]

I hear what you're saying and I promise not to post a link to the Normalisation Tutorial ;)
I do still need a clarificatioon of what you're after however.
Let me guess and you can tell me if I'm along the right lines.
Say you had a film Braveheart that you were interested in, you may want to select all those members who have Braveheart in any of their five top film slots? Is that about right?

[NeoPa]

Hi

whoops I meant 2-6, sorry.

Before you post your response I just want to say I am aware of Normalisation, I just purely want to know for my knowledge if it is possible for Access to search more than one field for the same information. I created a statement in SQL, however it still asks me for my search criteria for each field 5 times.

SELECT Table1.*
FROM TABLE1
WHERE (Table1.[Film 1] LIKE '*" & SearchString & "*') " & _
   "OR (Table1.[Film 2] LIKE '*" & SearchString & "*') " & _
   "OR (Table1.[Film 3] LIKE '*" & SearchString & "*') " & _
   "OR (Table1.[Film 4] LIKE '*" & SearchString & "*') " & _
   "OR (Table1.[Film 5] LIKE '*" & SearchString & "*')"

How did you create and/or test this.
While doctoring it so that it could be read, I found it was a bit of a hybrid of SQL code & VBA code. It couldn't possibly work as you had it. Can I assume that you have a VBA variable called SearchString?

Try :

strSQL = "SELECT * " & _
         "FROM Table1 " & _
         "WHERE [Film 1] & [Film 2] & [Film 3] & " & _
               "[Film 4] & [Film 5] Like '*" & SearchString & "*'"

[663scott]

Hi

Thank you for your posting

Quote:
Let me guess and you can tell me if I'm along the right lines.
Say you had a film Braveheart that you were interested in, you may want to select all those members who have Braveheart in any of their five top film slots? Is that about right?

Yes!!!!! Perfect thank you!!!!! you understood my query 100 Percent!!!

I tried the code, I created a query and used your code as the SQL behing it. Unfortunately I kept on receiving the result "EXPR1000". I entered your code like this:

SELECT "*"
FROM Table1
WHERE [Film 1] & [Film 2] & [Film 3] & [Film 4] & [Film 5] Like "*" & SearchString & "*'";

I will try to add a copy of the database with this error message. Thank you for helping me!!! I am really pleased that you are willing to help me work out this little problem which I have quite an interest in knowing how to solve.

And thank you for not posing any normalisation tutorials !!! I know the structure is not something that would be used but that isnt what I am interested in.

[NeoPa]

Hi

Thank you for your posting

Quote:
Let me guess and you can tell me if I'm along the right lines.
Say you had a film Braveheart that you were interested in, you may want to select all those members who have Braveheart in any of their five top film slots? Is that about right?

Yes!!!!! Perfect thank you!!!!! you understood my query 100 Percent!!!

I tried the code, I created a query and used your code as the SQL behing it. Unfortunately I kept on receiving the result "EXPR1000". I entered your code like this:

SELECT "*"
FROM Table1
WHERE [Film 1] & [Film 2] & [Film 3] & [Film 4] & [Film 5] Like "*" & SearchString & "*'";

I will try to add a copy of the database with this error message. Thank you for helping me!!! I am really pleased that you are willing to help me work out this little problem which I have quite an interest in knowing how to solve.

And thank you for not posing any normalisation tutorials !!! I know the structure is not something that would be used but that isnt what I am interested in.

Your version missed out the leading single-quote.

[NeoPa]

Your version missed out the leading single-quote.

And now I look more closely, some other problems too.
Try my version as exactly as possible.

[663scott]

Hi

Should the code you provided, be added to the "on update" section of a text box or the "View SQL" section in the QBE pane.

thanks

[NeoPa]

That depends heavily on what you want it for.
That's what you're supposed to tell me first :D

[663scott]

I would really like to create a form where the one search criteria is entered ie "Terminator" and the Query is sorted on the fly to search the "FILM" fields 2-6
to find and return only those entries. Unfortunately my coding is not too strong so I'll need a little help. I have created the form with an unbound text box labeled FILMENTRY, and a command button labeled FLMBUTTON.The table structure remains as the original example I provided. I need some help with the SQL statement. I tried using some ADO language in the variables section but it isn't working properly.