Subqueries in Access (1 Viewer)

[Ben_Entrew]

Hello,

I try to execute following subquery:

DoCmd.RunSQL "SELECT DISTINCT Q_PRICES.Material, Q_PRICES.Product_Class INTO TEST " & _
"FROM Q_PRICES " & _
"WHERE " & _
"(SELECT Q_PRICES.Material " & _
"FROM Q_PRICES " & _
"WHERE Q_PRICES.Valid_to Like '*2017' " & _
"GROUP BY Q_PRICES.Material " & _
"HAVING Count(Q_PRICES.Product_Class)>1) " & _
"GROUP BY Q_PRICES.Material, Q_PRICES.Product_Class"

It replies me this warning message:

At most one record can be returned by this subquery

Basically I wanna find out if there are material numbers with more than one product class name. This shouldn't occur in our data.

Any clue how I can rewrite this?

Thank you.

Regards,
Ben

 

[Ranman256]

dont write sql, create a query, bind the query to the sub form.
see if this works. It may give errors why.

 

[sneuberg]

If the subquery can produce more than one result maybe you need to use WHERE IN rather than just WHERE. Some like:

DoCmd.RunSQL "SELECT DISTINCT Q_PRICES.Material, Q_PRICES.Product_Class INTO TEST " & _
"FROM Q_PRICES " & _
"WHERE[COLOR="Blue"] Q_PRICES.Material IN[/COLOR] " & _
...

in place of

DoCmd.RunSQL "SELECT DISTINCT Q_PRICES.Material, Q_PRICES.Product_Class INTO TEST " & _
"FROM Q_PRICES " & _
"WHERE " & _
...