Results 1 to 2 of 2
  1. #1
    SDCragg is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    2

    Talking Help with mulitple count fields in one query

    Hi, I'm totally stuck and need some help. I am making a database that records when assets are borrowed and when they are returned (they are borrowed and returned on the same day). I want a report to say how many are borrowed in a range of times (it's for a school so I want to know if they're borrowed before school, during 1st break, second break, etc.) and same for returned. Just to see how much traffic is to be expected on a given day and break for staff and asset management and whatnot.

    I have a table called Scans. I have two relevant fields: ScanTime (borrowed time) and ReturnTime. These contain General Dates
    I have a form called DailyReport with text fields called StartDate and EndDate.
    Everything is needed in the format dd/mm/yyyy and hh:mm

    I want a query that will tell me, in this date or range of dates between DailyReport.StartDate field and DailyReport.EndDate field, how many records had a Scans.ScanTime between:
    07:00 and 09:30 (as BorrowBeforeSchool)
    09:31 and 11:15 (as Borrow1stBreak)
    11:16 and 13:30 (as Borrow2ndBreak)
    13:31 and 14:45 (as Borrow3rdBreak)


    14:46 and 17:30 (as BorrowAfterSchool)

    and the same for Scans.ReturnTime.
    so i want a count of how many assets were borrowed in those times and how many returned in those times
    Also, if possible, a count of all borrowed (ScanTime) between 09:00 and 10:05, 10:35 and 11:40, 12:35 and 13:40, and 14:00 and 15:05, (as BorrowDuringClass) and one for ReturnTime in the same times (as ReturnDuringClass), so we know how many assets are being borrowed and returned during classes.

    Best I can do is a separate query for each of the above, put them in a form each and copy the values across to text fields in the forms but it's a massive job with so many needed.

    Can anyone help me with a query? Pretty please?
    Thanks

  2. #2
    SDCragg is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    2
    Also a total of how many borrowed (as BorrowTotal) and returned (ReturnTotal) would be awesome

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 08-26-2013, 05:08 PM
  2. Replies: 1
    Last Post: 08-01-2013, 12:24 PM
  3. 'Count' two fields in a Query
    By dr4ke in forum Queries
    Replies: 2
    Last Post: 01-18-2013, 10:00 AM
  4. Search mulitple fields
    By wgroenewald in forum Forms
    Replies: 1
    Last Post: 02-23-2012, 11:46 AM
  5. Multiple count of query fields
    By BLD21 in forum Queries
    Replies: 2
    Last Post: 05-23-2011, 01:09 PM

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
  •  
Other Forums: Microsoft Office Forums