help building an expression

[tegan_marie]

Hi! I need help building an expression for a field in a query I'm designing.
I have columns of dates and types of reviews.

Example:
SiteID SiteDate Purpose
03500000 05/09/13 Initial Full Scope
03500000 08/17/15 Periodic Full Scope
03500001 05/09/13 Initial Full Scope
03500001 11/04/15 Periodic Full Scope

I need to calculate how many days passed between the first and second date (between the initial and the periodic).
I don't know anything about SQL or VBA. Is there a way to build an expression to calculate this?

Thanks for the help!

 

[BrianJN1]

Assuming that SiteID is in A1 then in Cell D3 write =B3-B2. I gives me 830.

 

[alansidman]

I would redesign my table.

SiteId
Initial Date
Periodic Date

In this manner, you can then run an expression to compare the two dates using the DateDiff function in Access in a new field in your query.

 

[stumac]

There is a couple of ways to do this depending on your data, I have given example SQL below.

If you only have 2 purposes where you want to calculate this then you would create 2 subqueries and take one away from the other (based on your table being called myTable):

SELECT X.SiteID, X.SiteDate, X.Purpose, Y.SiteDate, Y.Purpose, Y.[SiteDate]-X.[SiteDate] AS NoDays
FROM (Select * from MyTable Where Purpose = 'Initial Full Scope')  AS X 
INNER JOIN (select * from MyTable where purpose = 'Periodic Full Scope')  AS Y ON X.SiteID = Y.SiteID

If you want to see the days from the last transaction and/or the purpose isn't always the same then:

SELECT Mytable.SiteID, Mytable.SiteDate, Mytable.Purpose, Nz(Mytable.Sitedate-(SELECT TOP 1 SiteDate FROM Mytable as X
WHERE Mytable.SiteID = X.SiteID
AND X.SiteDate < Mytable.SiteDate
ORDER by X.SiteDate DESC),0) AS NoDays
FROM Mytable
ORDER BY Mytable.SiteID, Mytable.SiteDate