count where clause in Access Report

I would like to count all the records by status in group field in an Access Report.

I have the report built and a grouping set and I can count the records according to the grouping (Location)

Now I would like to do a count of the records by location and by status so I can show the following:

Oklahoma  200  /   In Progress   150   /    Scheduled    25    /      On Hold  20      /     Canceled    5

Texas          100  /   In Progress   50   /    Scheduled    25    /      On Hold  20      /     Canceled    5


I would like to count records according to P_Status  where P_Status equals 'In Progress'

and I would like to count records according to P_Status  where P_Status equals 'Scheduled'

some I am thinking something like the following

=count (all records) where P_Status = In Progress

and

=count (all records) where P_Status = Scheduled

...novice here so please be specific
ssblueAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chaauCommented:
I suggest you built a CROSS TAB report instead. Run the wizard - you will be surprised how simple it is
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
There is a lot of info missing here...
What does your source data look like?
Is what you posted the "exact" output you are looking for?
...or do you want that format to appear appended somewhere on your existing report?
You did not post a screenshot of your existing report or layout.

In any event...
I will presume that you need this summary at the "end" of the report (Report Footer)
...then you have to hardcode each State and Status into each textbox
Something like this:
=DCount("Status","YourTable","Status='In Progress'" & " AND " & "State='Oklahoma'")

Sample and screenshot attached
summaryDatabase175.mdb
1
Jeffrey CoachmanMIS LiasonCommented:
I am sure you can follow the syntax logic and apply it to the other "Status" values.

Again, the kicker here is that, for a Report Summary, all the discrete values must be hardcoded.
Meaning you will have to type (into each textbox controlsource) each state name and status.

;-)

Jeff
1
PatHartmanCommented:
Rather than hard-coding the discrete values (which is always problematic), use the crosstab approach suggested by chaau but embed it as a subform in the report footer section so it appears at the end of the report.
1
Jeffrey CoachmanMIS LiasonCommented:
Yes, ...First please give the crosstab technique a shot.

When I first read the question:
1. ...I presumed, (perhaps wrongly so), that you may have only had a few discreet values.
2. I also thought that what you posted as a result, was the exact format you were after.
3. I also presumed that this would be in the report footer, ...and as controls, ...it would be easier to manipulate them in a way that might not be possible (easily) with a crosstab.

So, yes, give the crosstab a shot, ..it may not give you the "exact" output you need, ...with no maintenance required.

;-)

Jeff
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.