423,851 Members | 1,356 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,851 IT Pros & Developers. It's quick & easy.

Access Report to count occurrences instead of listing every one individually

P: 27
Hello again,

So it seems that the DB that you all helped me build and get right is a complete hit. Now they want reports based on what we are collecting. Long story very short......I have no idea how to code a report to do what they want.

In this DB, there is one table and two forms. First form, requests are placed, in the second form, requests are completed.

The completed by field is what they want a report based off of.

They are asking for the name of the person that completed a record followed by the number of records that they marked completed followed by their percentage of the total records completed.

it sounds easy enough, so I told them I would give it a try.
What I have made is a report that lists every value in order from the completed by field and followed by a box that lists the total number of records.......37 times one right after another.

I know how to arrange this information in excel and could make a report through that. What i am wondering if if Access will formulate this report for me without me having to do the manual work in excel.

I would prefer to learn the skills that I need to solve this, but I need a direction.

Any help is appreciated.

-Tony
Oct 5 '18 #1
Share this Question
Share on Google+
6 Replies


P: 27
Funny thing is, I just received an email that tells me they also would like the ability to change the date range in this report as well as have a nice pie chart that displays the information as well.

I believe that I am in over my head on this.
Oct 5 '18 #2

PhilOfWalton
Expert 100+
P: 1,376
Reports are very versatile in Access and they have sufficiently good graphical abilities for most people, including pie charts. I believe Excel has more graphical potential, but I strongly doubt that you need to go there.

We are going to need a lot more information if you expect to get further help

Phil
Oct 5 '18 #3

P: 27
Yes Sir!

OK, very simply DB that i built with some help from this forum. it is tracking change requests from the first form and the second form filters out the ones that have not been completed, the adjuster completed the request and puts there name to it, they click save and an email is generated and sent to the original requester.

The report that they are asking for is based on the "work completed by" column. what they want is a clean looking report that lists each adjuster's name if the first column, the total number of requests that they completed in the second column followed by the total number of requests in the DB and follow that by the percentage that they completed.

They are also asking for the ability to chose a date range for this report and a pie chart that shows the total amount of requests and each adjuster would be a slice of the pie in a different color.

Like I said, I can do this in Excel by using =countif arguments and making a pie chart. but it would be nice to be able to have access do this for me. I started to make a report, but the first issue that i am running into is instead of saying the adjusters name once followed by a count of the records, it is listing the adjusters name 37 times followed by the number 37, another 37 times. I am including two pictures, the first one is what I am getting from the report that I don't know how to change and the second is rather what I am wanting.

Not sure if this can be done.....I sure hope so.

The other request, is for the user to be able to adjust the parameters of the report, like a start and end date that this report would prompt for.

I learned how to write some very simple VBA code for my output email, but after that i believe that i know just about nothing in Access.
Attached Images
File Type: jpg current report.jpg (50.0 KB, 8 views)
File Type: jpg What i want report.JPG (41.3 KB, 9 views)
Oct 5 '18 #4

PhilOfWalton
Expert 100+
P: 1,376
OK, here are some pointers

This is a portion of a form to enter dates:-



Assume the form is called "FrmDates", and the 2 dates are called TxtStartDate and TxtEndDate

Then on pressing the Calculate button, generate a query which is to be the RecordSource of your Report with the following Criteria. On the OnOpen Event of your report, use the query as the RecordSource

Expand|Select|Wrap|Line Numbers
  1. Between Forms!FrmDates!TxtStartDate and Forms!FrmDates!TxtEndDate
Phil
Attached Images
File Type: png Form Dates.png (4.7 KB, 107 views)
Oct 5 '18 #5

P: 27
Phil,

I like that. It seems easy to understand. Can you send me to a tutorial that can help me learn how do set up between this form, query and the report?

-Tony
Oct 5 '18 #6

PhilOfWalton
Expert 100+
P: 1,376
Tony, see a private message.

Phil
Oct 5 '18 #7

Post your reply

Sign in to post your reply or Sign up for a free account.