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

[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?

Attachment 40490

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.......

In the doctor table, first name, last name, address all help to "define" a "doctor".
Is the "AssistantID" an attribute of a doctor?

Well, it is when you're viewing it as an administrator or manager, you can quickly link who's currently working with said 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).

I do see your point, it does in fact link with a few other tables which are Station, which can also be seen in the relationship you saw in the screenshot, there's also a table called Shift, also clearly seen in another screenshot, and breaks too, so instead of entering way too much data in one table just have it connected to other tables, I don't know if doing it this way was the wrong approach, if that's what you're implying, but I would love to know your best approach at it and what you'd do differently.

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?

Obviously I'm still in a process of learning access 2016, it's a big leap from access 2010 and I didn't venture much in this type of database, I was mostly into doing online databases, which were more simple to handle, but seemingly it seems a bit different when doing the structure here since you use less programming and more data linking.

I really appreciate the help and all the support!

[LucianoPena28]

Attachment 40494

I've attached the database to see if anyone else has any more suggestions. I would really appreciate that

[ssanfu]

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

Object names should only be letters and numbers. NO spaces, punctuation or special characters (exception is the underscore) in object names.

There is a table name: "Clock IN/Out" - has a space and a slash.
In table Patient:
field name "Town/City" - has a slash
field name "Date of Admission" - has a spaces
field name "Room of Consultation/Observation" - has a space and a slash

I'm not being picky. It does make a difference. With a table name like "[Clock IN/Out].Clock_In", you will need to add the brackets every time you use it in a query or code.




A lot of your tables are designed like a spreadsheet.
For instance the "Symptoms" table


I would design the tables like


All of your tables are still there. I prefixed copies of the tables with "tbl" and the 1 table "jnct". the new queries are prefixed with "qry".


Let me ask a question. With your design, what happens if you want to add "Convulsions" to the "Symptoms" table?
After you modify the "Symptoms" table to add a field named "Convulsions", you would have to modify any queries, then the forms, then the reports and lastly search through the code.

With the design of "tblSymptoms", all you have to do is add "Convulsions" to the table. Done!


Well, that is my 2 cents worth.
My intention was not to pick on you, but hopefully help you see a different way of designing tables.
(Believe me, in my first 3 databases in Access I committed "Spreadsheet"! It was extremely frustrating. And they were not nearly as complicated as your dB.)

Good luck with your project.