Filtered Query

[alexk15102]

I am very new to Access, and I am having a lot of trouble figuring out how to get a query I want created.

I was able to create a query which prompts the user for input in four different fields and searches based on those entered keywords, however the query returns records that have any of those keywords, when what I really want is a query that only returns results that have all of the keywords. Is there a way to accomplish this?

Also I would prefer, if possible, one prompt which allows multiple keywords and uses those across all the fields being queried.

 

[pbaldy]

If you look at the query in SQL view, my guess is that the criteria are separated by "OR". Try changing those to "AND".

 

[alexk15102]

It's a Union Query, so each set of statements is separated by UNION. I think I tried replacing UNION with AND, but got an error.

Here is the text of the query.

SELECT Miniatures.ModelName, [OwnerAlex]+[OwnerBob]+[OwnerJoe]+[OwnerRich] AS Quantity, Miniatures.Size, Miniatures.Origin, Miniatures.Type, Miniatures.Keywords, Miniatures.Loc_Case, Miniatures.Loc_Tray, Miniatures.Loc_Slot
FROM Miniatures
WHERE (((Miniatures.ModelName) Like "*" & [ Model Name ] & "*") AND (([OwnerAlex]+[OwnerBob]+[OwnerJoe]+[OwnerRich])<>0))
UNION
SELECT Miniatures.ModelName, [OwnerAlex]+[OwnerBob]+[OwnerJoe]+[OwnerRich] AS Quantity, Miniatures.Size, Miniatures.Origin, Miniatures.Type, Miniatures.Keywords, Miniatures.Loc_Case, Miniatures.Loc_Tray, Miniatures.Loc_Slot
FROM Miniatures
WHERE ((([OwnerAlex]+[OwnerBob]+[OwnerJoe]+[OwnerRich])<>0) AND ((Miniatures.Size) Like "*" & [ Size ] & "*"))
UNION
SELECT Miniatures.ModelName, [OwnerAlex]+[OwnerBob]+[OwnerJoe]+[OwnerRich] AS Quantity, Miniatures.Size, Miniatures.Origin, Miniatures.Type, Miniatures.Keywords, Miniatures.Loc_Case, Miniatures.Loc_Tray, Miniatures.Loc_Slot
FROM Miniatures
WHERE ((([OwnerAlex]+[OwnerBob]+[OwnerJoe]+[OwnerRich])<>0) AND ((Miniatures.Origin) Like "*" & [ Origin ] & "*"))
UNION
SELECT Miniatures.ModelName, [OwnerAlex]+[OwnerBob]+[OwnerJoe]+[OwnerRich] AS Quantity, Miniatures.Size, Miniatures.Origin, Miniatures.Type, Miniatures.Keywords, Miniatures.Loc_Case, Miniatures.Loc_Tray, Miniatures.Loc_Slot
FROM Miniatures
WHERE ((([OwnerAlex]+[OwnerBob]+[OwnerJoe]+[OwnerRich])<>0) AND ((Miniatures.Type) Like "*" & [ Type ] & "*"))
UNION SELECT Miniatures.ModelName, [OwnerAlex]+[OwnerBob]+[OwnerJoe]+[OwnerRich] AS Quantity, Miniatures.Size, Miniatures.Origin, Miniatures.Type, Miniatures.Keywords, Miniatures.Loc_Case, Miniatures.Loc_Tray, Miniatures.Loc_Slot
FROM Miniatures
WHERE ((([OwnerAlex]+[OwnerBob]+[OwnerJoe]+[OwnerRich])<>0) AND ((Miniatures.Keywords) Like "*" & [ Keywords ] & "*"));

 

[pbaldy]

I'm not sure it needs to be a UNION. I would expect something like:

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

Does that work?

 

[alexk15102]

Nope. I get this error.

I'm going to try cutting and pasting into another query too. This one was built as a Union Query and maybe the program doesn't expect the ANDs.

EDIT: Cutting and pasting into a new query didn't work either. If someone with a more critical eye than myself can point out the syntax error I would appreciate it.

 

[pbaldy]

It would probably be simpler if you could post a sample db with the table and query.

 

[alexk15102]

Try this. I retained some sample data.

 

[pbaldy]

I only have A2k on this machine, but I'll be home shortly and have 2007 on my laptop, so I'll check it later on.

 

[alexk15102]

Any suggestions yet? I have the db with me at work on my flash drive, so I can try things out.

 

[pbaldy]

Sorry, I didn't get around to it last night. I do have 2007 on another machine here so I've got it open, but the sample I gave you appears to work, though it's incomplete (I'm too lazy to input all the fields):

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

 

[alexk15102]

I think I see. I'll give it a try.

 

[alexk15102]

Here is what I have and I get no results.

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;

 

[pbaldy]

I tried that with

Cleric of Order
medium
natural
human
human

and got a matching record. You said you wanted it to return records that met ALL conditions, right?

 

[alexk15102]

Wait. Okay I think I see the problem. It works when only one prompt is answered, but if you answer more than one prompt it returns no records. I would like to be able to enter "human" in the Model Name prompt and "human" in the keyword prompt and get all records where "human" appears in either field. Or enter "human" for Model Name and "dagger" in keywords and get all models with human in the model name and dagger in the keywords.

EDIT: Now it seems to be working. Can you tell me, does it require valid entry at each prompt or can you leave a prompt blank and not have it seen as a wild card?

EDIT: Still kind of finiky. Entering...
human
blank
blank
blank
human
still returns nothing, but there are records that have "human" in one or both fields. ???

 

[pbaldy]

I just entered through all of them and got a large number of records, so as I would expect leaving one blank is like a wild card. Don't forget records have to meet that other criteria with the names in it.

 

[alexk15102]

I think what I want is actually more complex than what I explained. In fact looking to the top of the thread that is exactly the case.

This works good for now, but I'd still like to be able to enter one or more words into a single prompt and have it search across all five fields returning records where all of the keywords appear across any of the searched fields.

 

[pbaldy]

Which records? I see 3 records with human in the model name field; none have human in the keywords field.

 

[alexk15102]

Which records? I see 3 records with human in the model name field; none have human in the keywords field.

Sorry, my database has 1063 records. Some of those meet the criteria.

 

[pbaldy]

Do they also meet the

([OwnerAlex]+[OwnerBob]+[OwnerJoe]+[OwnerRich])<>0

criteria?

 

[alexk15102]

I think I figured it out. The operator before the Keywords argument needs to be OR, instead of AND and I get the results I want. Still multiple prompts, but I think it will work the way I want it to.