Subform not showing correct data (1 Viewer)

[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

 

[Pat Hartman]

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

 

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

 

[Pat Hartman]

As you are looking through the table management app, think also about normalizing the design of your tables. Each test you perform belongs in a separate row rather than a separate column. Each drug you prescribe belong in a separate row rather than a separate column, each Symptom, disease, etc, all belong on separate rows in separate tables. This allows you to choose an item from one of the lists ("tables") defined within the management app. When you add a new test, drug, symptom, whatever, they will automatically become available in the combo box and so you don't have to modify any table to add a new column or create new queries and reports. It will dramatically simplify your application and its ongoing maintenance. Once you get to the point of doing analysis, instead of having to have 60 queries to search for specific drug prescriptions, you have ONE query that takes a parameter. Find all the patients for whom you prescribed Iron, Tylenol, Morphine, whatever you want to look at. You can then use the resulting list of patients to pull their symptoms or outcomes, etc. You can also make queries that look for patients where you prescribed drugA AND drugB to see if they are reporting symptoms. It will be like a weight lifted orr your shoulders.

I know we've helped other doctors and researchers with similar kinds of problems. Perhaps one of them might offer to share their solution. Or, you can search for them because people who don't develop applications as part of their job or hobby, tend to drop in only when they have a problem they need help with solving.