Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2012
    Posts
    24

    Question Unanswered: Help Needed With Report Design - (incl. Adding Multiple Fields within a Date Range)

    Hi,

    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.

    Background Info
    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.

    The Database
    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.

    http://img189.imageshack.us/img189/3...oursfields.png

    Uploaded with ImageShack.us

    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:

    http://img12.imageshack.us/img12/5962/daterange.png

    Uploaded with ImageShack.us

    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.

    Thank you for your time.
    Attached Files Attached Files

  2. #2
    Join Date
    Feb 2012
    Posts
    24
    Hi,

    Sorry to bump this, but I am still struggling. Am I right in thinking that I need to have an 'hours' and a 'minutes' field, and then convert the minutes to hours and add it to the hours?

    How would I do this?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •