Unanswered: Help Needed With Report Design - (incl. Adding Multiple Fields within a Date Range)
I am new to this forum, and I always feel a little rude joining a community when my first post is to request help, but needs must I'm afraid.
Prior to last week, I had never used Access before but my boss wanted an excel spreadsheet converting into a access database. I have managed to create a database, and even a form to make it look a little prettier. I even managed to import all my data from excel.
I was feeling very proud of myself until my boss reminded me that he needed reports from this. I am now stuck as I have no idea how to do this.
I am using Access 2010. I have attached a copy of my database (with no records, for data protection).
The point of this database is to record the visits made by our care staff to clients. This is an emergency care service, and the maximum care time is 7 days, so there are fields to input data for visits on 7 seperate days. These fields are at the bottom of the form. Most clients will only need care for 2 or 3 days, so most of these fields remain blank. The rest of the form/database is just information we need to keep, but not report on.
What I Would Like
Help with designing two reports that mainly use the fields in the bottom-most section of the form, headlined 'Ongoing Care Tasks Provided'.
Report 1: Total Hours Provided Per Individual
With this, I would like the report to show every record's name and address (from the second section of the form), plus the total number of hours provided. This total would come from adding together the figures entered into the 7 'Total Hours Provided' fields. At the bottom of the report, I would like a total hours field that adds up all of the individual totals.
Report 2: Total Hours Provided Within a Date Range Per Individual
This report would be similar to the report above, adding up the 'total hours provided' fields showing a total next to the name and address of the client, plus a 'total' box at the bottom adding up all of these individual totals.
However with this one, I would like to be able to input a date range (normally a weekly thing, but would need to be flexible so I can input a 'date from' and a 'date to' and it would pull the correct info.
The difficulty will probably be because we have multiple date fields, as shown here:
I have a little experience of macros in excel, so I assume the logic of this report would go along the lines of:
Input Date Range
Lookup Record 1
If the field '1) Date' is within the date range, select the '1) Total Hours Provided' figure, if not within date range, add '0'.
If the field '2) Date' is within the date range, select the '2) Total Hours Provided' figure and add it to the first figure, if not within date range, add '0'.
And so on through the 7 rows, and then through all the records.
Does this make sense? If I have not given enough information or not been clear enough feel free to ask for clarifications and I will try to explain a little better.
If somebody could point me in the right direction to solving this problem I would be very grateful.