MS Query Compare Two Tables with Date fields

[almostanexpert]

Hello All,

Thank you for taking an interest in this post.

I would like to compare two date fields in two different tables in MS Query to return records that meet this criteria table1.DateTime <= table.ExpireDate. Can this be achieved in MS Query?

I have tried a JOIN

table1 t1
JOIN table2 t2 ON
t1.DateTime <= t2 ExpireDate

However my data is duplicating.

Any information is greatly appreciated.

Thank you

 

[Mr_Olaf]

Hello,

You have to join tables with common fields and then create your condition "t1.DateTime <= t2 ExpireDate". SQL query should have structure like this:

SELECT
t1.field_1,
t1.field_2,
...
t1.field_n,
t2.field_1,
...
t2.field_n
FROM table1 t1 LEFT OUTER JOIN table2 t2 ON t1.[Unique Items Field]=t2.[Related Field]
WHERE t1.DateTime<=t2.ExpireDate

 

[almostanexpert]

I appreciate your response.

So it has to have a LEFT or RIGHT JOIN?

 

[Mr_Olaf]

It does not have to be left or right join. You can create inner join like you did, but not with DateTime and ExpireDate fields. You must use some kind of "identification field" to join two tables and then set condition you want in Where clause: t1.DateTime<=t2.ExpireDate.
If you can send some example of two tables, I will show you what I meant.

 

[almostanexpert]

After some pondering I scrapped this attempt and restarted my query using the graphical interface. There I found my Joining options amoung them the <= options I was looking for. Thanks for helping me get the head in gear.

Best Regards

 

[Mr_Olaf]

I'm glad you did not give up and you've solved the problem.