Group by and count queries

  • Thread starter Thread starter hoachen
  • Start date Start date
H

hoachen

I am using MS Access 2003 and want to accomplished is to group DVD# and VCR#
that are same number and then count the quantity. For example above, there
are two same DVD# and VCR# (145A and 123B). I want to group these two and
then count and total the Qty (total of 4) After done grouping and counting, I
want to list all column information on the query.

I've tried (see below) but unsuccessful, your help would be greatly
appreciate.

SELECT testing.CompanyID, testing.[Company Name], testing.[DVD#],
testing.[VCR#], testing.[Release Year], testing.[production/studio],
testing.[Distributed To], Count(testing.Qty) AS CountOfQty, testing.Price

FROM testing

GROUP BY testing.CompanyID, testing.[Company Name], testing.[DVD#],
testing.[VCR#], testing.[Release Year], testing.[production/studio],
testing.[Distributed To], testing.Price;

Testing Table:
CompanyID|Company Name|DVD#|VCR#|Release Year|Production/Studio|Distributed
To|Qty|Price

123|ABC|112A|116Z|2001|UUU|VVV|1|10
444|ZZZ|145A|145A|2007|MMM|NNN|2|22.5
125|JFK|123B|123B|2007|OOO|PPP|2|22.5
331|LOO|451D|655J|1999|PPP|QQQ|3|15
 
I am using MS Access 2003 and want to accomplished is to group DVD# and VCR#
that are same number and then count the quantity. For example above, there
are two same DVD# and VCR# (145A and 123B). I want to group these two and
then count and total the Qty (total of 4) After done grouping and counting, I
want to list all column information on the query.

I've tried (see below) but unsuccessful, your help would be greatly
appreciate.

SELECT testing.CompanyID, testing.[Company Name], testing.[DVD#],
testing.[VCR#], testing.[Release Year], testing.[production/studio],
testing.[Distributed To], Count(testing.Qty) AS CountOfQty, testing.Price

FROM testing

GROUP BY testing.CompanyID, testing.[Company Name], testing.[DVD#],
testing.[VCR#], testing.[Release Year], testing.[production/studio],
testing.[Distributed To], testing.Price;

Testing Table:
CompanyID|Company Name|DVD#|VCR#|Release Year|Production/Studio|Distributed
To|Qty|Price

123|ABC|112A|116Z|2001|UUU|VVV|1|10
444|ZZZ|145A|145A|2007|MMM|NNN|2|22.5
125|JFK|123B|123B|2007|OOO|PPP|2|22.5
331|LOO|451D|655J|1999|PPP|QQQ|3|15
Click to expand...

You can't have it both ways! Either group by DVD# and VCR# and count the
number of records, OR display all the (non-grouped, non-repeating) data... but
to do both you'll need two queries.

What would you like the output from your four-row example to look like?
 
Back
Top