Access 2003 looping thru records to find related records

[mahowe]

Hi, I have had this problem for a while and have not been able solve it.

What im looking at doing is looping thru my patient table and trying to organise the patients in to there admission sequence, so when patient "A" comes in and is treated at my hospital and is discharged and admitted to another Hospital within one day then patient "A" will get a code of 1 being there first admission.

then if patient "A" is admitted again but there admission date is greater than one day they get a code of 2 being for there second admission but will need to loop thru table looking for other admissions and discharges.

The table name is Adm_disc_Match_tbl

Basically what i have 4 fields.
Index_key = which is the patient common link (text)
ur_episode = this wil change for each Hospital (text)
Admission_datetime = patient admission date and time (datetime)
Discharge_datetime = patient discharge date and time (datetime)

example of data

Index_key,ur_episode,Admission_datetime,discharge_datetime
HERBERT-7/1929,513884-1686900,4/07/2006 10:58,17/07/2006 13:37
HERBERT-7/1929,C023092-1698859,17/07/2006 13:20,24/07/2006 0:30
ELSIE-5/1916,G148445-1720874,8/08/2006 11:00,30/08/2006 10:00
STANISLAWA-3/1918 ,G119981-1720045,8/08/2006 13:01,22/08/2006 12:13
FREDA-11/1925,183772-1998910,27/03/2007 9:53,3/04/2007 11:06
FREDA-11/1925,G147858-2007408,3/04/2007 10:49,26/04/2007 12:39
FREDA-11/1925,183772-2037727,28/04/2007 17:05,9/05/2007 11:41
FREDA-11/1925,G147858-2052082,9/05/2007 12:00,25/05/2007 11:17

If anyone could help it would be much appreciated.

****** Email deleted per site rules *******

[nico5038]

Hi, I have had this problem for a while and have not been able solve it.

What im looking at doing is looping thru my patient table and trying to organise the patients in to there admission sequence, so when patient "A" comes in and is treated at my hospital and is discharged and admitted to another Hospital within one day then patient "A" will get a code of 1 being there first admission.

then if patient "A" is admitted again but there admission date is greater than one day they get a code of 2 being for there second admission but will need to loop thru table looking for other admissions and discharges.

The table name is Adm_disc_Match_tbl

Basically what i have 4 fields.
Index_key = which is the patient common link (text)
ur_episode = this wil change for each Hospital (text)
Admission_datetime = patient admission date and time (datetime)
Discharge_datetime = patient discharge date and time (datetime)

example of data

Index_key,ur_episode,Admission_datetime,discharge_datetime
HERBERT-7/1929,513884-1686900,4/07/2006 10:58,17/07/2006 13:37
HERBERT-7/1929,C023092-1698859,17/07/2006 13:20,24/07/2006 0:30
ELSIE-5/1916,G148445-1720874,8/08/2006 11:00,30/08/2006 10:00
STANISLAWA-3/1918 ,G119981-1720045,8/08/2006 13:01,22/08/2006 12:13
FREDA-11/1925,183772-1998910,27/03/2007 9:53,3/04/2007 11:06
FREDA-11/1925,G147858-2007408,3/04/2007 10:49,26/04/2007 12:39
FREDA-11/1925,183772-2037727,28/04/2007 17:05,9/05/2007 11:41
FREDA-11/1925,G147858-2052082,9/05/2007 12:00,25/05/2007 11:17

If anyone could help it would be much appreciated.

****** Email deleted per site rules *******

Basically I don't see the need for an AdmissionSequence number.
It's a value that can be deducted, thus there's no need to store it.
To get the Patient records in the needed sequence a sort by Index_key and Admission_datetime would do the trick.

By adding a Dcount() function you could add a sequencenumber, but you should use that only when that's giving additional support.

Getting the idea ?

Nic;o)

[NeoPa]

Reminder to self - "Look at this later."

[NeoPa]

I too was having trouble seeing the rationale of this. Nico explains the problem. Let us know how this helps.

[mahowe]

Thanks all for your help,

I origanlly did sort by Index_key and Admission_datetime but what the problem I have is that by simply putting the table into that order it dosent answer my question.

I need to know a patients admission movement through the table. I dont need to know the seqeunce of admission (I can work that out), What I need help on is finding out is when a patient is admitted and discharged from several hospitals in admission sequence and there date gap between discharge from one hospital and admission to another hospital of less than one day then that becomes episode one and when theirs a break of greater than one day that becomes episode two and so on.

I hope I made it a bit more clearer, Any help would be apprecieted.

Thanks

Michael

[NeoPa]

Michael,

As that is trying to process records relative to each other, it is intrinsically unsuited to using SQL. That is to say there is no concept of Next Record or Previous Record in SQL.
I suggest that you do this in a recordset (DAO or ADO) and simply code in your logic.

[nico5038]

When the gap is always one day a trick can be used.
Assume the requirement is that the EndDate of the first visit is equal to the StartDate of the next visit. Then by placing the same table twice in the queryeditor you can connect the PatientID and the EndDate with the StartDate to get all visits in sequence.
Even when the Startdate is allowed to be one day further, then you can use a query with a "SELECT PatientID, StartDate, EndDate + 1 As NewGapEndDate, .." to do the same trick using this query and the original table.

Getting the idea ?

Nic;o)