Is this possible? Regarding dates

[Kat2016]

Good day,

I have a question, but am unsure if what I'm thinking of doing is possible.

I have a table that consists of slot machine data. The casino is open from 8 am to 3 am the next day.
Any activity for a machine within that time they consider to still be counted as the first day.

For example if I have a transaction date starting at 2016/01/01 08:00 and the last transaction date for the period is 2016/01/02 02:22
the casino counts the transactions that were before 2:22 am on the second day to be part of the first days period.



What I need to do is build a summarized query of these transactions, but being that I need to group by transaction dates I have an issue grouping any of the transactions after 2016/01/23:59 where I want them.

I have thought of adding another field but unfortunately since I am importing this data from reports generated in Excel I can not do that.

Is there any other way to group these time periods together to summarize my data?

Thanks.

[CJ_London]

Is there any other way to group these time periods together to summarize my data?

yes

3am is very specific - is it possible to creep to 3:05 or a bit later? Assuming not then

datevalue(dateadd("n",-180,transactiondate))

should do it

it deducts 180 minutes (3 hours) from your transaction date/time so 3am becomes midnight of the previous day (tho best to check - may need to make it 181 minutes to be sure). at the other end deducting 180 minutes from 8am still leaves it in the same date

[Kat2016]

Thank you again. You have solved all my problems today.