This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.
Course Of The Month
9 days, 1 hour left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
count where clause in Access Report
Posted on 2016-08-09
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
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
3
5 Comments
Active 7 days ago
I suggest you built a CROSS TAB report instead. Run the wizard - you will be surprised how simple it is
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","YourTabl
Sample and screenshot attached
Database175.mdb
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","YourTabl
Sample and screenshot attached
Database175.mdb
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
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
Active today
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.
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
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
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications. You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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 …
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month9 days, 1 hour left to enroll
617 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.