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

    Unanswered: Date Range Report with Multiple Date Fields

    Hi,

    I have previously been on this forum for help with a basic report (see http://www.dbforums.com/microsoft-ac...ml#post6540512)

    What I need to do now is make a slightly more specific report that asks the user for a start date and end date of the report. Crucially, this needs to search multiple date fields (14, to be exact) to find this information.

    Why 14 Date Fields?
    Our care staff go out and visit clients. For each day they visit (which can be anything from 0 to 14 days in a row), they input the date of the visit, the hours and minutes they were there, their name and the details of the visit. This is shown here (the screenshot shows 9, but it scrolls down to 14):

    http://img31.imageshack.us/img31/50/...28at120004.png

    My Current Report

    My current report adds up all the minutes and hours for each record (across the 14 rows), converts the minutes into hours and formats the result, so I end up with the following:

    http://img408.imageshack.us/img408/4...28at120304.png

    This is fine, and it shows me the total hours and minutes care provided. However, I also need to create a seperate report that only adds up hours from the fields that correspond to the date field. It needs to prompt the user to input a date range and then search each individual record for those dates. Where the date matches, it needs to add up the hours and minutes fields, and then move onto the next date field.

    Is this possible?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    In my view its a flawed design
    it shouldn't matter how many visits are performed per day/week/month/ year deciade
    a visit is a visit, it starts at HH:MM and ends at hh:mm, or starts at HH:MM and lasts xxx minutes
    a visit belongs in a single entity
    if you want to recreate a timesheet style report you can always get there using a report
    the way you are doing it means you have to work through sepcifric date columns presuambly with a horrendouns where clause with lots of "OR's" is
    put the visits in a single table (one visit per table then you use a simple where clause to find all values for the specified range

    by adhering to normalisation principles your task becomes easier.
    Fundamentals of Relational Database Design -- r937.com
    The Relational Data Model, Normalisation and effective Database Design
    Last edited by healdem; 02-28-12 at 08:34.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2012
    Posts
    24
    I'm sorry, I do not quite understand your reply. I am new to Access, this is the first database I have created.

    The hours and minutes are inputted into 'Number > General Number' fields, in order for it to add up correctly.

    The dates are there so we can see which date the visit was on.

    Is it not a simple fix for a report to check if date1 is within the date range, and if so collect the hours total, if not add '0'. Then check if date2 is within the date range, and if so collect the hours total, etc etc.

    In my mind, I could then repeat this for the minutes and then total the two together at the end.

    Is this not how it works?

  4. #4
    Join Date
    Feb 2012
    Posts
    24
    A small update: I have been looking into it, and I have managed to create a query whereby if date1 is within a date range it shows the details of the hours and minutes for that date. The date range is entered by referencing a form set up for this purpose.

    However, when I try to add to this and put date2 in there, I get two problems.

    1) It creates the SQL code as AND, so it only returns records where BOTH the dates are within the range.

    2) If I change the AND to 'OR', it does pick up every record that has a visit within these dates from the date1 or date2 fields, BUT it also displays BOTH visits from date1 and date2 even if one of thoise dates is NOT within the dates.

    Does this make sense? And is this what you were trying to explain to me?

Posting Permissions

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