Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2013
    Posts
    5

    Unanswered: Access Report to show 12 hour blocks

    Good morning,
    I have an Access (2010) database that I use to track in-field resources. At its base level, it tracks the check-in time and check-out time of several resources. These resources may be in-field for anywhere between 7-45 days. I am responsible for producing a report that shows the sum of resources that were checked-in and active during each 12-hour block for the duration of the event. Previously I have always achieved this with Excel but would like to do it with this Access database. Can someone help me produce a report that will show the sum of resources, by type, for each 12-hour block within a set date range? Thank you in advance.

  2. #2
    Join Date
    Nov 2011
    Posts
    413
    Here is an example:
    Minutes: DateDiff("n", [StartDateTime], [EndDateTime])

    Minutes is the alias for the calculated field; you could use any name you like. You must use "n" for DateDiff() to return minutes:

    To display this value as hours and minutes on your report, use a text box with this Control Source:

    =[Minutes] \ 60 & Format([Minutes] Mod 60, "\:00")

    You could simply then use < 12 for your criteria.


    HTH
    Last edited by Burrina; 10-01-13 at 04:01. Reason: Explanation

  3. #3
    Join Date
    Sep 2013
    Posts
    5
    Burrina,
    Thanks for the reply. I'm not 100% sure this is what I'm asking for. It looks like this will report the total time active. Is that correct? That's not exactly what I'm looking for. I need to know how many resources were active during a set timespan such as: How many of X type of resources were active between October 1, 2012 and October 10, 2012?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the only way I can think of doing this would be to create another table which stores your time periods and then join that table to your actual data

    so you pull the periods and then all rows which have a startdate <= periodstart date and enddate >= periodenddate
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Sep 2013
    Posts
    5
    Healdem,
    Ok, that's making sense. So the table in which I store the time periods, do you envision that as being just a single column table with each of the time periods stored in that 1 column? Such as 10/01/2013 06:00, 10/01/2013 18:00, 10/02/2013 06:00 etc. etc?

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by dslatten View Post
    Healdem,
    Ok, that's making sense. So the table in which I store the time periods, do you envision that as being just a single column table with each of the time periods stored in that 1 column? Such as 10/01/2013 06:00, 10/01/2013 18:00, 10/02/2013 06:00 etc. etc?
    its your system, its your design, what do you think?
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Sep 2013
    Posts
    5
    Ok Healdem. I appreciate your help. I will tinker with it and see what I come up with.

Tags for this Thread

Posting Permissions

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