I would create a table of all times tblTimes with TimeStart and TimeEnd fields
You can then use this table with your ISL History table to create a cartesian query like:
SELECT tblTimes.TimeStart, tblTimes.TimeEnd, Count([ISL History].BarCode) AS CountOfBarCode
FROM [ISL History], tblTimes
WHERE (((tblTimes.TimeStart)<=[CheckinTime]) AND ((tblTimes.TimeEnd)>=[CheckOutTime]))
GROUP BY tblTimes.TimeStart, tblTimes.TimeEnd;
You should get something like:
Duane Hookom MVP
MS Access
To: AccessDevelopers@yahoogroups.com
From: AccessDevelopers@yahoogroups.com
Date: Mon, 18 Aug 2014 11:34:19 -0500
Subject: Re: [AccessDevelopers] Usage Report
| TimeStart | TimeEnd |
|---|---|
| 12:00:00 AM | 1:00:00 AM |
| 1:00:00 AM | 2:00:00 AM |
| 2:00:00 AM | 3:00:00 AM |
| 3:00:00 AM | 4:00:00 AM |
| 4:00:00 AM | 5:00:00 AM |
| 5:00:00 AM | 6:00:00 AM |
| 6:00:00 AM | 7:00:00 AM |
| 7:00:00 AM | 8:00:00 AM |
| 8:00:00 AM | 9:00:00 AM |
| 9:00:00 AM | 10:00:00 AM |
| 10:00:00 AM | 11:00:00 AM |
| 11:00:00 AM | 12:00:00 PM |
| 12:00:00 PM | 1:00:00 PM |
| 1:00:00 PM | 2:00:00 PM |
| 2:00:00 PM | 3:00:00 PM |
| 3:00:00 PM | 4:00:00 PM |
| 4:00:00 PM | 5:00:00 PM |
| 5:00:00 PM | 6:00:00 PM |
| 6:00:00 PM | 7:00:00 PM |
| 7:00:00 PM | 8:00:00 PM |
| 8:00:00 PM | 9:00:00 PM |
| 9:00:00 PM | 10:00:00 PM |
| 10:00:00 PM | 11:00:00 PM |
| 11:00:00 PM | 12/31/1899 |
You can then use this table with your ISL History table to create a cartesian query like:
SELECT tblTimes.TimeStart, tblTimes.TimeEnd, Count([ISL History].BarCode) AS CountOfBarCode
FROM [ISL History], tblTimes
WHERE (((tblTimes.TimeStart)<=[CheckinTime]) AND ((tblTimes.TimeEnd)>=[CheckOutTime]))
GROUP BY tblTimes.TimeStart, tblTimes.TimeEnd;
You should get something like:
| TimeStart | TimeEnd | CountOfBarCode |
|---|---|---|
| 7:00:00 AM | 8:00:00 AM | 7 |
| 8:00:00 AM | 9:00:00 AM | 4 |
| 9:00:00 AM | 10:00:00 AM | 3 |
| 10:00:00 AM | 11:00:00 AM | 5 |
| 11:00:00 AM | 12:00:00 PM | 9 |
| 12:00:00 PM | 1:00:00 PM | 6 |
| 1:00:00 PM | 2:00:00 PM | 2 |
| 2:00:00 PM | 3:00:00 PM | 4 |
| 3:00:00 PM | 4:00:00 PM | 6 |
| 4:00:00 PM | 5:00:00 PM | 4 |
| 5:00:00 PM | 6:00:00 PM | 1 |
| 6:00:00 PM | 7:00:00 PM | 2 |
| 7:00:00 PM | 8:00:00 PM | 2 |
| 8:00:00 PM | 9:00:00 PM | 1 |
| 9:00:00 PM | 10:00:00 PM | 1 |
| 10:00:00 PM | 11:00:00 PM | 1 |
| 11:00:00 PM | 12/31/1899 | 1 |
Duane Hookom MVP
MS Access
To: AccessDevelopers@yahoogroups.com
From: AccessDevelopers@yahoogroups.com
Date: Mon, 18 Aug 2014 11:34:19 -0500
Subject: Re: [AccessDevelopers] Usage Report
Thanks Duane!
I have been told by the project manager that an item would never be checked out overnight. :)
This is the report that I currently have for the day's worth of sample data. However, it only counts an item at the time when it is checked out. The client wants to know when her peak usage occurs. I think she will probably run her reports on a monthly basis, but they could be done on a quarterly basis. The client doesn't care about partial hours. A partial hour counts as a single hour. So if an item is checked out at 9:45, and then back in at 10:30, it counts in both the 9 and 10 counts.
| Check Out Time Period | Usage Count |
|---|---|
| 7 | 7 |
| 8 | 3 |
| 9 | 2 |
| 10 | 4 |
| 11 | 4 |
| 12 | 4 |
| 13 | 1 |
| 14 | 4 |
| 15 | 2 |
| 16 | 3 |
| 18 | 1 |
Below is a sample day's worth of data. There are several line items that span multiple hours, in which case the report should have reflected that information by increasing the count in the hours following the CheckOutTime
| Barcode | CheckOutDate | CheckOutTime | CheckInDate | CheckInTime |
|---|---|---|---|---|
| 100002 | 1/21/2014 | 7:29:54 AM | 1/21/2014 | 7:54:08 AM |
| 100002 | 1/21/2014 | 7:39:34 AM | 1/21/2014 | 7:54:10 AM |
| 100002 | 1/21/2014 | 7:46:24 AM | 1/21/2014 | 7:55:55 AM |
| 100002 | 1/21/2014 | 7:46:36 AM | 1/21/2014 | 7:55:57 AM |
| 100002 | 1/21/2014 | 7:51:32 AM | 1/21/2014 | 7:57:22 AM |
| 100002 | 1/21/2014 | 7:51:48 AM | 1/21/2014 | 9:26:57 AM |
| 100002 | 1/21/2014 | 7:51:56 AM | 1/21/2014 | 7:54:14 AM |
| 100002 | 1/21/2014 | 8:03:50 AM | 1/21/2014 | 8:46:12 AM |
| 100002 | 1/21/2014 | 8:09:31 AM | 1/21/2014 | 8:46:13 AM |
| 100002 | 1/21/2014 | 8:10:00 AM | 1/21/2014 | 8:16:51 AM |
| 100002 | 1/21/2014 | 9:00:42 AM | 1/21/2014 | 9:03:53 AM |
| 100002 | 1/21/2014 | 9:45:00 AM | 1/21/2014 | 11:16:38 AM |
| 100002 | 1/21/2014 | 10:47:57 AM | 1/21/2014 | 11:16:39 AM |
| 100002 | 1/21/2014 | 10:52:52 AM | 1/21/2014 | 11:16:40 AM |
| 100002 | 1/21/2014 | 10:56:18 AM | 1/21/2014 | 11:16:41 AM |
| 100002 | 1/21/2014 | 10:57:33 AM | 1/21/2014 | 11:08:23 AM |
| 100002 | 1/21/2014 | 11:02:42 AM | 1/21/2014 | 11:16:42 AM |
| 100002 | 1/21/2014 | 11:13:26 AM | 1/21/2014 | 11:16:43 AM |
| 100002 | 1/21/2014 | 11:28:55 AM | 1/21/2014 | 12:52:50 PM |
| 100002 | 1/21/2014 | 11:58:06 AM | 1/21/2014 | 12:52:52 PM |
| 100002 | 1/21/2014 | 12:10:30 PM | 1/21/2014 | 12:52:53 PM |
| 100002 | 1/21/2014 | 12:23:50 PM | 1/21/2014 | 12:52:56 PM |
| 100002 | 1/21/2014 | 12:36:21 PM | 1/21/2014 | 12:52:58 PM |
| 100002 | 1/21/2014 | 12:53:23 PM | 1/21/2014 | 1:44:41 PM |
| 100002 | 1/21/2014 | 1:26:04 PM | 1/21/2014 | 1:44:39 PM |
| 100002 | 1/21/2014 | 2:15:38 PM | 1/21/2014 | 3:19:02 PM |
| 100002 | 1/21/2014 | 2:16:26 PM | 1/21/2014 | 3:19:00 PM |
| 100002 | 1/21/2014 | 2:16:37 PM | 1/21/2014 | 3:18:58 PM |
| 622622 | 1/21/2014 | 2:29:46 PM | 1/21/2014 | 3:18:55 PM |
| 100002 | 1/21/2014 | 3:30:52 PM | 1/21/2014 | 3:45:10 PM |
| 100002 | 1/21/2014 | 3:42:30 PM | 1/21/2014 | 11:59:00 PM |
| 100002 | 1/21/2014 | 4:12:45 PM | 1/21/2014 | 4:32:58 PM |
| 100002 | 1/21/2014 | 4:18:03 PM | 1/21/2014 | 4:33:02 PM |
| 100002 | 1/21/2014 | 4:28:51 PM | 1/21/2014 | 4:33:03 PM |
| 100002 | 1/21/2014 | 6:51:23 PM | 1/21/2014 | 7:56:41 PM |
On Mon, Aug 18, 2014 at 10:56 AM, Duane Hookom duanehookom@hotmail.com [AccessDevelopers] <AccessDevelopers@yahoogroups.com> wrote:
I'm not sure what you actual data looks like and how you want this represented/rendered in your report. Do you run usage reports for previous times or just current status?
Can you provide some significant sample data with the desired output?
Duane Hookom MVP
MS Access
__._,_.___
Posted by: Duane Hookom <duanehookom@hotmail.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (4) |
Please zip all files prior to uploading to Files section.
.
__,_._,___
Tidak ada komentar:
Posting Komentar