New to Access Question on Database Design

Alex_H

New member
Local time
Today, 11:12
Joined
May 19, 2011
Messages
1
Hello all. This is my first post of these forms (although I have lurked for a few weeks borrowing ideas and tips) so apologies if this is not the right part of the forum to post my question.

I have used access in its most basic forms for a few years (running queries and getting data out of existing databases, but have never really delved into database design very much and have little experience with VBA Code although I am learning all the time.

I have been tasked with building a database to record statistics about patients. The data comes in two categories for simplicity (Category A & Category B) which I have created in two separate tables; tblA and tlbB

The personal details of the patients have been recorded in their own table tblPatient.

The information is to be recorded once ever quarter and is expected to be collected going forward indefinitely.

I have created a form which displays the patient data (FrmPatient) and would eventually a couple of buttons which take me to a new form for each of the data sets (FrmA & FrmB)

I have created a many to many relationship between tblpatient and tblA through tblpatdata and plan to also link tblB to tblpatdata in a similar manner.

[Tblpatient] 8 --- 1 [tblpatdata] 1 ---- 8 [tblA]

Tblepatdata forms a subform on FrmPatient.
In the long term I will looking to run queries/reports on the data by patient by quarter by results and numerous other ways that I have not thought of yet, but will be requested by the powers that be.

My question comes from the fact that this data is reported quarterly as I commented upon. I had thought of having an “Quarter & Date” combobox on tblepatdata so that the user could select which quarter the information in tblA is recorded as. I.E
The user selects Patient “Joe Bloggs” on [FrmPatient] then from [Subform Frmpatdata] the user selects “Q1” and presses the button to open [FrmA] enters the data and exits [FrmA] back to the [FrmPatient]
If the user then selects Patient “Joe Bloggs” on [FrmPatient] then from [Subform Frmpatdata] the user selects “Q2” and presses the button to open [FrmA] he is instantly displayed the information he entered only a moment before.
Does this make sense to anyone? Is there a solution to my problem? I presume there must be. Please let me know if you need this better explained or need more information.

Alex
 
There are free data models at databaseanswers.org. Here are a couple that deal with Patients. These may be overkill but it will show the type of information and tables involved and some relationships. These are not meant to have you change what you have. The links are to help you confirm/modify or structure a table or relationship.

http://www.databaseanswers.org/data_models/patient_care/index.htm
http://www.databaseanswers.org/data_models/patient_tests/index.htm

"When you ask about a form and a subform and what should happen when" - in my view, you are really asking about GUI or interface design. Not meaning to be picky, but what do you think should happen. If you think X and you're the one who has to use it, then X seems fine to me. However, if you are not the user, but you are the designer, then ask the intended user -- What should happen next when this occurs?
The user will usually ask, "What are some of the options?"
So, you may try a few designs and again speak with those who will use it to see what the "thinking" is.

Just a few thoughts. Good luck.
 

Users who are viewing this thread

Back
Top Bottom