Variable number of criteria for a query

[Brendan]

Hi,
simplified, I have 3 tables

Name Cars Links
joe ford joe=ford
sam chevy joe=chevy
harry pontiac joe=pontiac
chris sam=chevy
harry=ford
harry=chevy
chris=pontiac

If I want to query to determine who owns a ford AND a chevy
or query who owns a ford AND a pontiac AND a chevy, etc.

using a form to select the cars,

how can I do it?

Thank you.
Brendan.

 

[Tom Ellison]

Dear Brandan:

Does your Links table have only one column? This is a bit difficult.
Let's break up that column with a query into two columns.

SELECT
LEFT(YourColumn, INSTR(YourColumn, "=") - 1) AS LName,
MID(YourColumn, INSTR(YourColumn, "=") + 1) AS LCar
FROM Links

Save this query as QLink.

Substituting the actual column name from the Links table, does this
work?

Next you can find what you were wanting:

a ford and a chevy

SELECT DISTINCT LName
FROM QLink Q
WHERE EXISTS (SELECT * FROM QLink Q1
WHERE Q1.LName = Q.Lname AND Q1.LCar = "ford")
AND EXISTS (SELECT * FROM QLink Q1
WHERE Q1.LName = Q.Lname AND Q1.LCar = "chevy")

a ford and a pontiac and a chevy:

SELECT DISTINCT LName
FROM QLink Q
WHERE EXISTS (SELECT * FROM QLink Q1
WHERE Q1.LName = Q.Lname AND Q1.LCar = "ford")
AND EXISTS (SELECT * FROM QLink Q1
WHERE Q1.LName = Q.Lname AND Q1.LCar = "chevy")
AND EXISTS (SELECT * FROM QLink Q1
WHERE Q1.LName = Q.Lname AND Q1.LCar = "pontiac")

Getting the data into usable form is first. You may benefit from
studying the rules for data normalization. That's what makes all this
much easier.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Brendan]

Hi Tom, thanks for the quick response.
yes, the Link table has two columns, I over simplified for the message.
I'll try your proposal!
Brendan.

 

[Dale Fye]

Brendan,

How are you selecting the car on your form. Here is what I have done for
this type of functionality in the past.

1. Add another column named MatchThis (Yes/No) to your cars table. Set all
the values to No.
2. Whenever the user clicks on one of the cars in your listbox (I'm
assuming you are using a listbox to capture which cars they want to match),
add a line of code that sets (True) or unsets(False) the MatchThis column in
your table.

Then, your Query becomes:

SELECT Links.Owner, Count(Cars.CarType) AS CountOfCarType
FROM Links INNER JOIN Cars
ON Links.CarType = Cars.CarType
WHERE Cars.MatchThis=True
GROUP BY Links.Owner
HAVING Count(Cars.CarType)=Abs(DSum("MatchThis","Cars"));

This way, you don't have to have a separate query for each count of car
types.

HTH
Dale

 

[Brendan]

Thank you! I'll try it.
Brendan.

Brendan,

How are you selecting the car on your form. Here is what I have done for
this type of functionality in the past.

1. Add another column named MatchThis (Yes/No) to your cars table. Set all
the values to No.
2. Whenever the user clicks on one of the cars in your listbox (I'm
assuming you are using a listbox to capture which cars they want to match),
add a line of code that sets (True) or unsets(False) the MatchThis column in
your table.

Then, your Query becomes:

SELECT Links.Owner, Count(Cars.CarType) AS CountOfCarType
FROM Links INNER JOIN Cars
ON Links.CarType = Cars.CarType
WHERE Cars.MatchThis=True
GROUP BY Links.Owner
HAVING Count(Cars.CarType)=Abs(DSum("MatchThis","Cars"));

This way, you don't have to have a separate query for each count of car
types.

HTH
Dale