WHERE statement in a form/query

[Mike]

Hi,

Can someone pls tell me why the query shown below is incorrect?

I'm trying to synchronize two combo boxes (cascading) in a form but the
query just wont accept the WHERE statement.

TIA

Mike

ACCESS03, SQL2K





SELECT DISTINCT MODEL_ID, MODEL_NAME, MAKE_ID

FROM MODEL

GROUP BY MODEL_ID, MODEL_NAME

WHERE MAKE_ID=[Forms]![Form1]![combo1]

ORDER BY MODEL_NAME



Combo1 displays MAKE_ID, MAKE_NAME (the first hidden)

Combo2 should display MODEL_ID, MODEL_NAME (The first hidden)

 

[John Spencer]

you either need to use HAVING instead of WHERE or Move the where clause. Since
you are not using any of the aggregate functions Sum, Avg, etc. it would be most
efficient to move the WHERE clause to the proper location in the query.


SELECT DISTINCT MODEL_ID, MODEL_NAME, MAKE_ID
FROM MODEL
WHERE MAKE_ID=[Forms]![Form1]![combo1]
GROUP BY MODEL_ID, MODEL_NAME
ORDER BY MODEL_NAME

You could use the following, but it will not be as efficient since the HAVING
restriction will be applied after the records are grouped. Where occurs before
the records are grouped.


SELECT DISTINCT MODEL_ID, MODEL_NAME, MAKE_ID
FROM MODEL
GROUP BY MODEL_ID, MODEL_NAME
HAVING MAKE_ID=[Forms]![Form1]![combo1]
ORDER BY MODEL_NAME

 

[John Vinson]

Can someone pls tell me why the query shown below is incorrect?

I'm trying to synchronize two combo boxes (cascading) in a form but the
query just wont accept the WHERE statement.

The order is important: the WHERE clause must come before the GROUP BY
clause.

SELECT DISTINCT MODEL_ID, MODEL_NAME, MAKE_ID

FROM MODEL

WHERE MAKE_ID=[Forms]![Form1]![combo1]

GROUP BY MODEL_ID, MODEL_NAME

ORDER BY MODEL_NAME

You're also using belt and suspenders here: the DISTINCT and the GROUP
BY are doing exactly the same thing. Try just taking the GROUP BY out
altogether.

John W. Vinson[MVP]

 

[Mike]

Thank you for replaying.

I'm still receiving an error in the query... Statement error WHERE close to
'!'

SELECT DISTINCT MODEL_ID, MODEL_NAME, MAKE_ID
FROM MODEL
WHERE MAKE_ID=[Forms]![Form1]![combo1]
ORDER BY MODEL_NAME

 

[John Spencer]

Your syntax looks as if it is correct.
For testing purposes have you tried entering a value in place of the
reference to the form control? If so, does that work? Since I don't know
your field types I am guessing that Make_Id is a text field and not a number
field.

SELECT DISTINCT MODEL_ID, MODEL_NAME, MAKE_ID
FROM MODEL
WHERE MAKE_ID = "Ford"
ORDER BY MODEL_NAME