Count Number of days between records/events

[CTJ]

My database records and dates the various statuses of a primary record. eg
from status 1 to status 2 to status 3 etc. Each change of status records a
seperate record in another table, linked to the primary record by a unique ID.

I need to caluculate the number of days between each status change record
attached to the unique ID i.e. number of days between status 1 and status 2,
status 2 and status 3 etc etc..

How can I achieve this?

 

[John W. Vinson]

My database records and dates the various statuses of a primary record. eg
from status 1 to status 2 to status 3 etc. Each change of status records a
seperate record in another table, linked to the primary record by a unique ID.

I need to caluculate the number of days between each status change record
attached to the unique ID i.e. number of days between status 1 and status 2,
status 2 and status 3 etc etc..

How can I achieve this?

A "Self Join" query or a subquery can do this, or a DLookUp... it depends on
the context. Where do you want this value to appear? What's the context?

For a Subquery you could use a calculated field in a Query: in a vacant Field
cell type

DaysSinceStatusChange: (SELECT DateDiff("d", [X].[DateChanged]) FROM mytable
AS X WHERE X.ID = mytable.ID AND X.Status = mytable.Status - 1)

 

[Daryl S]

CTJ -

Assuming you are using the field with data like "Status 1" to drive the
order, you can create a query to do what you want. The trick is to add your
table to the query twice and 'link' the tables with criteria instead of
normal links. When you add the table twice, the second one is appended
with "_1" so you would have two tables (e.g. MyTable and MyTable_1) in design
view.

Then add the following fields to the query grid:
MyTable.UniqueID
MyTable.Status
MyTable.StatusDate
MyTable_1.Status
MyTable_1.StatusDate

In the criteria portion of the grid, under the MyTable_1.Status field, enter
the following: "Status " & Val(Right(MyTable!Status,
len(MyTable!Status)-7)) + 1

This will link the tables by Status - each one to the next.

Then add a new field that has MyTable_1!StatusDate - MyTable!StatusDate in
it to calculate the days between the dates. You can use the DateDiff or
other formatting options here to get the difference in the dates (don't know
if you need time, etc.).

Check it out, using your actual table and field names.