Avatar of suvmitra
suvmitra
Flag for India 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?
Microsoft Access

Avatar of undefined
Last Comment
suvmitra

8/22/2022 - Mon
Michael Vasilevsky

Change the query?

SELECT Count([PO]) AS CountofPO
FROM mytables
GROUP BY [MONTH]
suvmitra

ASKER
Actually, I am creating this Pivot and I want to have the count of PO no..not the Sum?
1.JPG
Michael Vasilevsky

Can you post the SQL of qryPrjCC24PO?
Your help has saved me hundreds of hours of internet surfing.
fblack61
suvmitra

ASKER
As attached. thanks.
qry.txt
Michael Vasilevsky

Change it to:

SELECT Count(tblProjectCC24.PONo) As CountofPONo, tblProjectCC24.Month
FROM tblProjectCC24
GROUP BY tblProjectCC24.Month
HAVING tblProjectCC24.PODate>=DateAdd("m",-15,Date())

and you'll be able to get what you want.
suvmitra

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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Michael Vasilevsky

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
suvmitra

ASKER
Thank you =) works like a charm!