Multiple Field and Multiple Argument Query

[alexk15102]

I hope I can explain what I am trying to do correctly.

I have created a database for tracking a collection of plastic miniatures. The main table which stores all the data about the individual models has 11 - 13 fields I would like to seach with a single query. What I want is this...

A single argument prompt in which I can enter one or multiple keywords, that will search for those keywords across all 11 - 13 fields and return all records that contain all of the keywords with the keywords appearing in one or more of any of the fields searched. If I can get that working I would also like to make a second query with much the same configuration that will return all records with any of the keywords.

Right now the best I can do is prompts for each field I want to search and each prompt can only contain one keyword.

Any and all help is appreciated.

Thanks.

-Alex

 

[DCrake]

If you add a new field to your query which is a concatenation of all the searchable fields and perform the search on the new field using the Like() operator.

 

[alexk15102]

Is there any way to get the field to fill automatically, or does all the data need to be retyped into that field? Also that doesn't explain how to get multiple keywords into the query prompt. Right now I am using Like "*" & [ Field_Name ] & "*" to perform the search, but multiple keyword become a literal "keyword1, keyword2, etc" string and return no results, because none of the fields have commas or multiple keywords.

Thanks.

-Alex

 

[DCrake]

Try changing the Like to In()

 

[alexk15102]

Try changing the Like to In()

Sorry, I'm still a novice at this. Can you give me an example based on my example above?

Thanks.

-Alex

 

[DCrake]

From you text box you will have a string of keywords, say

Red, Blue, Green, Etc

In you query you would have In(Red, Blue, Green, Etc)

Have a look at Acess help on In()

 

[alexk15102]

From you text box you will have a string of keywords, say

Red, Blue, Green, Etc

In you query you would have In(Red, Blue, Green, Etc)

Have a look at Acess help on In()

I'm confused. Looking at Office Online it seems like the In() operator won't work in my expression. Here is what I have at present. It promps multiple times and each prompt can take only one keyword.

SELECT *
FROM Miniatures
WHERE Miniatures.ModelName Like "*" & [ Model Name ] & "*" And Miniatures.Size Like "*" & [ Size ] & "*" And Miniatures.Origin Like "*" & [ Origin ] & "*" And Miniatures.Type Like "*" & [ Type ] & "*" And Miniatures.Keywords Like "*" & [ Keywords ] & "*" And ([OwnerAlex]+[OwnerBob]+[OwnerJoe]+[OwnerRich])<>0;

I have more fields to add to the search since this query was built, but it should give you an idea what I have.

Thanks.

-Alex

 

[alexk15102]

I can't seem to get the In() operator to work, can you give me an example based on the quoted SQL above?

 

[wiklendt]

what DCrake is trying to say, me-thinks, is that you need to first concatenate all your fields into one, and then search that.

i.e., in design view, go to the next available 'blank' 'column' and type

MySearchField: [MyField1] & " " & [MyField2] & " " & [MeField3]

... etc

then in the criteria for that "MySearch" field, add the search criteria...

edit: but i'm not sure myself how to apply the In() in this instance - the usual is to have a new query with something like:

In (SELECT MySearchField FROM qryMyQueryWithTheConcatenatedField)

as the criteria, but how you would combine this with a parameter...? you'll just have to play around with this.

edit: and i'm not sure how to apply the comma issue either...

edit: and access help hasn't helped me much either (on this application use of IN() and parameters...)

 

[alexk15102]

This is in the query itself, not the table? Correct?

 

[alexk15102]

I must be doing something wrong. This crashed Access and I had to repair the database.

 

[alexk15102]

Okay got it working with the Like expression. Here is what I have.

SELECT Miniatures.ID, Miniatures.SetName, [Miniatures]![OwnerAlex]+[Miniatures]![OwnerBob]+[Miniatures]![OwnerJoe]+[Miniatures]![OwnerRich] AS Quantity, Miniatures.ModelName, Miniatures.Keywords, Miniatures.KeyRace, Miniatures.KeySex, Miniatures.KeyClass, Miniatures.KeyWeapon, Miniatures.KeyArmor, Miniatures.KeyClothing, Miniatures.ModelImage, [ModelName] & " " & [Keywords] & " " & [KeyRace] & " " & [KeySex] & " " & [KeyClass] & " " & [KeyWeapon] & " " & [KeyArmor] & " " & [KeyClothing] AS SortField
FROM Miniatures
WHERE ((([Miniatures]![OwnerAlex]+[Miniatures]![OwnerBob]+[Miniatures]![OwnerJoe]+[Miniatures]![OwnerRich])<>0) AND (([ModelName] & " " & [Keywords] & " " & [KeyRace] & " " & [KeySex] & " " & [KeyClass] & " " & [KeyWeapon] & " " & [KeyArmor] & " " & [KeyClothing]) Like "*" & [ Keyword ] & "*"))
ORDER BY Miniatures.ID;

I'm going to try creating the In() expression under the criteria and see what I get.

 

[gemma-the-husky]

can you find what you want by clicking the binocular icon?

 

[alexk15102]

No. I think that requires a literal match, and shows only one record at a time. I am trying to find all matches and view them as a report.

 

[alexk15102]

I've been thinking about something and I wanted to run it past you since I don't have my database here with me today. Would it be possible to have an expression like this in the criteria section of my SortField that would prompt multiple times, but allow for multiple keywords to be applied to the SortField results?

Like "*" & [ Keyword 1 ] & "*" AND Like "*" & [ Keyword 2 ] & "*" AND Like "*" & [ Keyword 3 ] & "*"...

Now that I have the concatenated field would this give me the results I am looking for?

 

[wiklendt]

maybe "OR" would be better...

 

[alexk15102]

No. I am looking to filter down the results with the extra keywords. If this works though I might want a second query that does OR.

Thanks.

 

[wiklendt]

i'm just worried that "AND" would mean that the result only comes up if you type the keywords in exact order that your concatenated field has them - have you tried you AND criteria yet? does it work for you?