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![]()