Duane -
How perfect! Thank you! I though there would have to be VBA and Arrays involved to get what we needed.
Thank you! Thank you! If I was still an MVP, I would have to buy you a drink at Summit. :)
Dawn
On Mon, Aug 18, 2014 at 2:34 PM, Duane Hookom duanehookom@hotmail.com [AccessDevelopers] <AccessDevelopers@yahoogroups.com> wrote:
I would create a table of all times tblTimes with TimeStart and TimeEnd fields
tblTimes 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:
qcarHourlyUsage 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
__._,_.___
Posted by: Dawn Bleuel <dlbleuel@gmail.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (5) |
Please zip all files prior to uploading to Files section.
.
__,_._,___
Tidak ada komentar:
Posting Komentar