By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,720 Members | 2,185 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,720 IT Pros & Developers. It's quick & easy.

Access 2003 looping thru records to find related records

P: 2
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

Expand|Select|Wrap|Line Numbers
  1. Index_key,ur_episode,Admission_datetime,discharge_datetime
  2. HERBERT-7/1929,513884-1686900,4/07/2006 10:58,17/07/2006 13:37
  3. HERBERT-7/1929,C023092-1698859,17/07/2006 13:20,24/07/2006 0:30
  4. ELSIE-5/1916,G148445-1720874,8/08/2006 11:00,30/08/2006 10:00
  5. STANISLAWA-3/1918 ,G119981-1720045,8/08/2006 13:01,22/08/2006 12:13
  6. FREDA-11/1925,183772-1998910,27/03/2007 9:53,3/04/2007 11:06
  7. FREDA-11/1925,G147858-2007408,3/04/2007 10:49,26/04/2007 12:39
  8. FREDA-11/1925,183772-2037727,28/04/2007 17:05,9/05/2007 11:41
  9. 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 *******
Oct 18 '07 #1
Share this Question
Share on Google+
6 Replies


nico5038
Expert 2.5K+
P: 3,072
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

Expand|Select|Wrap|Line Numbers
  1. Index_key,ur_episode,Admission_datetime,discharge_datetime
  2. HERBERT-7/1929,513884-1686900,4/07/2006 10:58,17/07/2006 13:37
  3. HERBERT-7/1929,C023092-1698859,17/07/2006 13:20,24/07/2006 0:30
  4. ELSIE-5/1916,G148445-1720874,8/08/2006 11:00,30/08/2006 10:00
  5. STANISLAWA-3/1918 ,G119981-1720045,8/08/2006 13:01,22/08/2006 12:13
  6. FREDA-11/1925,183772-1998910,27/03/2007 9:53,3/04/2007 11:06
  7. FREDA-11/1925,G147858-2007408,3/04/2007 10:49,26/04/2007 12:39
  8. FREDA-11/1925,183772-2037727,28/04/2007 17:05,9/05/2007 11:41
  9. 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)
Oct 18 '07 #2

NeoPa
Expert Mod 15k+
P: 31,489
Reminder to self - "Look at this later."
Oct 18 '07 #3

NeoPa
Expert Mod 15k+
P: 31,489
I too was having trouble seeing the rationale of this. Nico explains the problem. Let us know how this helps.
Oct 18 '07 #4

P: 2
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
Oct 21 '07 #5

NeoPa
Expert Mod 15k+
P: 31,489
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.
Oct 22 '07 #6

nico5038
Expert 2.5K+
P: 3,072
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)
Oct 22 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.