form to table - index problem?

[freeskier81]

hi and thanks in advance for your kind help!

info:
- built a db with several tables, queries, forms and under-forms.
- my central entry-form is linked to multiple tables.


- i got one index (patient number) that is identical in all tables.
- i was able to enter 4 datasets without any problems but than, the additional datasets, after being entered in the form, appear in the table, but, when reopened, have disappeared from the form. and, because the index is already existing in the table, i cannot enter any additional data via the form.

tia

[jzwp11]

Welcome to the forum!

- my central entry-form is linked to multiple tables.


i was able to enter 4 datasets without any problems but than, the additional datasets, after being entered in the form, appear in the table, but, when reopened, have disappeared from the form. and, because the index is already existing in the table, i cannot enter any additional data via the form.

Are you using subforms in your central entry form? I assume that you did not use subforms. If you base a single form on multiple tables, Access actually binds the form to a query that joins the multiple tables. I assume that for the first four datasets, you entered data such that at least one field in each of the tables had something entered. For the additional datasets, if you did not enter data in at least 1 field from all of the tables then a matching record was not created and thus does not satisfy the joins of the query and thus are not shown when you reopen the form. To avoid this, what is typically done is that you would have a main form with various subforms. The main form would be bound to the primary table which in your case is the patient table. Each subform would then be based on the related tables.

Now, you did not mention much about how your tables are related. At the heart of every successful relational database is the table structure. Before doing any other forms, queries or reports, it is critical that your table structure is correct.

For example, let's say that you work for a doctor's office and that you are tracking patient visits to that office. Most people visit the office more than once. So, a patient has many visits. This describes a one-to-many relationship between the patient and visits. To represent that in forms, you would have a main form based on the patient table with a subform based on a table that holds the info about the various visits.

Let's say for example that you want to track patients' medications. A patient can take several medications. Also, a medication can be taken by many patients. So, you have 2 one-to-many relationships. When you have 2 one-to-many relationships between the same two entities (patients and medications) you have a many-to-many relationship. This is typically handled with 3 tables (patient table, medication table, and a table that joins both patients and their medications: also called a junction table).

Now, to make sure you are on the right track relative to your table structure can you zip & post a copy of your database (with any sensitive data removed) or at least your table structure?

Something similar to this

tblPatients
-pkPatientID primary key, autonumber
-txtFName
-txtLName

tblMedications
-pkMedID primary key, autonumber
-txtMedName

tblPatientMedications
-pkPatientMedID primary key, autonumber
-fkPatientID foreign key relating back to tblPatients
-fkMedID foreign key relating back to tblMedications

[dblife]

If this was Access 2003, I would ask, in the properties box for the form, do you have 'DataEntry' selected as 'Yes' - I do not know if this is the same in Access 2010?
This option allows data entry in the form but does not show exisiting records when opened.

[freeskier81]

Welcome to the forum!



Are you using subforms in your central entry form? I assume that you did not use subforms. If you base a single form on multiple tables, Access actually binds the form to a query that joins the multiple tables. I assume that for the first four datasets, you entered data such that at least one field in each of the tables had something entered. For the additional datasets, if you did not enter data in at least 1 field from all of the tables then a matching record was not created and thus does not satisfy the joins of the query and thus are not shown when you reopen the form. To avoid this, what is typically done is that you would have a main form with various subforms. The main form would be bound to the primary table which in your case is the patient table. Each subform would then be based on the related tables.

Now, you did not mention much about how your tables are related. At the heart of every successful relational database is the table structure. Before doing any other forms, queries or reports, it is critical that your table structure is correct.

For example, let's say that you work for a doctor's office and that you are tracking patient visits to that office. Most people visit the office more than once. So, a patient has many visits. This describes a one-to-many relationship between the patient and visits. To represent that in forms, you would have a main form based on the patient table with a subform based on a table that holds the info about the various visits.

Let's say for example that you want to track patients' medications. A patient can take several medications. Also, a medication can be taken by many patients. So, you have 2 one-to-many relationships. When you have 2 one-to-many relationships between the same two entities (patients and medications) you have a many-to-many relationship. This is typically handled with 3 tables (patient table, medication table, and a table that joins both patients and their medications: also called a junction table).

Now, to make sure you are on the right track relative to your table structure can you zip & post a copy of your database (with any sensitive data removed) or at least your table structure?

Something similar to this

tblPatients
-pkPatientID primary key, autonumber
-txtFName
-txtLName

tblMedications
-pkMedID primary key, autonumber
-txtMedName

tblPatientMedications
-pkPatientMedID primary key, autonumber
-fkPatientID foreign key relating back to tblPatients
-fkMedID foreign key relating back to tblMedications

Thank you for your input.
I'have posted a .zip with the db. Please consider that this db has been built in a german ms access. I hope this isn't interfering with your efforts.

tia

[jzwp11]

You have some serious normalization issues with your database. I would recommend that you read up & understand normalization before doing anything. This site has a basic overview as a start. I would recommend setting your current database aside and starting with a new one; no forms, queries or reports should be created until your table structure is normalized.

I assume that the PNR field you have in every table as the primary key somehow relates to a patient. You cannot do that. The PNR can be the primary key in 1 table but will be a foreign key in related tables.

In your lab results table you have a field for every test, that is incorrect as well. A test should be a record in a table and you would relate the test to the patient. I assume that a patient can have many visits where these tests can be repeated again and again, so it would be better to relate the patient to the visit and then relate the tests to that visit/patient combination. Something like this

tblPatients
-pkPatientID primary key, autonumber
-txtFName
-txtLName

tblPatientVisits
-pkPatientVisitID primary key, autonumber
-fkPatientID foreign key to tblPatients
-dteVisit (date of visit)

tblTests (holds only the tests as records)
-pkTestID primary key, autonumber
-txtTestname

tblPatientVisitTests
-pkPatientVisitTestID primary key, autonumber
-fkPatientVisitID foreign key to tblPatientVisits
-fkTestID foreign key to tblTests
-TestResult

Not knowing the details of the process you are modeling, the above table structure should only be considered as an example.

In order for us to help you achieve a normalized table structure,you will have to explain the medical process for which you are trying to capture data.

[jzwp11]

In response to your IM, the basic structure I proposed is a good start, you would only need to insert the observations and parameters relative to a visit.

A few questions for you on that.

Are the parameters of an observation in anyway related to the tests or are the tests only related to the observation?

Can the another patient undergo the same observations as another patient?

Are the parameters only applicable to 1 observation?
Are the parameters unique to a patient/observation?

[freeskier81]

In response to your IM, the basic structure I proposed is a good start, you would only need to insert the observations and parameters relative to a visit.

A few questions for you on that.

Are the parameters of an observation in anyway related to the tests or are the tests only related to the observation?

I hope i got the question.
At every given time point ( day 1 - baseline, +1h, +2h, +4h, +6h, +10h; day 2 - +24h, day 8 - +168h) a certain number of lab tests are done in every single patient. The same lab tests in all patients. so the results of every observation (e.g. +1h, +2h,....) are connected to the certain time point of the observation. kind of complicated and hard to describe...


Can the another patient undergo the same observations as another patient?

all patient are undergoing the 100% same procedure.

Are the parameters only applicable to 1 observation?
Are the parameters unique to a patient/observation?

the parameters/lab tests are repeated at every time point/observation and are the sam in all patients.

[jzwp11]

The one thing I am confused about is that you mention observations, parameters and tests. I now understand the observations as being time periods and I understand that the tests are medical tests you conduct at the various observation times, but what are the parameters? Perhaps a couple examples of each would help?

[freeskier81]

The one thing I am confused about is that you mention observations, parameters and tests. I now understand the observations as being time periods and I understand that the tests are medical tests you conduct at the various observation times, but what are the parameters? Perhaps a couple examples of each would help?

You're totally right - I've messed up a couple of terms. So I will try now to define a persistent terminology.

1. visit = each day a patient attends the clinic; 4 per patient, 1 screenig visit and 3 study visits.
2. observation = time point when blood is drawn to be used in lab tests or physical exams are conductes, respectively. 9 per patient.
3. tests = different lab tests. repeated for every observation.
4. parameter = lab test result.

have I cleaned up te mess?

[jzwp11]

Yes, that cleans things up nicely. Starting from what we had previously:

tblPatients
-pkPatientID primary key, autonumber
-txtFName
-txtLName

tblPatientVisits
-pkPatientVisitID primary key, autonumber
-fkPatientID foreign key to tblPatients
-dteVisit (date of visit)


Now add the observations. Since the times are fixed and apply to every patient, we'll set up a table to hold them. Each time period would be a record, so a record for each baseline, +1h, +2h, +4h, +6h, +10h...
I assume that you just go by hours and the actual day does not matter correct?

tblObservations
-pkObsID primary key, autonumber
-longHour (long integer number, baseline=0, then just the hours since baseline after that)

Now we have to relate the observation period to the patient visit

tblPatientVisitObs
-pkPatientVisitObsID primary key, autonumber
-fkPatientVisitID foreign key to tblPatientVisits
-fkObsID foreign key to tblObservations

We still need the test table

tblTests (holds only the tests as records)
-pkTestID primary key, autonumber
-txtTestname


Now relate the tests to each patient/visit/observation period combination

tblPatientVisitObsTests
-pkPatientVisitObsTestID primary key, autonumber
-fkPatientVisitObsID foreign key to tblPatientVisitObs
-fkTestID foreign key to tblTests
-Parameter

[freeskier81]

hi again!

I'm afraid i don't get your proposed structure.
Maybe it's because I had a completely wrong idea of how my DB should look like from the start.
I put many hours work in the design of my input forms - is that all lost or can I use them with the new design?
In the end I need to get the data out of the DB in some fashion like:
see postet tbl.

[jzwp11]

What you posted looks like a spreadsheet; data is not handled that way in a relational database, but you can extract it like that using queries. In my original response to your post, I included a link to a site that explained normalization. The rules of normalization are at the heart of relational databases. These rules are used to create the proper table structure. Once the table structure is created properly then and only then should forms be created. Unfortunately, any forms you may have created prior to the rework of the tables will not function properly and will have to be recreated.

[freeskier81]

What you posted looks like a spreadsheet; data is not handled that way in a relational database, but you can extract it like that using queries. In my original response to your post, I included a link to a site that explained normalization. The rules of normalization are at the heart of relational databases. These rules are used to create the proper table structure. Once the table structure is created properly then and only then should forms be created. Unfortunately, any forms you may have created prior to the rework of the tables will not function properly and will have to be recreated.

thougt so.
the spreadsheet I posted should gave been an example for one of the "results querries" I'd like to generate for using them with e.g. SPSS.
What I tried to point out with the example was, how I need the generated spreadsheet to look like and further to know if this is possible with your structure.

thanks!

[jzwp11]

Yes, outputting the data in the format you showed is possible with the use of queries with probably subqueries.

[freeskier81]

I don't get the observation table. I've got 9 time points/observations, do I need 9 variables in the tbl? or just one variable? and how do I connect the table?