Storing New and Updating Existing Records to a Table where data is retrieved from other tables?

[LucianoPena28]

Hi guys,



New here but I'll be direct. So I have this database where I'm managing and storing records of a small clinic and I'm making a couple forms for users to utilize within access.
I have a Table called Doctors Information and I'm storing the following:

DoctorID

First Name

M.I.

Last Name

Street Address

City

State

Phone Number

Social Security Number

AssistantID

StationID

I want to store values on these using a form but I want to store the ID number of the AssistantID matching the ID of the NurseID with the use of a combobox of just the first name of that corresponding ID. It's the same with the StationID, I want to store the value of that ID with the use of a combobox that displays the names of the Stations I've created as the available options. I come accross a conflict when wanting to store new information but the existing information displays perfect.

Attachment 40487Attachment 40488Attachment 40489

[June7]

Why would a physician have only 1 assistant and 1 station associated? I suspect instead of saving this data in Doctors, possibly you need a junction table to associate doctor, assistant, station for a particular event.

Why would a station have multiple types? What is a type? Advise not to use multi-value fields.

[ranman256]

Yeah. A tAssociates table that has DrID & nurseID,
so Dr can have N nurses.

[LucianoPena28]

I tried that, and I see where you're coming from, but it's a small clinic and the staff isn't as big as a hospital would entail, so to accommodate I want to assign 1 assistant nurse to one physician, although depending on the station they're assigned at then it will have more nurses stationed there to attend and assist patients but not all can assist one doctor in for it will be chaos for the physician. So, best have one nurse assist one physician especially when the patient is brought to the injury section of the clinic. I must pay attention as well to the stations or departments, there are rooms where only one patient can be in consultation with a physician at a time, having one assistant inside can help the current patient, but having too many assistants their roles would be hard to decide on. It's best to have 1 assistant for 1 doctor both being in the same station, and if the station requires other nurses then it will be assigned to the station but not labeled as an assistant for a physician but more of an assistant to the current patients.

[LucianoPena28]

Yeah. A tAssociates table that has DrID & nurseID,
so Dr can have N nurses.

If I associate more than one assistant to a physician their role as nurses will be lost and it will be a confusion to their roles as nurses in the clinic. A nurse doesn't necessarily have one role, they have multiple roles, the focus of an assistant is having one nurse assisting a physician with one patient at a time while other nurses tend to other patients without the supervision of a physician or surgeon.

[LucianoPena28]

Why would a physician have only 1 assistant and 1 station associated? I suspect instead of saving this data in Doctors, possibly you need a junction table to associate doctor, assistant, station for a particular event.

Why would a station have multiple types? What is a type? Advise not to use multi-value fields.

I'm not quite getting all your questions right. Can you be more clear on your questions please?

[June7]

I really don't see how this confuses roles.

Could a physician work with different assistants at different stations for each patient visit?

This junction table only identifies which nurse served in capacity of assistant for that particular event.

How can I be more clear? I am asking you to clarify relationships of these data entities.

You show a multi-value field for Types. Types of what? Why are you using multi-value field? Advise not to use.

[LucianoPena28]

Why would a physician have only 1 assistant and 1 station associated? I suspect instead of saving this data in Doctors, possibly you need a junction table to associate doctor, assistant, station for a particular event.

Why would a station have multiple types? What is a type? Advise not to use multi-value fields.

A station type is like a label for a room, like for instance a waiting room, it cannot be qualified as an observation room for patients and it's not suited to hold sick and injured persons in the same station, so the station name would be the name of the room, like a waiting room, then room one, which is then identified and labeled as a consulting room where a patient can consult with a physician privately about whatever health issue is ongoing and so forth, there are different departments in a clinic for sure we all know that, but having them classified under what they are meant for is good to know as well, also certain rooms/stations may have multiple functions, of course we may assume because it's a small clinic and the infrastructure can only entail so much, so enabling multiple labels which can also inform anyone of its functions with the classification it will have.

[LucianoPena28]

I really don't see how this confuses roles.

Could a physician work with different assistants at different stations for each patient visit?

This junction table only identifies which nurse served in capacity of assistant for that particular event.

How can I be more clear? I am asking you to clarify relationships of these data entities.

You show a multi-value field for Types. Types of what? Why are you using multi-value field? Advise not to use.

Obviously a physician may not likely work with different assistants at different stations due to small staff and it's likely that an assistant must have the same or close to the same shift as the physician and must be station in the same area as well as the physician. The clinic will not have as much physicians present as the number of nurses and only certain nurses with the qualifications may be suited to be an assistant for a physician.

There are a number of stations in a clinic for instance a observation ward, which likely there will only be a physician or two present and I must emphasize that not all nurses need to assist a physician with a patient where they can also assist other patients without the supervision of a physician. The key role here just for this form is to just assign one assistant for a physician for when it's best suited for that physician, other than that it's unlikely that they must be assigned to a physician for full supervision. Nurses operate differently and are managed by a different set of Managers and supervisors, the key role here is just to let a nurse know, hey, you are assigned to help doctor John Doe on room 1 which is a private consultation room, and so to assign that nurse it must match the same station here. That's the goal for now, later on the form will be modified further more when it is to assign them by dates and shifts that may be in consideration, but for now I'm aiming for this.

[LucianoPena28]

A station type is like a label for a room, like for instance a waiting room, it cannot be qualified as an observation room for patients and it's not suited to hold sick and injured persons in the same station, so the station name would be the name of the room, like a waiting room, then room one, which is then identified and labeled as a consulting room where a patient can consult with a physician privately about whatever health issue is ongoing and so forth, there are different departments in a clinic for sure we all know that, but having them classified under what they are meant for is good to know as well, also certain rooms/stations may have multiple functions, of course we may assume because it's a small clinic and the infrastructure can only entail so much, so enabling multiple labels which can also inform anyone of its functions with the classification it will have.

The other goal is to see if new records can be recorded in the same form also, storing the ID of the station, and assistant in the table with just the click of the nurse's name and the station name in a combobox. Aiming for this will help when assigning different shifts to all nurses to accommodate for the next shift as well, and for the next day, but that will then be modified later when all shifts have been set for all personnel.

[LucianoPena28]

I do apologise for not being so clear with my goals but it's complex especially taking into consideration that other tables are in dependency of the tables I'm working on, so I aim to reach these goals first in order to continue the others

[June7]

If you want to use a field in Doctors table to identify an assistant for physician for all visits until that assistant is changed in record, then do so but don't link junction table to that field. Save that NurseID into junction table and link to Nurses table.

I presume the nurse assigned station in Nurses table is irrelevant when nurse is assigned as assistant.

I still don't see why a junction table would not apply.

Records in junction table would define data for physician/assistant/patient/station/date combinations. This offers most flexibility for documenting events and dynamic assignments.

[ssanfu]

I also think you need to stop and fix the design issues.

First, the naming issues:
---------------------------
Object names should be letters and numbers.
Do not begin an object name with a number.
NO spaces, punctuation or special characters (exception is the underscore) in object names

Object include Field names, table names, query names, form names and report names.



In a relational database, generally 1 table holds info (attributes) of one "thing".

In the doctor table, first name, last name, address all help to "define" a "doctor".
Is the "AssistantID" an attribute of a doctor?
How about the "Station_ID"?
How about a "ShiftID"?
What about "DoctorBreaks"??
So those 4 "things" (plus 2 others) should be in another table (the junction table).


The same goes for the Nurse table.
Does/can a nurses position change over the course of a day/shift? With the current design, if you change the nurse position, the previous position is lost.
StationID?
ShiftID?
BreaksID, LunchID, WorkHrsID?



So it looks like you will be in need of several junction tables.


I also noticed there is not one date/time field in your dB. Wouldn't the date/time a doctor/nurse assisted a patient be important?


Something to think about.......

[LucianoPena28]

If you want to use a field in Doctors table to identify an assistant for physician for all visits until that assistant is changed in record, then do so but don't link junction table to that field. Save that NurseID into junction table and link to Nurses table.

I presume the nurse assigned station in Nurses table is irrelevant when nurse is assigned as assistant.

I still don't see why a junction table would not apply.

Records in junction table would define data for physician/assistant/patient/station/date combinations. This offers most flexibility for documenting events and dynamic assignments.

Ok, in other terms it's best for me to just have a table to store the records of:

("=" for links/relationships)
DoctorID = [Docor Information].DoctorID
Assistant ID = [Nurse Information].NurseID
StationID = [Station]. StationID

as a junction table?

If not, what values would you recommend for me to store in the junction table to avoid having restrictions and hopefully store new records? If any other approach is there, what are they?

I will surely use this approach in advance surely will take note next time I come across this issue

[LucianoPena28]

I also think you need to stop and fix the design issues.

First, the naming issues:
---------------------------
Object names should be letters and numbers.
Do not begin an object name with a number.
NO spaces, punctuation or special characters (exception is the underscore) in object names

Object include Field names, table names, query names, form names and report names.



In a relational database, generally 1 table holds info (attributes) of one "thing".

In the doctor table, first name, last name, address all help to "define" a "doctor".
Is the "AssistantID" an attribute of a doctor?
How about the "Station_ID"?
How about a "ShiftID"?
What about "DoctorBreaks"??
So those 4 "things" (plus 2 others) should be in another table (the junction table).


The same goes for the Nurse table.
Does/can a nurses position change over the course of a day/shift? With the current design, if you change the nurse position, the previous position is lost.
StationID?
ShiftID?
BreaksID, LunchID, WorkHrsID?



So it looks like you will be in need of several junction tables.


I also noticed there is not one date/time field in your dB. Wouldn't the date/time a doctor/nurse assisted a patient be important?


Something to think about.......

Huh? About the object names, not sure where you're coming from but all my field names are only characters of letters and symbols, no numbers included, so, I'm clarifying that