Solved

count where clause in Access Report

Posted on 2016-08-09
5
41 Views
Last Modified: 2016-08-12
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
0
Comment
Question by:ssblue
  • 3
5 Comments
 
LVL 24

Accepted Solution

by:
chaau earned 250 total points
ID: 41749559
I suggest you built a CROSS TAB report instead. Run the wizard - you will be surprised how simple it is
1
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 41750701
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
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 250 total points
ID: 41750817
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
 
LVL 34

Expert Comment

by:PatHartman
ID: 41750844
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 41751057
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

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Auto Filter in Combo Box 7 34
MS accesss using VBA to change a default value 5 30
Alter an update query which rounds 7 33
Export Query data to excel file 14 39
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now