Subform not showing correct data

[Kuleesha]

I am in the process of developing my first database. This db is only for my personal use.
I will try to explain my problem as best as possible. Since explanation only is propably not going to be enough, I am attaching my database with dummy data.
Here's how the database works:
Welcome screen opens first. From here I can choose a new patient on an existing patient by double clicking on ID field. I either of these situations the frmPatient form opens. From here you can choose a new visit or an existing visit. New visit opens frmVisit with the relevant patient ID and date already populated. On this form I enter data for the visit. I also have a subform called PastHx which records data with regard to the patient and not the visit (so this data is linked to the patient ID rather than the visit ID). This data is the same for each visit for a particulat patient. I know that placing this subform in frmPatient is more logical but that is not a good flow for me and I need this form to be on that particular tab position on frmVisit since it is compatible with my work flow.
My problem is that the subform (frmPastHx) does not display the correct record (it displays data from the first record in that table). When I try to type sometimg in I get an error indicating that this would create duplicates. Once this visit has been closed up and accessed through the combobox on frmPatient, the subform displays correct data.
Please be kind enough to go through my database and suggest a solution.
Thanks

 

[plog]

This db is only for my personal use.

First off, some people's personal databases are for organizing music collections, or recipes or budgets. You're tracking very detailed medical information for multiple people. I'm fine helping you no matter what the topic is, just seems odd to go out of your way to say this is a personal database.

Second, you need to put aside your forms and reports and fix your table structure. You have not set up your tables properly. THat process is called normalization:

Database normalization - Wikipedia

en.wikipedia.org

I suggest you read up on it, practice a few tutorials, then apply what you learn to your data. Then, complete your relationship Tool and either post a screenshot of it or upload that database so we can go over it. When looking at the current database's Relationship Tool, these are the big things I see wrong structurally with your tables:

1. Data stored in field names. Field names should be generic, if you feel the need to put a specific industry term in a field name, you most likely shouldn't but instead use a [Type] field to hold that specific industry term. For example, all those [...CLCD] in tblPatient should go into a whole new table. That table would have this structure:

tblCLCD
clcd_ID, autonumber, primary key
ID_Patient, number, foreign key to tblPatient.PatientID
clcd_Value, short text, will hold the value not in tblPatient for all those [...CLCD] fields
clcd_Type, short text, will hold the prefix currently in all those [...CLCD] fields, e.g. OGDDue, USSDue, etc

That's it, those 4 fields will now hold all the data that currently goes into the 6 [...CLCD] fields of tblPatient. If you have values for all 6, you will put 6 records into tblCLCD for them.

I am certain you have done this ,multiple times, not just those [,...CLCD] fields--I see a lot of fields suffixed similarly (...Pancreatitits, Hepatitis) Further, my guess is that tblInvestigations has done this for every field. You have used specific industry terms for just about every field in it. Instead you need to store what is in the field name in a Type field with its associated value in another field. Databases should accomodate data vertically (with more rows) and not horizontally (with more columns).

2. Inappropriate data types. I see a couple spots where you have used text instead of dates. Be sure you are using the right datatypes and not just throwing everything in as Short text. By choosing the right data type you are able to use Access's built in functions on them easier. For example, DateDiff (https://www.techonthenet.com/access/functions/date/datediff.php) which allows you to determine time between two dates. If you have dates as text, you can't use that. Same thing goes for numbers stored as text--you can't reliably do math on them.

Again, forget whatever issue you posted about, read up on normalization and focus on getting your tables correct.

 

[Kuleesha]

Thanks for the advice.
What I meant by personal use is I am the only one who is going to use it.
I'm a practicing doctor and would like to track my patients with it.
I will try to restructure my DB along your lines of thought

 

[Kuleesha]

@Kuleesha, I hate to see you struggling with this. There are so many tests and diseases and symptoms, and drugs, etc that need to be tracked and the list grows continually, it is really cumbersome to do it the way you are but you didn't know better so you took a stab at it and actually got something working. Kudos for the effort I would offer to help but the medical terminology is beyond me. I can do some things though. I've attached a mini-app that I include in all my applications to manage lookup tables. I think it might be helpful for you to manage the many lists you have. The intention is to use this tool for simple lists that have only ID, ParentID, shortName, LongName, ActiveFlg but if the drugs can be put into a two-tiered structure, It would be possible to to add other columns in addition to the short and long names to hold dose. It is simple to transfer the data from the existing tables to the table maintenance schema as a straight port but you might want to consider normalizing the data on the way so that the dose and unit of measure are separate and the instructions (if that's what the other data is) ends up in a third column. It would require you to change your queries though but you would be able to use a single query with an argument instead of a query for each table.

Instead of using one table for all drugs, what is the criteria that made you separate them into multiple tables? Knowing that will help me understand if the table maintenance tool can be modified for your purpose.

Many Thanks Pat.
I will go through the database that you have attached.