still confused, can someone help

[val_87]

Now i was told that to build in Access 2010 i must leave my mindset of Excel behind.
I tried, now when i have a profile of client (patient) there is information that is available straight away and some that is available later.
As per advice i separated it all in to different smaller tables (list of hospitals, list of nurses, etc etc list of results). But there is one thing i don't understand.
When i will be displaying the information in a query / form or editing it, how will the program know that this patient surname is corresponding for example with this nurse. So that patient was treated by that nurse.
Or i could have a lot of small tables, and then one big general table just for filling in the information by information in small tables as we go?
As in for the future patient name, discharge date, nurse, diagnosis... when i am filling this out, so one particular patient has the correct info after it.
I am so confused...

 

[plog]

So that patient was treated by that nurse.

Junction tables: https://en.wikipedia.org/wiki/Junction_table

Databases are about relationships. A lot of relationships are 1 to many (one baby sitter can have many kids, one vendor can offer many products, etc.). But then there are some relationships that are many to many (one patient can have multiple nurses and one nurse can have multiple patients). These are where junction tables come in. They sit in between 2 tables and acts as a map to keep track of who is assigned to who.

You have these 2 tables:

Nurses
Nurse_ID, Nurse_FName, Nurse_LName
1, Sally, Jones
2, Tim, Smith
3, Betty, Stone

Patients
Patient_ID, Patient_FName, Patient_LName
1, Steve, Jenkins
2, Teresa, Hall


Now to sort out what patient went with what nurse and vice versa, you need a junction table:

PatientNurses
Patient_ID, Nurse_ID
1, 3
1, 2
1, 1
2, 1


Now, if you made a query using all three tables, brough in the names you would see that Patient Jenkins had every nurse; and Patient Hall had Jones.

That's how you relate your tables with many to many relationships.

 

[Minty]

Think of your basic patient information as the "top level" information. Your patient table should have a unique patient ID (I would call it patientID ...) Your table would record the first name, surname, DOB, contact info etc. Information that is unlikley to change.
Your other tables will records actions or events about that patient.
Each of these other things will also have a unique ID and also the patientID , this is how each event can tie back to your patient.

 

[val_87]

Think of your basic patient information as the "top level" information. Your patient table should have a unique patient ID (I would call it patientID ...) Your table would record the first name, surname, DOB, contact info etc. Information that is unlikley to change.
Your other tables will records actions or events about that patient.
Each of these other things will also have a unique ID and also the patientID , this is how each event can tie back to your patient.

So i can include the Patient ID (the autoid) in to all other tables, this is how access will see it?
Thanks for your replies, it is just that there are at least 7 actions that happen after the main details are written down. Each action has 4 to 5 fields, i want to have a table for actions, dropdowns. The only think i was worried, is if i entered patient info, is how to update this same patient the next day so that system would know to update this particular patient.
Thanks for your help, quite new at this, learning.
So my idea of having a bunch of small tables, then to be used to update one big one, is a bad idea? And how do i include this Patient ID in to other tables, other ones will have their own unique ID's, won't they?

 

[val_87]

I will try and create a structure tomorrow ... i will inform you of my progress if anyone is interested, i want to learn this skill i can see it can be quite valuable!

 

[Minty]

You shouldn't be using the small tables to update another big table.
The smaller tables are holding data that relates to your other tables.
You shouldn't normally ever need to duplicate stored data.

http://ycmi.med.yale.edu/nadkarni/star_schema.bmp

 

[val_87]

Now ... i created a relationship but seems it is not working...
The whole point is, that if i enter the patient details i have one batch of info one day, and then next day the other batch. As in baby_mother_clinical_personal and then clinical_staff. However they relate to same patient. As in ID. Now the clinical_personal table has a primary key which is Patient_ID. I added this Patient_ID to second table which is the clinical staff, and related them. But if i fill out the form on clinical_personal and then form on clinical_staff, clinical_personal is filled however continuation of relationship to clinical_staff is not being filled.
I am attaching some screenshots, can anyone advice as to what am i doing wrong? If any of you need more info to help, let me know i will reply back tomorrow.

 

[Minty]

If you look at the picture I posted you will see there are two independent tables - Physician and Patient - that are linked by the Pharmacy_Order table.

This is where you have gone a little wrong. Your patient record should be a unique ID and your Clinical staff table should have another unique ID (StaffID?)

You should then have a third table that has records that store another unique ID (VisitID) along with the StaffID and Patient ID and any other visit specific information. This will give you a usable structure.

 

[val_87]

If you look at the picture I posted you will see there are two independent tables - Physician and Patient - that are linked by the Pharmacy_Order table.

This is where you have gone a little wrong. Your patient record should be a unique ID and your Clinical staff table should have another unique ID (StaffID?)

You should then have a third table that has records that store another unique ID (VisitID) along with the StaffID and Patient ID and any other visit specific information. This will give you a usable structure.

So let me see that i get this right. Structure should be as follows:
I should have personal details of staff table, then table for details of physicians. But for instance when different activities happen with the patient and physician interact, these activities would be in separate tables. As in first visit table, second visit table etc etc...then i would have a usable structure?
Of course i can have separate tables for list of staff to use in drop-down menus?
Am i thinking the right way?
Thanks for your help!

 

[gemma-the-husky]

No

you don't have different tables for each visit/consultation

your table needs to have fields such as

date, patientid, doctored, treatment details etc.

then for a given patient, you can locate all his treatments in date order, or sorted by doctor, or filtered by treatment type. - however imaginative you need to be.

you may need subsidiary tables depending on the actual data you are dealing with. eg, if multiple drugs can be prescribed at a given consultation, then you need a subsidiary table called "prescribeddrugs" linked to the consultation - or maybe linked to the patient rather than the consultation.

The skill/art of the thing IS the data analysis into manageable tables.

 

[val_87]

No

you don't have different tables for each visit/consultation

your table needs to have fields such as

date, patientid, doctored, treatment details etc.

then for a given patient, you can locate all his treatments in date order, or sorted by doctor, or filtered by treatment type. - however imaginative you need to be.

you may need subsidiary tables depending on the actual data you are dealing with. eg, if multiple drugs can be prescribed at a given consultation, then you need a subsidiary table called "prescribeddrugs" linked to the consultation - or maybe linked to the patient rather than the consultation.

The skill/art of the thing IS the data analysis into manageable tables.

Thank you.
This is not a typical doctors surgery database. It is a bit different. I will try to explain what is happening then i will post another screenshot.

When a child is born, sample is taken then sent off to laboratory. Then results are received and it is determined whether another test is required, if yes then test 2 test 3 test 4 and final one with outcome. It also specifies reasons why the re-tests are needed. So when a new record is created, in a form i want to be able to fill out mothers and child details and details of hospital they came from. Hence personal and clinical details. So we have a hospital lookup wizard with a table with list of hospitals, same i will have medical personell table, just haven't had time to deal with this yet. Separate tables that contain reasons for retests and outcoems table. So they can be later modified via forms and enforced all over the place. In a screenshot i think most things are ok. Except for Baby ID, i have it in every table and they are connecte so that system sees that this is a single record for a particular patient/child. I am not even starting to create forms or queries until i have relationsips in tables.

See the screenshot attached.

This i am doing to make my life easier at work, i have to do it because government is sure as hell not paying anyone to do it ... Now i wish i was a pro programmer! Maybe i need a video for stupid people explaining what is what!

 

[Minty]

You are on the right track - but you really don't need separate test tables. Make one test table with all the fields as per test 1, but add a field called test number.

You can then have one form for all your test entries but filter out the fields you don't need based on the test number.

Also if in the future someone decides to add Test6 you don't need to re-write everytihng - simply add test number 6 to your list of test types.

You may possibly have a extraneous table in the baby Gender one- It is only ever going to male or female. That maybe taking normalisation a bit too far.

 

[gemma-the-husky]

Thank you.
This is not a typical doctors surgery database. It is a bit different. I will try to explain what is happening then i will post another screenshot.

When a child is born, sample is taken then sent off to laboratory. Then results are received and it is determined whether another test is required, if yes then test 2 test 3 test 4 and final one with outcome. It also specifies reasons why the re-tests are needed. So when a new record is created, in a form i want to be able to fill out mothers and child details and details of hospital they came from. Hence personal and clinical details. So we have a hospital lookup wizard with a table with list of hospitals, same i will have medical personell table, just haven't had time to deal with this yet. Separate tables that contain reasons for retests and outcoems table. So they can be later modified via forms and enforced all over the place. In a screenshot i think most things are ok. Except for Baby ID, i have it in every table and they are connecte so that system sees that this is a single record for a particular patient/child. I am not even starting to create forms or queries until i have relationsips in tables.

See the screenshot attached.

This i am doing to make my life easier at work, i have to do it because government is sure as hell not paying anyone to do it ... Now i wish i was a pro programmer! Maybe i need a video for stupid people explaining what is what!

first, You shouldn't think that what you have is not standard. I expect that it is perfectly standard. It's just a matter of deciding what details you need.
You probably will find that whatever you do here would work for any standard medical practice, and that any standard medical system would give you what you want. It ought to, certainly. That's really what you should aim for. You may find that your special requirements give you some specific enhancements that aren't in a standard system. That's all, though, I think.


patienttable - the baby - patientid name, dob, birthweight, hospitalid(hospital born at).
anything you need. As you are dealing with babies, personally I would include the parent name in this table. In a general medical system, you might have a separate patient record for the mother, and want to link to that record.

hospitalstable. hospitalid and details of hospital. record this id in the patient table.

doctorstable, doctorid and doctor name

patientconsultationtable. consultationid, patientid (baby id), hospitalid, doctorid, date, notes (the consultation might not take place at the birth hospital - it's details like this you have to consider)

testable testid, testdetail (eg jaundice test)

patienttests consultationid, testid, date, details etc
or maybe
patienttests patientid, testid, date, details etc

this depends on your data analysis - are the patient tests to be linked to the consultation, or to the patient. It depends on how you want to refer to them within your system.

you will have multiple patienttests per patient. This is perfectly normal.
what you 100% do not want to do is have specific tables for test1, test2, test3, etc, You might want a single lookup table for test outcomes, "OK, Further Tests Needed, Problem", again with a numeric key. You do not want multiple tables for outcomes. Ditto, for "reasons"

So the art here is for you to identify every little thing you need to record in your system, and then decide on the appropriate table structure. Not the value of the thing. Not a "jaundice test", and a "kidney function test". Just a "test". All specific tests are just examples of general test. There is a continual refinement going on, though. As you start to develop you will most likely decide that you original design wasn't quite right. It is better to try and fix the design than persevere with the bad design. You might need extra data in the patient table. You might decide that something in the consultation table needs to be a different table. The idea is to normalise the data, so that the same data is never repeated in 2 different tables

The items in red are the id field of the relevant table. I would add an autonumber field to each table for this purpose.

The items in blue are the autonumbers stored in a table that refer to items in a linked table - foreign keys, in some cases called lookup fields/tables.

Finally, the design process is critical, and not to be taken lightly. A good design will lead to harmonious and rapid development. Your thoughts of what you want to see on forms should help you identify the data you are modelling. But the form layout you envisage is not critical or even important of itself. The user interface is important, but it's just a presentation detail. The user interface and the forms are just mechanisms to get the data into the tables, and back out again.

Hope this helps

 

[jdraw]

val_87,

I'm going to suggest that you work through 1 or 2 of the sample tutorials at RogersAccessLibrary.

-Entity Relationship Diagramming
-Class Information Database
-Consolidated Widgets

You have to work through them (~30-40 minutes each) but you will learn how to take a "problem/opportunity" description and design the appropriate tables and relationships. What you learn can be used with any database.
Try a couple of these (they do include answers) and get familiar with the concepts.
Then, go back to your own database and apply what you have learned.

Good luck.

 

[val_87]

Thank you all for help. I will read, learn and post what i came up with!

 

[val_87]

Now, attached two screenshots. Relationships work now i think.
I may have not done the testing tables as supposed to, someone suggested using a dropdown menu to add 1test, 2test and etc. But i just made a table with these tests, i know it looks clumsy... but i can't figure out how to have a dropdown menu to add tests if needed. I mean the test data has to be stored somwhere, if its a first test and its all good its stored, but if second is required it is also stored. This is what i get confused.

Now i will be creating a search tool next, idea is is to search a record by a value, not Baby ID but the UPI nr and display that information, ideally when i search, i could click on the record and it would open it in a form.

Or am i better off just using reports for that?

 

[Minty]

Your Test table is not right I'm afraid.
You have stored all the tests in one place and as a result have had to add 4 different retest reasons, this is incorrectly designed.
I don't have time right now - but will try and draw up what you should do. You should re-read what else has been suggested earlier as well.

You need a basic Test table with a test number field. Then your retest reason would be a single table.

 

[plog]

Here's the things that stand out to me in your design:

1. Circular paths. There should only be 1 path between tables. Testing, clinical_info and personal info are all linked directly to one another. One of those has to go. I don't know which, but you have an extra path between tables.

2. Numerated field names. This is what Minty touched on. When you start suffixing/prefixing field names with numbers (1st test, 2nd test, etc.) its improperly structured. Tables should grow vertically (with more records) not horizontally (with more fields). Testing should look like this:

Testing
BabyID
TestNum
TestDate
PostDate
PostageTracking
AllClear
RetestReason

Where TestNum contains if it is the 1st, 2nd or nth test.

3. Tables with only 1 real field of data. Autonumbers don't count as real data, so all those tables (hospitals, outcomes, etc.) you have with just an autonumber and another field, shouldn't exist. You would just store the actual value in the foreign table and not the id of what that value resolves to.

 

[val_87]

Your Test table is not right I'm afraid.
You have stored all the tests in one place and as a result have had to add 4 different retest reasons, this is incorrectly designed.
I don't have time right now - but will try and draw up what you should do. You should re-read what else has been suggested earlier as well.

You need a basic Test table with a test number field. Then your retest reason would be a single table.

Thank you!
I appreciate this very much. I just hope i am not in over my head and it is not something that can be done by a programmer.

 

[val_87]

Here's the things that stand out to me in your design:

1. Circular paths. There should only be 1 path between tables. Testing, clinical_info and personal info are all linked directly to one another. One of those has to go. I don't know which, but you have an extra path between tables.

2. Numerated field names. This is what Minty touched on. When you start suffixing/prefixing field names with numbers (1st test, 2nd test, etc.) its improperly structured. Tables should grow vertically (with more records) not horizontally (with more fields). Testing should look like this:

Testing
BabyID
TestNum
TestDate
PostDate
PostageTracking
AllClear
RetestReason

Where TestNum contains if it is the 1st, 2nd or nth test.

3. Tables with only 1 real field of data. Autonumbers don't count as real data, so all those tables (hospitals, outcomes, etc.) you have with just an autonumber and another field, shouldn't exist. You would just store the actual value in the foreign table and not the id of what that value resolves to.

So with testing table if i understand correctly, rather than having multiple entries, i should mark the tests as if its first, second third fourth or fifth, with a dropdown menu?
As in this test is first. And outcome is all clear and close the case.
But for example if this test has outcome insufficient sample, second test must be conducted. So how do i add this second test to the same baby record?
Am i understanding it correctly?