Query with INNER JOIN with time comparison

[mastersbsb]

I have a database in MS Access, which serves to store the trips from the cut to the embankment in an earthwork. In this database I have two tables, the CAD_CARGA table and the CAD_DESCARGA table, the following image shows the structures of both:

Basically what I need is a query with an INNER JOIN that matches the discharge with the respective load, that is, the loads with the same DATA, EQUIPMENTO_TRANSPORTE and MATERIAL. So far so good, I managed to do it. However, I have 33,800 records, but the query multiplies the lines, generates around 130,000 results, another way would be, in addition to the fields above, to use the HOUR field, which is common in both tables, that is, CAD_DESCARGA.HORA has to be the smallest value possible compared to CAD_LOAD.HORA, also satisfying the requirements of the same DATA, EQUIPMENTO_TRANSPORTE and MATERIAL. However I can't progress, could someone help me?

 

[Jon]

Welcome to Access World! We're so happy to have you join us as a member of our community. As the most active Microsoft Access discussion forum on the internet, with posts dating back more than 20 years, we have a wealth of knowledge and experience to share with you.

We're a friendly and helpful community, so don't hesitate to ask any questions you have or share your own experiences with Access. We're here to support you and help you get the most out of this powerful database program.

To get started, we recommend reading the post linked below. It contains important information for all new users of the forum:

https://www.access-programmers.co.uk/forums/threads/new-member-read-me-first.223250/

We hope you have a great time participating in the discussion and learning from other Access enthusiasts. We look forward to having you around!

 

[Pat Hartman]

Welcome

What do you plan to join on? There will not be anything that is an exact match in the two tables.

PS - using a datetime data type as a PK simply won't work. You will never find matches. The datetime data type is actually a double precision number. The integer portion is the number of days since Dec 30, 1899 and the decimal portion is the elapsed time since midnight.

print Now()
6/27/2023 2:37:46 PM
Print cdbl(#6/27/2023 2:37:46 PM#)
45104.6095601852
So the date displayed is internally stored as the second value shown by the cdbl(). There have been 45,104 days since Dec 30, 1899. Noon today would have been 45104.5, 6 PM would be 45104.75 So, if you interpolate, .609 is almost half way between noon and 6 PM

 

[The_Doc_Man]

Just for the formality, hello and welcome to the forum.

Pat's answer is correct. Date/time fields are not going to be very good for JOIN operations because they are accurate to the second - but depending on how they were stored, it is possible that you would have significant internal rounding errors in the millisecond/microsecond range that would totally prevent a JOIN from matching another date/time. Those millisecond/microsecond times would BE rounding errors, not actually measured time to that limit, because Windows won't go beyond seconds with the NOW() function.