Hi fellow data addicted people...
Today I've got a question I've absolutely no clue how to tackle it - so far I did this in Excel but want to move it into Access to better join it with other data.
Two tables (see attached screenshot).
Table1 got a date and two time columns. Table2 has a similar structure.
What I want is to count/sum occurrences in Table2 where it's date & time columns are matching those in Table1.
For example the data of entry pKey = 9 of Table2 is:
DateFull = 1/2/2021
TimeStart = 9:30
TimeEnd = 14:30
Now the criteria to update a row in Table1 would be:
Table2.DateFull = Table1.DateFull AND
Table2.TimeStart < Table1.DateIntervalEnd AND
Table2.TimeEnd > Table1.DateIntervalStart
(At least this is how I did it in Excel so far)
So giving this example the result should be this in Table1:
This is obviously just based on 1 row from table2, but the query should go through all rows in table2.
Can be a create/append or update query; doesn't matter in the end.
Attached is a sample DB.
Any help appreciated.
Cheers