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

  • Thread starter Thread starter mlk
  • Start date Start date
M

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
 
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
 
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
Click to expand...

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.
 
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
 
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.



John W. Vinson said:
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
Click to expand...

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.
Click to expand...
 
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.



John W. Vinson said:
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
Click to expand...
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.
Click to expand...
Click to expand...
 
Thanks to both of you for your assistance!

John Spencer said:
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.



John W. Vinson said:
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.
Click to expand...
Click to expand...
.
Click to expand...
 
Back
Top