Number of days between records

  • Thread starter Thread starter Access Rookie
  • Start date Start date
A

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.
 
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.
 
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
 
Thanks! This is exactly what I needed.

You're my hero.

KARL DEWEY said:
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.

Access Rookie said:
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.
Click to expand...
Click to expand...
 
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?
 
Back
Top