Counting on a Report

[Emma35]

Hi All,
I'm stuck again :banghead:....sorry. Last thing here and then the database is finished. When entering information on the form, there is a combo box with 4 options

Started
In Progress
Verified
Complete

There are several different areas on site here and i'd like to be able to set up a report which will count the amount of the above 4 possibilities for each section. For example i'd like the report to look something like below

Area A
Started 1
In Progress 6
Verified 3
Complete 5

Area B
Started 3
In Progress 9
Verified 21
Complete 11

So i'm displaying the number of jobs in each section and how far along they are at a glance

Thanks again ,
Emma

 

[pr2-eugin]

Look into Sub Reports.

 

[Emma35]

Don't really know how a subreport might help me here. Is there a way i could set up the query to make it work....like maybe add extra fields using expressions or something ?. I've been trying to do it with the grouping and sorting part of the report but nothing seems to be working

 

[pr2-eugin]

Okay here is what Google gave me : http://office.microsoft.com/en-gb/access-help/create-and-use-subreports-HA010209281.aspx

Looks exactly what you want it to be. I may be wrong !

 

[nigeve]

How about grouping by area and adding =Count(Started) in the detail section (in a textbox control source)..?

 

[Emma35]

Thanks for the suggestions pr2-eugin and nigeve. I looked into both but can't get them working. I don't think subreports will help here as the database is very small and only has one talbe with 6 fields. I'm also getting an error message when i tried nigeve's code so no luck there either. I've been trying to use the grouping and sorting feature of the report itself and i think i'm nearly there. I'm attaching a picture of the results i'm getting and you can see that it is counting each status but also listing each one which i don't want

 

[pr2-eugin]

Can you upload a Stripped DB please?

How to Upload a Stripped DB.

To create a Sample DB (to be uploaded for other users to examine); please follow the steps..

1. Create a backup of the file, before you proceed..
2. Delete all Forms/Queries/Reports that are not in Question (except the ones that are inter-related)
3. Delete auxiliary tables (that are hanging loose with no relationships).
4. If your table has 100,000 records, delete 99,990 records.
5. Replace the sensitive information like Telephone numbers/email with simple UPDATE queries.
6. Perform a 'Compact & Repair' it would have brought the Size down to measly KBs..
7. (If your Post count is less than 10 ZIP the file and) Upload it..

Finally, please include instructions of which Form/Query/Code we need to look at. The preferred Access version would be A2003-A2007 (.mdb files)

 

[Emma35]

Thanks for your help. I can't change the version back to .mdb because it's telling me that some of the features in use are not compatible with the older version...sorry.

 

[pr2-eugin]

I have changed the simple SELECT Query to a TOTALS Query and you get the desired result ! Hope this helps. Good Luck

 

[Emma35]

Yes that's exactly what i wanted. Never thought of trying that. Thanks a lot for your help....much appreciated.

Em x

 

[pr2-eugin]

No problemo Good Luck !