Generating a field that is the difference between 2 dates (within

[mlk]

Hi,
I have data in the following format --

(Sorted by ID and EncoutnerDate)
ID EncounterDt NEWVAR-see below
1 1/1/2009 0
1 1/15/2009 14
1 1/17/2009 2
1 1/30/2009 13
2 4/20/2009 0
2 4/25/2009 5
2 5/2/2009 7
2 5/15/2009 20
2 etc.

I would like to create a new field that (by ID) generates the # of days
between that date and the date before that. Is there an easy way to do this
in MS Access?

Thank you.
Mandi

 

[vanderghast]

SELECT a.id, a.encounterDt, Nz(MAX(b.encounterDt) -a.encounterDt, 0)
FROM table|NameHere AS a LEFT JOIN tableNameHere AS b
ON a.id = b.id
AND a.encouterDt > b.encouterDt
GROUP BY a.id, a.encounterDt

should do


Vanderghast, Access MVP

 

[John W. Vinson]

Hi,
I have data in the following format --

(Sorted by ID and EncoutnerDate)
ID EncounterDt NEWVAR-see below
1 1/1/2009 0
1 1/15/2009 14
1 1/17/2009 2
1 1/30/2009 13
2 4/20/2009 0
2 4/25/2009 5
2 5/2/2009 7
2 5/15/2009 20
2 etc.

I would like to create a new field that (by ID) generates the # of days
between that date and the date before that. Is there an easy way to do this
in MS Access?

Thank you.
Mandi

Use a calculated field in the query:

NewVar: DateDiff("d", NZ(DMax("[EncounterDt]", "yourtablename", "[ID] = " &
[ID] & " AND [EncounterDt] < #" & [EncounterDt] & "#"), [EncounterDt]))

This will search for the largest EncounterDt for this particular ID; if there
is none, NZ will return the current record's EncounterDt. DateDiff will count
the number of days.

 

[John Spencer]

First, your subject got truncated.

I think you want the difference between two dates for each ID.

SELECT ID, EncounterDt
, DateDiff("d",(SELECT Max(EncounterDt) FROM YourTable as Temp
WHERE Temp.ID = YourTableID
AND Temp.EncounterDt < YourTable.EncounterDt),EncounterDt) as Days
FROM YourTable

Updating the field would not be possible with this query, but in most cases it
would be wisest not to store the calculation, but to use something like the
above query. If you add, delete, or modify the records in the table, your
stored data would be incorrect or missing.

If you really feel the need to store the data, you will need to use a VBA
aggregate function to do so

UPDATE YourTable
SET NewVar = DateDiff("d",DMax("EncounterDt","YourTable","ID=" & [ID] & " AND
EncounterDt=" & Format(EncounterDt,"\#yyyy-mm-dd\#") ),[EncounterDt])

If you want to do this using the query design view
== Add your table
== Add the field you want to update
== Select Query: Update from the menu
== Enter the following in the update to for the field
DateDiff("d",DMax("EncounterDt","[YourTable]","ID=" & [ID] & " AND
EncounterDt=" & Format(EncounterDt,"\#yyyy-mm-dd\#") ),[EncounterDt])

If you can't accept null and must have zero for the first date in each ID then
change the expression to
Nz(DateDiff("d",DMax("EncounterDt","YourTable","ID=" & [ID] & " AND
EncounterDt=" & Format(EncounterDt,"\#yyyy-mm-dd\#") ),[EncounterDt]),0)


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[mlk]

Thanks. When I try this I get the following --
The expression you entered has a function containing the wrong number of
arguments. Suggestions?

Thanks again.

Hi,
I have data in the following format --

(Sorted by ID and EncoutnerDate)
ID EncounterDt NEWVAR-see below
1 1/1/2009 0
1 1/15/2009 14
1 1/17/2009 2
1 1/30/2009 13
2 4/20/2009 0
2 4/25/2009 5
2 5/2/2009 7
2 5/15/2009 20
2 etc.

I would like to create a new field that (by ID) generates the # of days
between that date and the date before that. Is there an easy way to do this
in MS Access?

Thank you.
Mandi

Use a calculated field in the query:

NewVar: DateDiff("d", NZ(DMax("[EncounterDt]", "yourtablename", "[ID] = " &
[ID] & " AND [EncounterDt] < #" & [EncounterDt] & "#"), [EncounterDt]))

This will search for the largest EncounterDt for this particular ID; if there
is none, NZ will return the current record's EncounterDt. DateDiff will count
the number of days.

 

[John Spencer]

John's concept as usual was elegant. Too bad he missed the last argument to
the DateDiff function.

NewVar: DateDiff("d", NZ(DMax("[EncounterDt]", "yourtablename", "[ID] = " &
[ID] & " AND [EncounterDt] < #" & [EncounterDt] & "#"), [EncounterDt]),
[EncounterDt])


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Thanks. When I try this I get the following --
The expression you entered has a function containing the wrong number of
arguments. Suggestions?

Thanks again.

Hi,
I have data in the following format --

(Sorted by ID and EncoutnerDate)
ID EncounterDt NEWVAR-see below
1 1/1/2009 0
1 1/15/2009 14
1 1/17/2009 2
1 1/30/2009 13
2 4/20/2009 0
2 4/25/2009 5
2 5/2/2009 7
2 5/15/2009 20
2 etc.

I would like to create a new field that (by ID) generates the # of days
between that date and the date before that. Is there an easy way to do this
in MS Access?

Thank you.
Mandi

Use a calculated field in the query:

NewVar: DateDiff("d", NZ(DMax("[EncounterDt]", "yourtablename", "[ID] = " &
[ID] & " AND [EncounterDt] < #" & [EncounterDt] & "#"), [EncounterDt]))

This will search for the largest EncounterDt for this particular ID; if there
is none, NZ will return the current record's EncounterDt. DateDiff will count
the number of days.

 

[mlk]

Thanks to both of you for your assistance!

John's concept as usual was elegant. Too bad he missed the last argument to
the DateDiff function.

NewVar: DateDiff("d", NZ(DMax("[EncounterDt]", "yourtablename", "[ID] = " &
[ID] & " AND [EncounterDt] < #" & [EncounterDt] & "#"), [EncounterDt]),
[EncounterDt])


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Thanks. When I try this I get the following --
The expression you entered has a function containing the wrong number of
arguments. Suggestions?

Thanks again.

Hi,
I have data in the following format --

(Sorted by ID and EncoutnerDate)
ID EncounterDt NEWVAR-see below
1 1/1/2009 0
1 1/15/2009 14
1 1/17/2009 2
1 1/30/2009 13
2 4/20/2009 0
2 4/25/2009 5
2 5/2/2009 7
2 5/15/2009 20
2 etc.

I would like to create a new field that (by ID) generates the # of days
between that date and the date before that. Is there an easy way to do this
in MS Access?

Thank you.
Mandi
Use a calculated field in the query:

NewVar: DateDiff("d", NZ(DMax("[EncounterDt]", "yourtablename", "[ID] = " &
[ID] & " AND [EncounterDt] < #" & [EncounterDt] & "#"), [EncounterDt]))

This will search for the largest EncounterDt for this particular ID; if there
is none, NZ will return the current record's EncounterDt. DateDiff will count
the number of days.

.