Questions about doing a report

[Gwen61]

I think I have the db table the way I want it, sort of, but I'm hung up on how to extract the neccessary data for a simple report. I'll try to explain what I need.

The db is going to be used by 5 people (Service Officers), who will be accumulating a bunch of info from each person that contacts them, but ultimately, a simple report is needed that shows the number of contacts each of the 5 people made during a particular month, and the number of services provided to the contact.

The report column headings would be: Service Officer - Month - # of Contacts - # of Services

My dilemma is that the db I designed has all the Service Officer's initials in a Combo Box. Also, not just the month, but also the date and the year in one field, and all the services that can potentially be provided - monetary & non-monetary, in two separate fields, all listed in a Combo Box.

These are the questions I have:

Have I made the db too detailed to provide such a simple report?

Rather than have the entire date in one field, do I need to have 3 different fields - one for month, one for date & one for year?

Do I need to have 5 different tables - one for each service officer?

Plus, I need to be able to provide the total services provided in each given month, and I haven't yet been able to figure out how to do that.

I hope I've provided enough info to get some suggestions, but if not, please let me know.

Thanks.

 

[Alansidman]

Look to base your report(s) on queries. You can specify the particular dates or range of dates, the service officer, etc.

Alan

 

[Gwen61]

So, would I do 5 reports, one for each service officer?

Another thought I had since I posted was to have each service officer enter their info in Excel, import each spreadsheet into Access as a separate database, then generate reports from each table.

Does that make any sense?

 

[ArtMiles]

These are the questions I have:

Have I made the db too detailed to provide such a simple report?

NO

Rather than have the entire date in one field, do I need to have 3 different fields - one for month, one for date & one for year?

Have one field with the date only. In a query you can check the date like this Month([DateField]), Year([DateField]).

Do I need to have 5 different tables - one for each service officer?

No, add a field to the table to identify the service officer.

Plus, I need to be able to provide the total services provided in each given month, and I haven't yet been able to figure out how to do that.

Use a select query and select the Totals option from the view menu. In the 'Field:' row enter something like this Month:Month([DateField]) and GroupBy this.


Hope this helps.

Art

 

[stopher]

I agree with Art. Only one table is needed and thus you can create a report showing contacts for each service officer (all service officers on one report). The only other point I would add is that you can do the grouping in the report. This is particularly useful if you want to show a list of contacts for each service officer by month and then show a count of contacts for the month. In other words don't do the aggregation in the query.

The most important this is to get your table design right. Perhaps you could post your database for comment.

Chris

 

[Gwen61]

Thank you very much for your comments, Art and Stopher. I've been focused on something else today - I needed a break from the db - but I'll be back at it tomorrow.

How does one post a db on here?

 

[stopher]

Thank you very much for your comments, Art and Stopher. I've been focused on something else today - I needed a break from the db - but I'll be back at it tomorrow.

How does one post a db on here?

Explanation of how to post attachments is here
Chris

 

[Gwen61]

Thank you for the instructions on how to post attachments, Stopher. I've not forgotten this thread, but the person in charge of the project won't be back in the office until 1/5. I'm in a holding pattern until then, but after I have a better idea of what he wants for the end result, I'll be back.

Thanks again.