Number of days between records

[Access Rookie]

Hello All,

I have a table that tacks customer subscriptions. I would like to add a
field "Laps Time" that tracks the number of months between renewals (no
unique id in this table). I need to take the End_Date of the first record and
subtract the Start_Date of the second record and so on (grouped by VIN and
Acct_Sak). Is there a way to do that in a query?

Start_Date End_Date Package VIN Acct_Sak Laps Time
11/17/2005 11/16/2006 FACTORY 00000000000 100000000
4/25/2007 4/24/2008 SAFETY 00000000000 100000000 5
4/25/2008 5/24/2008 SAFETY 00000000000 100000000 0

Any assitance provided will be greatly appreciated.

 

[KARL DEWEY]

Use a Rank BY Group query like the one below to create a Rank for each record
based on VIN as group with dates in order.
SELECT Q.[Group], Q.[Item_no], Q.[Points], (SELECT COUNT(*) FROM [Product] Q1
WHERE Q1.[Group] = Q.[Group]
AND Q1.[Points] < Q.[Points])+1 AS Rank
FROM Product AS Q
ORDER BY Q.[Group], Q.[Points] DESC;

Then place two copies of the Rank BY Group query in the design view of a new
query (Access adds a suffix of '_1' to the second instance). Join on VIN.
Use criteria on second Rank of first Rank +1. This gives you the following
record to be able to take the End_Date of the first record and subtract the
Start_Date of the second record.

 

[Michel Walsh]

You can also do it in one step, with a total query over a join:



SELECT a.VIN, a.start_ Date,
a.start_date - MAX(b.end_date) AS differenceInDays

FROM table AS a LEFT JOIN table AS b
ON a.vin = b.vin AND a.start_date > b.end_date

GROUP BY a.VIN, a.start_date




sure, for the earliest start_date, there is no 'previous' end_date, and for
that record, the difference in days is null. You can remove that row from
the result by changing LEFT JOIN into INNER JOIN.




Vanderghast, Access MVP

 

[Access Rookie]

Thanks! This is exactly what I needed.

You're my hero.

Use a Rank BY Group query like the one below to create a Rank for each record
based on VIN as group with dates in order.
SELECT Q.[Group], Q.[Item_no], Q.[Points], (SELECT COUNT(*) FROM [Product] Q1
WHERE Q1.[Group] = Q.[Group]
AND Q1.[Points] < Q.[Points])+1 AS Rank
FROM Product AS Q
ORDER BY Q.[Group], Q.[Points] DESC;

Then place two copies of the Rank BY Group query in the design view of a new
query (Access adds a suffix of '_1' to the second instance). Join on VIN.
Use criteria on second Rank of first Rank +1. This gives you the following
record to be able to take the End_Date of the first record and subtract the
Start_Date of the second record.

Hello All,

I have a table that tacks customer subscriptions. I would like to add a
field "Laps Time" that tracks the number of months between renewals (no
unique id in this table). I need to take the End_Date of the first record and
subtract the Start_Date of the second record and so on (grouped by VIN and
Acct_Sak). Is there a way to do that in a query?

Start_Date End_Date Package VIN Acct_Sak Laps Time
11/17/2005 11/16/2006 FACTORY 00000000000 100000000
4/25/2007 4/24/2008 SAFETY 00000000000 100000000 5
4/25/2008 5/24/2008 SAFETY 00000000000 100000000 0

Any assitance provided will be greatly appreciated.

 

[Access Rookie]

This is perfect. Thank you so much.

I will need the first record to appear unfortunately it only appears if I
search on a specific VIN. How can I trigger the query to show all records
every time?