MS Query SQL problem

[daggers]

Hi,

If I have the following code in a SQL statement in MS Query:

...WHERE table_x.variable_y='A'
GROUP BY table_x.variable_z....

and run it, it appears to ignore the first line and groups the entire content of table_x rather than grouping the content of table_x and restricting the grouped content to where variable_y='A'. Is there any way of doing this?

Thanks.

 

[WillRiley]

This works for me

SELECT cust.so, Count(cust.no)
FROM pdunity.dbo.cust cust
WHERE (cust.status<>'active')
GROUP BY cust.so

Do you have your aggregate functions set up correctly...

Do you get the right number of records if you exclude the GROUP BY statement?

Will

 

[frainbreeze]

mmh, that code looks ok to me.. it may be a silly question, but have you checked that there are records ='A' in table_x? and that the name is called variable_y?

maybe try using

...WHERE table_x.variable_y LIKE 'A'
   GROUP BY table_x.variable_z....

even though the = should suffice..maybe access is just being access. if that still doesnt work maybe try doing it through Access, i.e. set 'A' in the Criteria row and then have Sort by variable_z

 

[daggers]

WillRiley:
If I exclude the GROUP BY statement, I get the right number of records for the criteria table_x.variable_y='A'...I am just at a loss to show those records aggregated by variable_z as well.

How do I set up aggregate functions? 'Help' is no use..

 

[daggers]

I have worked out what the problem is...

The first line of code should have been

SELECT SUM(table_x.variable_z) AS 'Total'

rather than

SELECT table_x.variable_z