I am struggling with coming up with a way to create a report that reflects whether an item is still checked out.
I have a history table that shows when a book is picked up (Date and time fields)
When the book is returned another set of Date and Time fields are updated.
So far, so good. However, where I am struggling:
If the book is picked up at 10 am and not returned until 7 pm the Usage Report is supposed to show a count for the book at 10, 11, 12, 1, 2, 3, 4, etc.
The following query shows the count of items for the Pickup Time only. I am not sure how to get it to continue the count.
SELECT ConvertTime(Hour([ISL History]![CheckOutTime])) AS [Check Out Time Period], Count([ISL History].Barcode) AS [Usage Count], Hour([ISL History]![CheckOutTime]) AS [Time Period Sort]
FROM [ISL History]
WHERE ((([ISL History].CheckOutDate) Between [Forms]![Run Report]![StartDate] And [Forms]![Run Report]![EndDate]))
GROUP BY ConvertTime(Hour([ISL History]![CheckOutTime])), Hour([ISL History]![CheckOutTime])
ORDER BY Hour([ISL History]![CheckOutTime]);
--
FROM [ISL History]
WHERE ((([ISL History].CheckOutDate) Between [Forms]![Run Report]![StartDate] And [Forms]![Run Report]![EndDate]))
GROUP BY ConvertTime(Hour([ISL History]![CheckOutTime])), Hour([ISL History]![CheckOutTime])
ORDER BY Hour([ISL History]![CheckOutTime]);
--
Dawn Bleuel
"Dedicated to Living Healthier"
__._,_.___
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 (1) |
Please zip all files prior to uploading to Files section.
.
__,_._,___
Tidak ada komentar:
Posting Komentar