Solved Access report to count types of cases, not all together

Microsoft Office 2010 professional
July 17, 2013 at 10:45:36
Specs: Windows XP
I'm trying to write a manager's report in Access 2010 and am trying to figure out how to sperate cases, regions, names, etc. There are five fields: Name (employee name, 70+); Region (three choices); Review Type (also three choices); Date Taken (start date); and Client Number (possiblity of duplicates but usually a single entry)

Right now, when I run the report, I placed '=count(*)' in the Control Source, so I get back results like this; 6886, 6886, 6886, 6886, and the Client number, each one individually (a 9 char number) and it goes on for hundreds of pages.

The results I want are --
Name Region Review
Jane Doe West 195 Event 9
Trigger 150
New 35
South 60 Event 2
Trigger 33
New 25
North 213 Event 150
Trigger 80
New 83
etc.
It should be like this for every one of my employees. How can I do this? Would I still use the count(*)? I've written formulas for Excel so I am familiar with the process if that's what's necessary.

Anyone have any ideas?


See More: Access report to count types of cases, not all together

Report •

#1
July 17, 2013 at 12:37:28
✔ Best Answer
It's hard to describe without going into so much detail that I end up writing a book... but if I understand your question correctly, I believe you just want to group and count your data in a report. See the site below on how to do that.

http://office.microsoft.com/en-us/a...

I didn't read completely through the article I linked above, but I still want to mention an easier way of creating reports. If your DB is in a front-end/back-end configuration you will always want to create your reports in the front-end but they will be based on the tables in the back-end. Since your front-end is linked to the back-end, all you need to do is open the linked table that you want to create the report on in the front-end and click on the 'Create' tab and click on the 'Report' button. It will create a report using the table that is open. All you have to do is right-click on the tab of the report, change it to design view and click the 'Group & Sort' button in the menu ribbon.

Another helpful note for you: you can assign forms, reports or just about anything really to a button. So if you want to have a form specifically for reports, you can create a separate button for each report, use a macro to open the report. One more thing, you can put buttons on reports so you can click it to close them. You can also set those buttons to not print when you print the report.

I hope this answers your questions!!!! Let me know if you have any other questions!

-Newbie10

Law of Logical Argument: Anything is possible if you don't know what you're talking about.


Report •

#2
July 19, 2013 at 06:53:27
Thanks, Newbie! That was a good link, I am picking up some answers from it but you know I'll have more questions....as a matter of fact, here are a couple!

1) Is there a way to feed one table with another? Example: Table masterdata has the field for TaskOwnerMGR but the field is mostly empty. TaskOwner and TaskOwnerMGR are both on table picklist-taskowner so can I get 'masterdata' to populate it's TaskOwnerMGR field by reading picklist-taskowner?

2) How do I get a Summary report? I've been grouping and sorting for days now but all I really need is something that counts the TaskOWner or Analyst fields and tells me how many times each name shows up under the three regions. I want to know that Jane Doe did 14 reviews from the north and 12 from the west. And I'll want to know how many she did during one time period (say a week). What I keep getting is Jane Doe's name listed 14 times, with the number 14 at the end (in the footer) or at the beginning of the list (in the header.) And this is WITH the group option selected.

3) What am I doing wrong?!? Any ideas, Newbie10?

Thanks!


Report •

#3
July 19, 2013 at 07:42:44
To answer question #1, yes, it is possible. In the Front-End, you will need to create an 'update query' that includes the two tables in question (masterdata and the taskowner table) all you have to do is set a relationship between the two tables, which in this case would be the 'TaskOwner' field, set the update to in the TaskOwner field equal to [TaskOwner]![Task Owner MGR Name]. See the link below and the attached screen capture for an example.

http://office.microsoft.com/en-us/a...

I want to make sure my screen capture worked... I will answer your othr questions in my next post...

Law of Logical Argument: Anything is possible if you don't know what you're talking about.


Report •

Related Solutions

#4
July 19, 2013 at 07:45:51
Trying to attach screen capture again...

UPDATE: Shoot, it didn't work...

Law of Logical Argument: Anything is possible if you don't know what you're talking about.


Report •

#5
July 19, 2013 at 07:59:03
Last Screen Capture attempt... Hopefully this time it works, because it is a very simple query, just hard to describe.

Law of Logical Argument: Anything is possible if you don't know what you're talking about.


Report •

#6
July 19, 2013 at 08:35:04
To answer questions 2 & 3, I would say, what you want to do is completely possible, it will just take some trial and error. You will need to group the report by Task Owner then have a sub group for region. you can create counts at any level of the report (group count, total count, etc. but putting the count field in the group heading)

It sounds like your just grouping them wrong, if you group them by name first then by region, I think you will get the results you want. The other reason why you could be getting duplicate names is because they may be slightly different in the database? JohnDoe vs. John Doe for example. Same name, but the database identifies them as different.

Law of Logical Argument: Anything is possible if you don't know what you're talking about.


Report •

Ask Question