suvmitra
asked on
MS Access 2003 Pivot Table Count
Hi,
I am trying to create a pivot table in MS Access 2003. I have only two fields in my query and I have inserted [Month] in the Row Area and I have inserted [PO] in the data area .. now access automatically Summing up the PO values .. but I need the count of the POs for the Month..So how do I change the Sum function to Count Function?
I am trying to create a pivot table in MS Access 2003. I have only two fields in my query and I have inserted [Month] in the Row Area and I have inserted [PO] in the data area .. now access automatically Summing up the PO values .. but I need the count of the POs for the Month..So how do I change the Sum function to Count Function?
ASKER
Actually, I am creating this Pivot and I want to have the count of PO no..not the Sum?
1.JPG
1.JPG
Can you post the SQL of qryPrjCC24PO?
ASKER
As attached. thanks.
qry.txt
qry.txt
Change it to:
SELECT Count(tblProjectCC24.PONo) As CountofPONo, tblProjectCC24.Month
FROM tblProjectCC24
GROUP BY tblProjectCC24.Month
HAVING tblProjectCC24.PODate>=Dat eAdd("m",- 15,Date())
and you'll be able to get what you want.
SELECT Count(tblProjectCC24.PONo)
FROM tblProjectCC24
GROUP BY tblProjectCC24.Month
HAVING tblProjectCC24.PODate>=Dat
and you'll be able to get what you want.
ASKER
Getting an error saying: You tried to execute a query that does not include the specified expression 'PODate' as part of an aggregate function.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you =) works like a charm!
SELECT Count([PO]) AS CountofPO
FROM mytables
GROUP BY [MONTH]