Selecting two items from a single field

[PhilBear]

I want to be able to perform an AND search on a field where both specified values must be present for an item to be selected.

I have a table with some properties of items. Each record in the table is for a specific item and one of its properties. Say for instance that I want to list the items that are described with both "red and green". Assume that there are entries for widgetA to widgetG with colors "red, blue, yellow, purple" and widgetB and widgetE also has the property "green" in addition to the others ... the query should return widgetB and widgetE only.

I tried to use MSAccess 2003 query builder but cannot get the AND result to work. I switched to an OR and verified that the two values I am searching for do exist for two items in my table.



Here is the SQL statement that did not work.
SELECT Properties.PropertyIndex, Properties.HerbOilLink, Properties.PropertyTypeIndex, Properties.PropertyDescriptionIndex
FROM PropertyType INNER JOIN Properties ON PropertyType.PropertyTypeIndex = Properties.PropertyTypeIndex
WHERE (((Properties.PropertyDescriptionIndex)=80 And (Properties.PropertyDescriptionIndex)=15));

 

[pbaldy]

It sounds like the data is in different records, and the WHERE clause compares values in the same record. The same record can not have a PropertyDescriptionIndex equal to both 80 and 15. It's one or the other. What does the data look like that is returned from the query with no WHERE clause?

 

[Fifty2One]

i just had to do similar where first i select a customers order number then a specific item with in the order which sounds similar to what you need so maybe you can do it with cascading combo boxes

 

[PhilBear]

I was able to do the AND query using three queries ...
Query 1 selected PropertyDescriptionIndex of 15
Query 2 selected PropertyDescriptionIndex of 80
Query 3 I linked the HerbOilNameLink fields together

Now, can I do this all in 1 query?

Query Property15
SELECT Properties.PropertyIndex, Properties.HerbOilLink, Properties.PropertyTypeIndex, Properties.PropertyDescriptionIndex
FROM Properties
WHERE (((Properties.PropertyDescriptionIndex)=15));


Query Property80
SELECT Properties.PropertyIndex, Properties.HerbOilLink, Properties.PropertyTypeIndex, Properties.PropertyDescriptionIndex
FROM Properties
WHERE (((Properties.PropertyDescriptionIndex)=80));


Query Property15_AND_80
SELECT Property15.HerbOilLink
FROM Property15 INNER JOIN Property80 ON Property15.HerbOilLink = Property80.HerbOilLink;