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 - 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 *******
6 2734 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 - 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 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)
NeoPa 32,577
Recognized Expert Moderator MVP
Reminder to self - "Look at this later."
NeoPa 32,577
Recognized Expert Moderator MVP
I too was having trouble seeing the rationale of this. Nico explains the problem. Let us know how this helps.
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
NeoPa 32,577
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.
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)
Sign in to post your reply or Sign up for a free account.
Similar topics |
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
|
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 character of into a new
column if it is alpha. In addition, will later
|
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
|
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.
The same ASP page, with less data however works without any issue and is
still on test on our local...
|
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 before needed to do this HTTP/XML/MySQL type functions.
| |
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 to 3 db admins (2 IT, I departmental), and 1dept manager. The app's audit log is supposed to track...
|
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 can't convert on the fly.
Any suggestoins would be appreciated.
....Banba
|
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)
|
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 the ordinary, but have no idea how. Googling for an example has failed to find anything close to what...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
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 synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
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 instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
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 into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
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 effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |