473,574 Members | 2,621 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Access 2003 looping thru records to find related records

2 New Member
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_datet ime = patient admission date and time (datetime)
Discharge_datet ime = 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
6 2724
nico5038
3,080 Recognized Expert Specialist
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_datet ime = patient admission date and time (datetime)
Discharge_datet ime = 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 AdmissionSequen ce 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_datet ime 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
32,564 Recognized Expert Moderator MVP
Reminder to self - "Look at this later."
Oct 18 '07 #3
NeoPa
32,564 Recognized Expert Moderator MVP
I too was having trouble seeing the rationale of this. Nico explains the problem. Let us know how this helps.
Oct 18 '07 #4
mahowe
2 New Member
Thanks all for your help,

I origanlly did sort by Index_key and Admission_datet ime 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
32,564 Recognized Expert Moderator MVP
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
3,080 Recognized Expert Specialist
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

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

Similar topics

6
4728
by: Peter Frost | last post by:
Please help I don't know if this is possible but what I would really like to do is to use On Error Goto to capture the code that is being executed when an error occurs. Any help would be much appreciated. Thanks in advance
1
5102
by: Thats Me | last post by:
TMALSS: Task With maintenance of Access Database I did not develop, don't ask about non-commented code problems or where data dictionary is (LOL). Have table with Inventory data for all Publications held, contains binder number on shelf and is text data in numeric/alpha form i.e.; 123, 1234, 123a, 1234c. I want to stuff the last...
1
1750
by: bill yeager | last post by:
I did some more debugging and found the following: 1) I placed the following code in the button event just to see if I could cycle thru the datagrid control collection: <code> Dim strhello As String Dim myDataListItem As DataListItem Dim myDatagridItem As DataGridItem Dim myCheckBox As CheckBox
9
2124
by: Steven Ung | last post by:
Hello all, I'm having the following error dump from an ASP.net application. The program is to calculate MRP and it involves complex queries looping thru hundreds of records in an few SQL 2000 table. After running for 2~3 minutes, the below error is shown. I've search high and low, but can't find anything close to solving this problem. ...
15
4584
by: Cheryl Langdon | last post by:
Hello everyone, This is my first attempt at getting help in this manner. Please forgive me if this is an inappropriate request. I suddenly find myself in urgent need of instruction on how to communicate with a MySQL database table on a web server, from inside of my company's Access-VBA application. I know VBA pretty well but have never...
1
2735
by: effiw | last post by:
I'm working with an application that has an MS Access front end linked to the SQL Server DB backend. During a period of one week, 32 records of a spefic event (and all related records to that event) disappeared from both DBs. The application uses an Access report db for users to query for specialized data. Security is to the Access DB is limited...
3
2324
by: banba_ca | last post by:
When I create a new db from the main Access window or thru Vb (set newdb = ...) I always end up with Access 2000 file format. I would like to get 2002 - 2003 file format. The reason is that when I try to link from a 2002 - 2003 db to a 2000 db, the Jet engine complains that it can't find the table in the target (2000) database. And no, I...
4
4219
by: lupo666 | last post by:
Hi everybody, this time I have three problems driving me nuts :-((( (1) I have a report with 20 or so Yes/No "squares". Is there a way to either hide/show the "square" or change the yes/no relative textbox, depending on value? (2)
4
2240
Zwoker
by: Zwoker | last post by:
Hi all, I'm not sure whether this should be posted in the MS Access forum or one of the SQL forums (which one?), so I'll start here. I'm self taught in the syntax of SQL based queries that I have been writing in VBA in my MS Access 2003 environment, which has proved to be an issue now that I want to do something a bit more complicated than...
0
7736
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8249
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
8107
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
1
5631
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5307
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3748
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3756
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1352
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1071
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.