CODEWITHSUNDEEP
sqlms-access
MonkeyMonkey
MonkeyMonkey

Reputation: 160

MS Access SQL Date Calculation with Single Date

I have two tables linked to a MS Access database I have created. One of my tables contains many rows of data and has one field with a date in there. My second table has one single field and one single entry, which is the date.

My second table at the moment has a field called "ReportDate" and then the content is "2019-03-04".

I am trying to create a query to perform the difference between the dates in the first table and the single date in the second table.

Table 1

ID  Location    Date
1   US  2019-05-10
2   US  2019-06-15
3   CA  2019-05-23
4   CA  2019-06-04
5   US  2019-10-20

Table 2

ReportDate
2019-03-05

RESULT

ID  Location    Date    DayDiff
1   US  2019-05-10  66
2   US  2019-06-15  102
3   CA  2019-05-23  79
4   CA  2019-06-04  91
5   US  2019-10-20  229

Any help would be greatly appreciated!

I tried DateDiff("d",Table2.ReportDate,Table1.Date), but realized that I have nothing joining the 2 tables together. Any guidance would be greatly appreciated!

Upvotes: 0

Views: 38

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You can use a cross join, which in MS Access is done with a comma:

select t1.*, t2.ReportDate,
       DateDiff("d", t2.ReportDate, t1.Date)
from table1 as t1,
     table2 as t2

Upvotes: 1

Related Questions