Results 1 to 7 of 7
  1. #1
    Fuii is offline Novice
    Windows 10 Office 365
    Join Date
    Aug 2020
    Posts
    12

    Join tables and count date/time matches in table2 based on variable table1 values

    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:
    Click image for larger version. 

Name:	ExampleResult.JPG 
Views:	21 
Size:	34.3 KB 
ID:	43956

    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
    Attached Thumbnails Attached Thumbnails Tables.jpg  
    Attached Files Attached Files

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    You should have 2 fields,[startdate] and [enddate]. (Not 3 fields)
    a date field holds both date AND time, then you just get the difference to get the elapsed time:
    DateDiff("h",StartDate,EndDate)

  3. #3
    Fuii is offline Novice
    Windows 10 Office 365
    Join Date
    Aug 2020
    Posts
    12
    Thanks for the answer, though it's completely missing the intention

    I don't want the time difference, I want to count the lines in table2 that match the date/time start/end times in table1.
    Yes, I could do a combined date+time field instead of date and time separate, but that how the data is present atm.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    If you are not already aware -- Excel (spreadsheet) and Access(database) are built on different object models and deal with different concepts. What you do in Excel is NOT NECESSARILY how you do the same functionality in Access.

    In Access you would use a query to do a calculation when needed. You would NOT NORMALLY store a calculated value in a table.

    For example the data of entry pKey = 9 of Table2 is:

    DateFull = 1/2/2021
    TimeStart = 9:30
    TimeEnd = 14:30
    So giving this example the result should be this in Table1:
    Shows 4 occurrences??

    My understanding/best guess of you request is that
    - for each record in Table2
    - for a given date you want to review the time range between timeStart and timeEnd and
    - for each 1 hour interval that exists in that range, you want to increment field occurrences in table 1.

    However, if this is your intention, then I can't explain why there are not occurrences for
    13:00 - 14:00 and 14:00 - 15:00 giving a total of 6 occurrences for pKey 9.
    Attached Files Attached Files

  5. #5
    Fuii is offline Novice
    Windows 10 Office 365
    Join Date
    Aug 2020
    Posts
    12
    Thanks for your reply!

    Quote Originally Posted by orange View Post
    If you are not already aware -- Excel (spreadsheet) and Access(database) are built on different object models and deal with different concepts. What you do in Excel is NOT NECESSARILY how you do the same functionality in Access.

    In Access you would use a query to do a calculation when needed. You would NOT NORMALLY store a calculated value in a table.
    I'm aware, just want to know how far I can get with Access without using an intermediate like Excel with VBA to alter the data format into an better one for Access before importing.

    My understanding/best guess of you request is that
    - for each record in Table2
    - for a given date you want to review the time range between timeStart and timeEnd and
    - for each 1 hour interval that exists in that range, you want to increment field occurrences in table 1.
    That's correct.

    However, if this is your intention, then I can't explain why there are not occurrences for
    13:00 - 14:00 and 14:00 - 15:00 giving a total of 6 occurrences for pKey 9.
    Yeah, pKey 9 would be beyond the maximum interval of Table1, so running it would be 6 occurrences (but only 4 in the existing table if restricting it to match the existing lines and not adding new lines to table1).


    Also thanks for the sample script, I'll have a look how far I can push it.
    Closed for now.

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    OK, I now see how you were limiting the intervals for your calculations.
    Here is a modified database where I have used your limiting start and end intervals from Table1
    to calculate the occurrences for table2.

    The script/function now uses the limits from Table1 to identify the values that apply.

    Good luck.
    Attached Files Attached Files

  7. #7
    Fuii is offline Novice
    Windows 10 Office 365
    Join Date
    Aug 2020
    Posts
    12
    Quote Originally Posted by orange View Post
    Here is a modified database where I have used your limiting start and end intervals from Table1
    to calculate the occurrences for table2.
    Click image for larger version. 

Name:	awesomeCat.jpg 
Views:	7 
Size:	48.0 KB 
ID:	44022

    Thanks again

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 12
    Last Post: 09-13-2015, 10:59 AM
  2. Replies: 10
    Last Post: 05-15-2015, 11:35 AM
  3. Replies: 5
    Last Post: 07-29-2014, 11:20 AM
  4. TABLE1 VS TABLE2...Are all ID's from Table1 in Table2???
    By smoothlarryhughes in forum Queries
    Replies: 11
    Last Post: 10-26-2012, 11:28 AM
  5. Copy values from table1 to table2
    By wubbit in forum Queries
    Replies: 2
    Last Post: 04-19-2012, 04:40 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums