Setting up a basic hospital record system: linking forms with patient ID or name?

[leslieknope]

Hi,



I have about two days' worth of experience in Access, so please forgive me if this question is pretty novice.

I work for a charity in the Pacific. I am trying to develop a very basic patient electronic health record for a new hospital, to be used until we get a more comprehensive patient management system in place.

I have set up basic tables, forms, and a welcome screen.

The idea is that the doctor or nurse will start at the welcome screen. From there, they can either enter a new patient, or add a new patient encounter.

There are four types of patient encounters: assessment, treatment/surgery, refraction, and follow-up. I have made tables and forms for these encounters.

My issue is finding an easy way for the doctor or nurse to quickly and easily make sure that the encounter form they are filling relates to the appropriate patient. I already have a patient form, and patient ID field across the tables. But I would like to be able to add a 'search for patient' button on each of the encounter forms (or any other relatively easy method) to make sure everything lines up.

I would really appreciate any help with this issue. I am attaching my work so far as well.

Thanks.

Database draft.zip

[June7]

Could use form/subforms arrangement to synchronize related records. Main form bound to tblPatients and subforms bound to the related encounter tables. That way you only need one 'search for patient' button. This arrangement can be used to view all existing records as well as enter new patient and their encounter records.

Related primary and foreign key fields must be the same data type. An autonumber primary key must be related to a number foreign key. The PatientID and ProviderID fields in the related encounter tables must be number not text type.

Don't really understand the EncounterInformation table. How are these records associated with appropriate encounter records from the other tables - PatientID and date?

Advise no spaces or special characters/punctuation (underscore is exception) in naming convention.

[leslieknope]

Thanks, very helpful!

[orange]

I would highlight the advice re naming convention - do not use embedded spaces or speciail characters in field and object names. They will cause you syntax errors in your project.

I'm going to suggest this tutorial to help with getting your tables and relationships designed to support your business. You have to work through the tutorial but you will learn about business rules, table design and relationships. There is a solution provided. The author uses a process that can be used with any database development.
There are other tutorials on the site that follow the same technique. Working through any/some of these would reinforce your learning.

I notice you do not have a relationship diagram. I recommend a data model to help with establishing the big picture to help keep activities/needs up to date. I often recommend to new developers using Access to get a good understanding of database concepts before getting too deeply into Access or any other software.

A good practice is to write a 2-3 line description of every table and field in simple terms. This will help you more than you think.

Good luck with your project.

[leslieknope]

Thanks everyone for the help. I will definitely check out the tutorial and am trying to learn as much as I can within the time frame I'm working under!

So, I have linked all the tables through relationships (Patient ID or "PatID") as the key. I am working on getting rid of those spaces in the field names etc.

I have set up the subforms and it links the encounter records back to the patient though the PatID, which is great.

However, now my main form is pretty huge. Is there a way I can place buttons to open the subform for that particular patient in a new window, instead of having the whole subform in the main form?

I understand how to create a button to open the subform, but when I do this, it just creates a new record and doesn't automatically link the subform record to the patient like the subform does when it is in the main form.

If I use buttons, do I need to add a 'find a patient' button on each subform?

Thanks again

File attached if that's useful database250815.zip

[June7]

Search for patient record on main form. Subforms will show only records related to main form records.

Triage subform does not have Master/Child Links properties set.

Can organize subforms on a Tab control. Can have the subforms open independently, however, this complicates creating new records.

[leslieknope]

Thanks so much for the quick reply, and for the triage note!

Tabs are perfect! Will do that. Thanks again.