Your support to for a GoodCourse - Improve Patient care.

[Indi123]

Dear Friends

I am writing to you with a view to get bit of advice and guidance with regards to access Tables.

I only have very basic knowledge in using Access so be little bit more descriptive with your answers please.

I am a health worker and wants to create a basic access data base for our office use.

I created 4 tables

(1) Patient Info Table (Patients personal info – name DOB etc) It has own auto primary key
(2) Referral table (when a patient come to the clinic it is call A referral – It has own primary Auto referral ID)
(3) Assessment Table (When a patient come to the clinic he/she get assessed for medical needs – I brought the Referral ID from Table 2 to this and made it primary key – I will tell you why in a minute)
(4) Discharge Table (Following assessment we discharge the patient – again I used Referral ID from table 2.

This is how I set the relationships:

Patient info and Referral Table – have One to Many (because A patient can get referred to the hospital/clinic many times)

Referral Info Table and Assessment table – One to One (Because each referral get assessed on that given day and….

Assessment Table and Discharge table (again One to One) Because after the assessment patient goes home one the same day.

so the summary

Mr Smith come to the clinic (referral ) get assessed (assessment table) he goes home (discharge table)

This is the support I need:

When I register a new patient (as a new referral ) The referral table generate an Auto number – I want that number to Automatically go to Assessment & Discharge table automatically

Or Do you think what I have done is the world’s biggest stupid thing – if yeas then what is the best way to link the tables

My Primary aim is to design a gorgeous form (lol) where I can see Mr Smith’s details such as

When he was referred (referral history) when he was assessed (or the history of assessments and his discharge history
I also want to design a form where the user can put all that details as things happen (because the Patient details and referral can add on the same day (at the point of referral however Mr Smith has to wait few days be assessed, so the user has to serch Mr Smith and update his assessment/discharge details as/when it happened.

Thanks so much for your support – Do remember your time given here is for a genuinely GOOD Course.

Thanks Guys/girls

Indi

 

[plog]

I think you have too many tables. But your description is very ambigous so I can't be sure. Respond to these ideas/questions:

Patients can have multiple visits correct? John Smith can come in on 1/1/2014 for a chest x-ray. And then come in on 1/7/2014 to get his appendix out. Does your database need to accomodate multiple visits for unique patients?



Following that at what level does the patient get referred? Are they referred for a visit or as an individual? John Smith could be refered for that chest x-ray by Dr. Jones, but the appendix could be from Dr. Davis. Is a patient refered or is a visit?

Can a patient have multiple assesments per visit? They could come in for trauma to the head but also have a broken leg and need stitches. Does your database need to accomodate this?

 

[Indi123]

Hi, Thanks so much for the rapid response.

So sorry for if my descriptions are not clear let me try again.

A patient (Mr Smith ) get referred as an individual – each visit to the clinic is One episode.

In one episode (one referral ) Mr Smith can not have multiple assessments or multiple discharges.

Mr smith (Pt Table) get Referred (Referral related info , date, time reasons ect) on 12/01/14 for - Memory assessment …Then he get assessed (assessment table, date of assessment, name of assessor , out comes etc). Following that he get discharge (discharge table , date of discharge and final out comes etc) so that is end of one episode.

So say few weeks/months later Smith can get referred again

But Mr smith’s personal info remains the same – but New referral information, new assessment information, new discharge information.

Yes Mr Smith can have multiple assessments/discharges but not during one episode. Each time Mr smith has to have a NEW referral – then a related Assessment then a Related Discharge. that’s the end of ONE episode.

Mr Smith Each time has a Referral – Assessment – discharge. Then he goes home. Few days/weeks/months later…. he comes back – again new referral – related assessment, related discharge then he geos home.

 

[Indi123]

"Patients can have multiple visits correct? John Smith can come in on 1/1/2014 for a chest x-ray. And then come in on 1/7/2014 to get his appendix out. Does your database need to accomodate multiple visits for unique patients?"

Yes,

John Smith come 0n 01/01/14…(This is call referral) Then he get Assessed ….Then he get Discharge. End of ONE Episode .

John Smith can come on 01/07/14 – (so his personal info remain the same) But all other info such as Referral, assessment, discharge related info change – because this NEW episode – however belongs to the same patient.

So DB can give a unique Pt ID at the point of his very First registration so if he comes again DB can refer to the same ID so it knows that is Mr Smith.

 

[plog]

Thanks for the explanations, exactly what I needed. This should be your structure:

Patients,
PatientID, FirstName, LastName, .....

Episodes
EpisodeID, PatientID, EpisodeStartDate, EpisodeEndDate, ReferalData, AssesmentData

That's it. You only need 2 tables. You shouldn't have 1-1 relationships in your data, so that means no seperate Referal table, no seperate Assesment table, no seperate Discharge table.

 

[plog]

From a form standpoint, each table should feed its own form. My suggestion for "automatically" creating an episode for a new patient is this:

Create a form for inputing Patient data (First Name, Last Name, etc.). Then on the bottom of that form have a button that says 'Create New Episode'. You click it and it creates a record with the PatientsID in the Episodes table and opens the Episodes form to that record.

This form could also be used to lookup patients. You would find a patient, go to their Patient Form and then click that button if they exist and you go to a new episode for them.

 

[Indi123]

Hi Plog

The reasons why I wanted to have Referral related data in one table, because at the end of the month we have to produce statics related (1) Referrals (2) Assessments (3) Discharges

As a very New person to Acccess, I read on Internet. that I have to keep one set of data in one table

My Patient table:

Pt ID (auto number)
Title (drop down)
First Name
Surname
DOB
Area Code (drop down)
Gender (drp down)


My Referral Table Looks like this:

Referral ID
Patient ID (coming from patients info Table)
Date of Referrals
Time of Referral
Referral status (drop down – urgent or Routine)
Source of Referral (Drop down with 20 choices – various hospital depts.)
Reasons for Referral (20+ reasons) I collect this data (EG how many referrals for Jan/Fed,, say for Memory related, or related to Depression, anxiety etc )



Assessment table contains


Referral ID (coming from above table- so assessment table knows this assessment belongs to a particular referral)
Date of assessment (I need this to calculate date Diff from the referral – how long did my team took see the patient from the date of referral )
Time of assessment
The assessor: Name of the health care professional
The Out come of assessment (drop down with 20+ choices)


My Discharge table contains:

Referral ID (coming from assessment table) (so the discharge table should know this particular discharge is related to A particular assessment in assessment table.
Date of discharge
Final out come (20+ choices – whether pt was discharge home, sent to another hospital, sent to another team etc )

Do you think this is not appropriate ???

 

[plog]

No it is not. My structure is the appropriate one. 2 tables.

A patient has a first name, a last name a birthdate. All that data goes into one table. It doesn't go into a first name table, a last name table and a birthdate table.

Same thing with episodes. They are comprised of a Referral, an Assesment and a Discharge. All that data goes into the same table.

 

[Indi123]

OK, thanks so much and I will update my tables accordingly.

 

[Indi123]

Hi Plog and others

Based on your advised I made two tables
(1) Patient info Table
(2) Referral table
In the referral table, I put all assessment and discharge related info.
However there is a problem.
The forms on this DB are going to use by two set of people:
(1) The administrative team (their job is to receive a referral – add pt detais and Referral related details and save that details then pass that to
(2) Health care professionals.

Following their assessments/discharges HCPs have to fill the relevant information.
I made a registration form (containing Pt info + (main form) Referral info (subform)
However, as I have made some Assessment and discharge related information compulsory (without making this compulsory some Health care professionals tend to ignore some bits of the information) the registration form cant save – because DB is asking the user to fill out assessment related as well as discharge related info at the same time.
Any further advise please ??

 

[plog]

I would make those fields not required in the table, but enforce that using the forms. Whenever the Assesment/Discharge forms load they would use an unbound form (which means the data input into them doesn't automatically feed to the underlying table). The user would input information and then click a 'Submit' button. That button would run that code that first checks to make sure the appropriate required fields have data, if not it prompts the user that the data can't be saved unless its input. If the required data is there, it updates the table with the data they supplied.

 

[Indi123]

Sonds very good - well.... now you know the diffrence between Me..who has lessthan 24 hrs experince using Access and YOU...the master.

this may be a wrong question to ask here..(as it relate to forms) How do i enforce that on a form, ??

Thanks Plog

 

[plog]

I gave you the broad strokes--The Assesment and Discharge forms need to be unbound (not tied to the table). Which means when they type in data it doesn't automatically go to the table. You have a 'Submit' button that first validates the data (makes sure the required fields have data) and if so it runs an UPDATE statement and puts the new data into the record. If not, it spits out a message and informs the users of missing data.

Give it a shot and then post any questions in the Forms sections of this site.