Multiple Admission Dates

C

cd6109

I'm using Access 2002 and need to distinguish admission dates from a series
of other service dates. This could easily be done by grouping ClientId and
using Min([Service Date] if each client had only one admission; however, I
need the query to recognize when there is a break in service (discharge) and
then return the value for the new re-admission date for the next group of
records while keeping the admission date the same for the first series of
records. The data looks like this:

ClientId Service Date Admission Date
00044 12/22/06
00044 12/23/06
00044 12/24/06
00044 12/25/06
00044 01/09/07
00044 01/10/07
00044 04/18/07
00122 10/03/06
00122 10/04/06
00989 03/07/07
00989 03/08/07
00989 04/01/07

For ClientId 00044, I need to populate Admission Date with 12/22/06 for the
first series of records, 01/09/07 for the second set of records, and 04/18/07
for his last record. ClientId 00122 would need to have Admission Date be
10/03/06. ClientId 00989 would need to have Admission Date be 03/07/07 on
the first two records and 04/01/07 for the final record.

What's the best way to tackle this?
 
S

Steve

Make a backup of your database and try the following ---

Create a query named QryClient and include the fields ClientID, ServiceDate
and AdMission Date in that order. Sort ClientID ascending and Service Date
ascending.

Put the following code in the click event of a button on some form:
Dim DB As DAO.Database
Dim Rst As DAO.Recordset
Dim ClientID As Long
Dim ServiceDate as Date
Dim AdMissionDate As Date
Set DB = CurrentDB
Set Rst = DB.Openrecordset("TblClient")
Do Until Rst.EOF
ClientID = Rst!ClientID
Do Until Rst!ClientID <> ClientID
ServiceDate = Rst!ServiceDate
Do Until Rst!ServiceDate <> ServiceDate
Rst!AdmissionDate = AdMissiondate
Rst.MoveNext
Loop
Loop
Loop
Set DB = Nothing
Rst.Close
Set Rst = Nothing

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)
 
J

John W. Vinson

I'm using Access 2002 and need to distinguish admission dates from a series
of other service dates. This could easily be done by grouping ClientId and
using Min([Service Date] if each client had only one admission; however, I
need the query to recognize when there is a break in service (discharge) and
then return the value for the new re-admission date for the next group of
records while keeping the admission date the same for the first series of
records. The data looks like this:
Click to expand...

What qualifies as a break? One day difference? Two days? a week?

John W. Vinson [MVP]
 
C

cd6109

A "break" in this case will be anything more than 1 day difference from the
preceding service date--Consecutive dates do not constitute a "break" or
"discharge".

John W. Vinson said:
I'm using Access 2002 and need to distinguish admission dates from a series
of other service dates. This could easily be done by grouping ClientId and
using Min([Service Date] if each client had only one admission; however, I
need the query to recognize when there is a break in service (discharge) and
then return the value for the new re-admission date for the next group of
records while keeping the admission date the same for the first series of
records. The data looks like this:
Click to expand...

What qualifies as a break? One day difference? Two days? a week?

John W. Vinson [MVP]
Click to expand...
 
M

Michael Gramelspacher

cd6109 said:
A "break" in this case will be anything more than 1 day difference from the
preceding service date--Consecutive dates do not constitute a "break" or
"discharge".

John W. Vinson said:
I'm using Access 2002 and need to distinguish admission dates from a series
of other service dates. This could easily be done by grouping ClientId and
using Min([Service Date] if each client had only one admission; however, I
need the query to recognize when there is a break in service (discharge) and
then return the value for the new re-admission date for the next group of
records while keeping the admission date the same for the first series of
records. The data looks like this:
Click to expand...

What qualifies as a break? One day difference? Two days? a week?

John W. Vinson [MVP]
Click to expand...
Click to expand...
not a query, but ..

Sub UpdateAdmissions()

Dim d As Date
Dim s As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Set dbs = CurrentDb()

' Note: for some reason I cannot do this in a query due to
' Operation must use an updateable query error.

s = "SELECT client_id, Min(service_date) AS AdmitDate" & _
" FROM (Select s.service_date, s.client_id FROM Services AS s" & _
" INNER JOIN Services as s1 ON s.client_id = s1.client_id" & _
" AND DateDiff('d',s.service_date, s1.service_date) < 2" & _
" GROUP BY s.client_id, s.service_date) AS a" & _
" GROUP BY client_id"

' open recordset using Number Workdays query
Set rst = dbs.OpenRecordset(s, dbOpenForwardOnly)
With rst
' loop through recordset and updating admission_date column
' in Services table with AdmitDate from the query
Do
s = "UPDATE Services SET Services.admission_date = #" & .Fields(1) & _
"# WHERE Services.client_id= '" & .Fields(0) & "'"
'Debug.Print s
dbs.Execute s, dbFailOnError
.MoveNext
Loop Until .EOF
End With
rst.Close
Set rst = Nothing
Set dbs = Nothing
End Sub
 
J

John Spencer

I might try a two-query approach. The first query Sets the Admissiondate to
the service date for all the records that have no prior date record for the
clientID.

UPDATE TheTable
SET TheTable.[Admission Date] =[Service Date]
WHERE Not Exists
(SELECT *
FROM TheTable as TMP
WHERE Tmp.ClientID = TheTable.ClientID
AND Tmp.ServiceDate = TheTable.Service Date -1)

Now that you have that solved. You can use a second query

UPDATE TheTable
SET TheTable.[Admission Date] =
DMax("[Admission Date]","TheTable","ClientID=" & Clientid & " AND [Service
Date] <=#" & [Service Date] & "#")
WHERE TheTable.AdmissionDate is Null


Standard warning: BACK UP YOUR DATA before you attempt this.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
S

Steve

Corrected Code --- Has been tested

Dim DB As DAO.Database
Dim Rst As DAO.Recordset
Dim ClientID As String
Dim ServiceDate As Date
Dim AdmissionDate As Date
Set DB = CurrentDb()
Set Rst = DB.OpenRecordset("QryClient")
Do Until Rst.EOF
ClientID = Rst!ClientID
Do Until Rst!ClientID <> ClientID
ServiceDate = Rst!ServiceDate
AdmissionDate = Rst!ServiceDate
Do Until Rst!ServiceDate <> ServiceDate
Rst.Edit
Rst!AdmissionDate = AdmissionDate
Rst.Update
ServiceDate = ServiceDate + 1
Rst.MoveNext
If Rst.EOF Then
Exit Sub
End If
Loop
Loop
Loop

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)






Steve said:
Make a backup of your database and try the following ---

Create a query named QryClient and include the fields ClientID,
ServiceDate and AdMission Date in that order. Sort ClientID ascending and
Service Date ascending.

Put the following code in the click event of a button on some form:
Dim DB As DAO.Database
Dim Rst As DAO.Recordset
Dim ClientID As Long
Dim ServiceDate as Date
Dim AdMissionDate As Date
Set DB = CurrentDB
Set Rst = DB.Openrecordset("TblClient")
Do Until Rst.EOF
ClientID = Rst!ClientID
Do Until Rst!ClientID <> ClientID
ServiceDate = Rst!ServiceDate
Do Until Rst!ServiceDate <> ServiceDate
Rst!AdmissionDate = AdMissiondate
Rst.MoveNext
Loop
Loop
Loop
Set DB = Nothing
Rst.Close
Set Rst = Nothing

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)




cd6109 said:
I'm using Access 2002 and need to distinguish admission dates from a
series
of other service dates. This could easily be done by grouping ClientId
and
using Min([Service Date] if each client had only one admission; however,
I
need the query to recognize when there is a break in service (discharge)
and
then return the value for the new re-admission date for the next group of
records while keeping the admission date the same for the first series of
records. The data looks like this:

ClientId Service Date Admission Date
00044 12/22/06
00044 12/23/06
00044 12/24/06
00044 12/25/06
00044 01/09/07
00044 01/10/07
00044 04/18/07
00122 10/03/06
00122 10/04/06
00989 03/07/07
00989 03/08/07
00989 04/01/07

For ClientId 00044, I need to populate Admission Date with 12/22/06 for
the
first series of records, 01/09/07 for the second set of records, and
04/18/07
for his last record. ClientId 00122 would need to have Admission Date be
10/03/06. ClientId 00989 would need to have Admission Date be 03/07/07
on
the first two records and 04/01/07 for the final record.

What's the best way to tackle this?
Click to expand...
Click to expand...
 
M

Michael Gramelspacher

Dim DB As DAO.Database
Dim Rst As DAO.Recordset
Dim ClientID As String
Dim ServiceDate As Date
Dim AdmissionDate As Date
Set DB = CurrentDb()
Set Rst = DB.OpenRecordset("QryClient")
Do Until Rst.EOF
ClientID = Rst!ClientID
Do Until Rst!ClientID <> ClientID
ServiceDate = Rst!ServiceDate
AdmissionDate = Rst!ServiceDate
Do Until Rst!ServiceDate <> ServiceDate
Rst.Edit
Rst!AdmissionDate = AdmissionDate
Rst.Update
ServiceDate = ServiceDate + 1
Rst.MoveNext
If Rst.EOF Then
Exit Sub
End If
Loop
Loop
Loop
Click to expand...
It works for me too. Good work!
 
M

Michael Gramelspacher

Corrected Code --- Has been tested

Dim DB As DAO.Database
Dim Rst As DAO.Recordset
Dim ClientID As String
Dim ServiceDate As Date
Dim AdmissionDate As Date
Set DB = CurrentDb()
Set Rst = DB.OpenRecordset("QryClient")
Do Until Rst.EOF
ClientID = Rst!ClientID
Do Until Rst!ClientID <> ClientID
ServiceDate = Rst!ServiceDate
AdmissionDate = Rst!ServiceDate
Do Until Rst!ServiceDate <> ServiceDate
Rst.Edit
Rst!AdmissionDate = AdmissionDate
Rst.Update
ServiceDate = ServiceDate + 1
Rst.MoveNext
If Rst.EOF Then
Exit Sub
End If
Loop
Loop
Loop

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)
Click to expand...
I think this query solution works too:

Sub CreateServices()
With CurrentProject.Connection
.Execute _
"CREATE TABLE Services" & _
" (client_id VARCHAR(10) NOT NULL" & _
", service_date DATETIME NOT NULL" & _
", admission_date DATETIME NULL" & _
", PRIMARY KEY (client_id,service_date));"

.Execute "INSERT INTO Services (client_id, service_date)" & _
" VALUES ('00044', #12/22/2006#);"
.Execute "INSERT INTO Services (client_id, service_date)" & _
" VALUES ('00044', #12/24/2006#);"
.Execute "INSERT INTO Services (client_id, service_date)" & _
" VALUES ('00044', #01/09/2007#);"
.Execute "INSERT INTO Services (client_id, service_date)" & _
" VALUES ('00044', #01/10/2007#);"
.Execute "INSERT INTO Services (client_id, service_date)" & _
" VALUES ('00044', #04/18/2007#);"
.Execute "INSERT INTO Services (client_id, service_date)" & _
" VALUES ('00122', #10/03/2006#);"
.Execute "INSERT INTO Services (client_id, service_date)" & _
" VALUES ('00122', #10/04/2006#);"
.Execute "INSERT INTO Services (client_id, service_date)" & _
" VALUES ('00989', #03/07/2007#);"
.Execute "INSERT INTO Services (client_id, service_date)" & _
" VALUES ('00989', #03/08/2007#);"
.Execute "INSERT INTO Services (client_id, service_date)" & _
" VALUES ('00989', #03/09/2007#);"
.Execute "INSERT INTO Services (client_id, service_date)" & _
" VALUES ('00989', #04/01/2007#);"
End With
End Sub

Query: Preceded by Gap
------------------------
SELECT Services.client_id,
Services.service_date
FROM Services
WHERE (((EXISTS (SELECT s.service_date
FROM Services AS s
WHERE Services.client_id = s.client_id
AND s.service_date = DATEADD('d',-
1,services.service_date))) = False));

Query: Get Admission Dates
---------------------------
SELECT Services.client_id,
Services.service_date,
MAX([Preceded by Gap].[service_date]) AS Admission
FROM Services
INNER JOIN [Preceded by Gap]
ON ([Preceded by Gap].[service_date] <= services.service_date)
AND (Services.client_id = [Preceded by Gap].client_id)
GROUP BY services.client_id,services.service_date;

Query: Update Services Admissions
 
C

cd6109

This two-query approach works better than the form suggestion for what I'm
doing...The next problem, however, is calculating the discharge dates. The
first query works great by using WHERE Tmp.ClientID = TheTable.ClientID
AND Tmp.ServiceDate = TheTable.Service Date +1). I can't seem to figure out
how to make the second query work for me to populate the rest of the
discharge dates.

Here's exactly what I used for the second-query admission date calcuation
(note I just had to add some single quotes for some reason):

UPDATE tblES_L01 SET tblES_L01.[Admission Date] = DMax("[Admission
Date]","tblES_L01","[ClientId] = '" & [ClientId] & "' AND [Service Date] <=#"
& [Service Date] & "#")
WHERE (((tblES_L01.[Admission Date]) Is Null));

If you could help me figure out the discharge calculation too, that would be
great! Thanks a lot.

John Spencer said:
I might try a two-query approach. The first query Sets the Admissiondate to
the service date for all the records that have no prior date record for the
clientID.

UPDATE TheTable
SET TheTable.[Admission Date] =[Service Date]
WHERE Not Exists
(SELECT *
FROM TheTable as TMP
WHERE Tmp.ClientID = TheTable.ClientID
AND Tmp.ServiceDate = TheTable.Service Date -1)

Now that you have that solved. You can use a second query

UPDATE TheTable
SET TheTable.[Admission Date] =
DMax("[Admission Date]","TheTable","ClientID=" & Clientid & " AND [Service
Date] <=#" & [Service Date] & "#")
WHERE TheTable.AdmissionDate is Null


Standard warning: BACK UP YOUR DATA before you attempt this.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

cd6109 said:
I'm using Access 2002 and need to distinguish admission dates from a
series
of other service dates. This could easily be done by grouping ClientId
and
using Min([Service Date] if each client had only one admission; however, I
need the query to recognize when there is a break in service (discharge)
and
then return the value for the new re-admission date for the next group of
records while keeping the admission date the same for the first series of
records. The data looks like this:

ClientId Service Date Admission Date
00044 12/22/06
00044 12/23/06
00044 12/24/06
00044 12/25/06
00044 01/09/07
00044 01/10/07
00044 04/18/07
00122 10/03/06
00122 10/04/06
00989 03/07/07
00989 03/08/07
00989 04/01/07

For ClientId 00044, I need to populate Admission Date with 12/22/06 for
the
first series of records, 01/09/07 for the second set of records, and
04/18/07
for his last record. ClientId 00122 would need to have Admission Date be
10/03/06. ClientId 00989 would need to have Admission Date be 03/07/07 on
the first two records and 04/01/07 for the final record.

What's the best way to tackle this?
Click to expand...
Click to expand...
 
J

John Spencer

So it seems that ClientID is a text field. That is why you needed the
single quotes.

Is discharge date the last date in the series where there is no follow on
date? If so, I would again try a two-query approach. The first query would
set the discharge date on the last record.

UPDATE TheTable
SET TheTable.[DISCHARGE Date] =[Service Date]
WHERE Not Exists
(SELECT *
FROM TheTable as TMP
WHERE Tmp.ClientID = TheTable.ClientID
AND Tmp.ServiceDate = TheTable.Service Date +1)

Now that we have accomplished that, you could try something like the
following.
UPDATE TheTable
SET TheTable.[DISCHARGE Date] =
DMin("[DISCHARGE Date]","TheTable","ClientID='" & Clientid & "' AND [Service
Date] >=#" & [Service Date] & "#")
WHERE TheTable.[DISCHARGE Date] is Null

AGAIN, back up your data before you try this.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

cd6109 said:
This two-query approach works better than the form suggestion for what I'm
doing...The next problem, however, is calculating the discharge dates.
The
first query works great by using WHERE Tmp.ClientID = TheTable.ClientID
AND Tmp.ServiceDate = TheTable.Service Date +1). I can't seem to figure
out
how to make the second query work for me to populate the rest of the
discharge dates.

Here's exactly what I used for the second-query admission date calcuation
(note I just had to add some single quotes for some reason):

UPDATE tblES_L01 SET tblES_L01.[Admission Date] = DMax("[Admission
Date]","tblES_L01","[ClientId] = '" & [ClientId] & "' AND [Service Date]
<=#"
& [Service Date] & "#")
WHERE (((tblES_L01.[Admission Date]) Is Null));

If you could help me figure out the discharge calculation too, that would
be
great! Thanks a lot.

John Spencer said:
I might try a two-query approach. The first query Sets the Admissiondate
to
the service date for all the records that have no prior date record for
the
clientID.

UPDATE TheTable
SET TheTable.[Admission Date] =[Service Date]
WHERE Not Exists
(SELECT *
FROM TheTable as TMP
WHERE Tmp.ClientID = TheTable.ClientID
AND Tmp.ServiceDate = TheTable.Service Date -1)

Now that you have that solved. You can use a second query

UPDATE TheTable
SET TheTable.[Admission Date] =
DMax("[Admission Date]","TheTable","ClientID=" & Clientid & " AND
[Service
Date] <=#" & [Service Date] & "#")
WHERE TheTable.AdmissionDate is Null


Standard warning: BACK UP YOUR DATA before you attempt this.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

cd6109 said:
I'm using Access 2002 and need to distinguish admission dates from a
series
of other service dates. This could easily be done by grouping ClientId
and
using Min([Service Date] if each client had only one admission;
however, I
need the query to recognize when there is a break in service
(discharge)
and
then return the value for the new re-admission date for the next group
of
records while keeping the admission date the same for the first series
of
records. The data looks like this:

ClientId Service Date Admission Date
00044 12/22/06
00044 12/23/06
00044 12/24/06
00044 12/25/06
00044 01/09/07
00044 01/10/07
00044 04/18/07
00122 10/03/06
00122 10/04/06
00989 03/07/07
00989 03/08/07
00989 04/01/07

For ClientId 00044, I need to populate Admission Date with 12/22/06 for
the
first series of records, 01/09/07 for the second set of records, and
04/18/07
for his last record. ClientId 00122 would need to have Admission Date
be
10/03/06. ClientId 00989 would need to have Admission Date be 03/07/07
on
the first two records and 04/01/07 for the final record.

What's the best way to tackle this?
Click to expand...
Click to expand...
Click to expand...
 
C

cd6109

Yes ClientId is text. This works great...Lots of hours of frustration trying
to get this to work. Thanks very much!

John Spencer said:
So it seems that ClientID is a text field. That is why you needed the
single quotes.

Is discharge date the last date in the series where there is no follow on
date? If so, I would again try a two-query approach. The first query would
set the discharge date on the last record.

UPDATE TheTable
SET TheTable.[DISCHARGE Date] =[Service Date]
WHERE Not Exists
(SELECT *
FROM TheTable as TMP
WHERE Tmp.ClientID = TheTable.ClientID
AND Tmp.ServiceDate = TheTable.Service Date +1)

Now that we have accomplished that, you could try something like the
following.
UPDATE TheTable
SET TheTable.[DISCHARGE Date] =
DMin("[DISCHARGE Date]","TheTable","ClientID='" & Clientid & "' AND [Service
Date] >=#" & [Service Date] & "#")
WHERE TheTable.[DISCHARGE Date] is Null

AGAIN, back up your data before you try this.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

cd6109 said:
This two-query approach works better than the form suggestion for what I'm
doing...The next problem, however, is calculating the discharge dates.
The
first query works great by using WHERE Tmp.ClientID = TheTable.ClientID
AND Tmp.ServiceDate = TheTable.Service Date +1). I can't seem to figure
out
how to make the second query work for me to populate the rest of the
discharge dates.

Here's exactly what I used for the second-query admission date calcuation
(note I just had to add some single quotes for some reason):

UPDATE tblES_L01 SET tblES_L01.[Admission Date] = DMax("[Admission
Date]","tblES_L01","[ClientId] = '" & [ClientId] & "' AND [Service Date]
<=#"
& [Service Date] & "#")
WHERE (((tblES_L01.[Admission Date]) Is Null));

If you could help me figure out the discharge calculation too, that would
be
great! Thanks a lot.

John Spencer said:
I might try a two-query approach. The first query Sets the Admissiondate
to
the service date for all the records that have no prior date record for
the
clientID.

UPDATE TheTable
SET TheTable.[Admission Date] =[Service Date]
WHERE Not Exists
(SELECT *
FROM TheTable as TMP
WHERE Tmp.ClientID = TheTable.ClientID
AND Tmp.ServiceDate = TheTable.Service Date -1)

Now that you have that solved. You can use a second query

UPDATE TheTable
SET TheTable.[Admission Date] =
DMax("[Admission Date]","TheTable","ClientID=" & Clientid & " AND
[Service
Date] <=#" & [Service Date] & "#")
WHERE TheTable.AdmissionDate is Null


Standard warning: BACK UP YOUR DATA before you attempt this.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I'm using Access 2002 and need to distinguish admission dates from a
series
of other service dates. This could easily be done by grouping ClientId
and
using Min([Service Date] if each client had only one admission;
however, I
need the query to recognize when there is a break in service
(discharge)
and
then return the value for the new re-admission date for the next group
of
records while keeping the admission date the same for the first series
of
records. The data looks like this:

ClientId Service Date Admission Date
00044 12/22/06
00044 12/23/06
00044 12/24/06
00044 12/25/06
00044 01/09/07
00044 01/10/07
00044 04/18/07
00122 10/03/06
00122 10/04/06
00989 03/07/07
00989 03/08/07
00989 04/01/07

For ClientId 00044, I need to populate Admission Date with 12/22/06 for
the
first series of records, 01/09/07 for the second set of records, and
04/18/07
for his last record. ClientId 00122 would need to have Admission Date
be
10/03/06. ClientId 00989 would need to have Admission Date be 03/07/07
on
the first two records and 04/01/07 for the final record.

What's the best way to tackle this?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

count not-overlapping days in a "Table" with Beginning and EndingsDates Specified 0
Find then highlight in yellow 6
Calculating Work Time 7
eliminate data subset from query 2
time_report_date-wise_employee-wise 6
Reverse Index Match 2
find the latest date in access 2007 2
Advanced problem using auto-number and datediff to find readmissio 2

Top