Solved Data Entry Form W/Subform Creates extra records?

[danbl]

Okay .... I am now looking at simplifying the tables. While some are quite simple, others posse more questions as to normalization. If tables are in the simplest form it seems data is more fragmented? Some tables are reference tables and others should be tables that hold the data elements.
When I have some of these redone I will repost for your thoughts

 

[danbl]

You suggested that the Perioperative table be split into three sections which I see how to do, but each section is directly related to the first section, Thus a one to one relationship? So I am confused why to break this up??

 

[June7]

What do you mean by 'split into three sections'? I am not suggesting table be split. I am suggesting each form will have filter to only show desired operative type records. You were getting 3 records before but each had a bunch of empty fields. Now there will still be 3 records but without all the empty fields. All 3 records will associate with the same Visit master record. And there should be a new field for OperativeType.

 

[danbl]

I guess I was confused .... previous post lead me to believe you were suggesting splitting the table. With that said PreOp, interop and post op all have separate tabs in the sub form now??

 

[June7]

Not sure what I said led you to think that but sorry if I was not clear.

You had 3 separate tabs to begin with - that's what suggestion is trying to accommodate. You will still have 3 tabs - PreOp, InterOp, Postop.

 

[danbl]

I am creating some smaller tables as you have suggested. What is your thought on lookup (lookup wizard) being used in the other tables??

 

[danbl]

Here is a pass at your suggestions as I understood them ... queries and forms have not been redone. Tables added and relationships started. Is this a correct path?

 

[June7]

This is not what I was suggesting. My suggestion did not involve adding any tables (exactly what were the new tables).

I suggested a single operative data table (t_Perioperative Data renamed as t_OperativeData) with a single set of operative data fields (not 3 sets) and a new field OperativeType. Then each subform would have a RecordSource that filters on this new field:

SELECT * FROM t_OperativeData WHERE OperativeType = "Pre";

SELECT * FROM t_OperativeData WHERE OperativeType = "Inter";

SELECT * FROM t_OperativeData WHERE OperativeType = "Post";

Then a textbox on each subform bound to OperativeType field with DefaultValue as appropriate: "Pre" or "Inter" or "Post". Set textbox as Locked Yes and TabStop No.

But now I see table t_Advanced_Directive has the same non-normalized structure. Is this a new table?

Nothing you have done resolves the original issue of subforms creating 3 records. In fact, the situation is worse. Form/subform linking is not in line with relationships. Can't link sibling subforms that way. Even if you could, 3 subforms will still generate 3 records with lots of empty fields.

 

[danbl]

I will revert back to what we started with and try again?
The tables are as follows: t_Visit, t_Advanced_Directive, t_DVT_Data, t_PerioperativeData, and t_Temperature_Monitoring
These are the main tables.

 

[danbl]

Lets see if I am getting this right.
Are you saying to rename the existing t_PeriooperativeData to t_OperativeData and retain all the current fields, and add a field to hold the values Pre, Inter, Post ?
Would this necessitate a new table t_OperativeValue containing the mentioned values?

 

[June7]

I am saying that if you follow my design suggestion, renaming the table might make more sense but you decide. I also said you would remove two of the sets of repetitive fields. Instead of a set of fields for preoperative, a set for interoperative, a set for postoperative, there would be only 1 set of operative data fields. Perhaps rename Peroperiative to just Operative. Then would need a new field for OperativeType. A lookup table for the 3 operative values is not necessary in this case but you can make one if you want.

But what is Peroperative Indicator field for?

 

[danbl]

I have looked at the fields in the Perioperative Data table and don't see the repetitive fields you refer to. I can see were field descriptions may be misleading. I am not getting how adding the field Operative Type would solve the problem? The table has three district sections with the fields; Preoperative, Interoperative, and Postoperative that are Yes/No fields for the purpose of verifying data entry.

 

[June7]

Fields with a number suffix usually indicate repetitive type of data. Example: University1, University2, University3.

Are you saying your 3 distinct sections don't document the same kind of data? PQ1 and PQ8 and PQ15 aren't documenting the same thing? What are they documenting?

The problem is because of 3 subforms bound to same table, you are getting 3 separate records, each of which has many empty fields. My suggestion will still generate 3 records but not with empty fields. If you want to stay with a single record and still have 3 subforms then will need VBA to manage this or 3 tables.

 

[danbl]

Are you saying your 3 distinct sections don't document the same kind of data? The questions are either Yes/No responses and if the question does not apply then the N/A field is selected.

PQ1 and PQ8 and PQ15 aren't documenting the same thing? NO they are not the same. Each question is defined in the description section of the table. (Some descriptions may need clarification)

What are they documenting? What is documented is whether or not the event (QUESTION) happened or not, or does it not apply for the particular visit.

Could the problem be resolved by making one form to capture all the data?, Or dividing the larger table into three separate tables and then link each table to the associated sub form? (This maybe what you 've been saying all along and I am now just catching on)

 

[June7]

So PQ1 and PQ8 and PQ15 each document a different question? Not the same question asked 3 times?

If you don't want to fully normalize data structure, options have been provided. Restated:

1. single table and single subform instead of 3 and no code required

2. eliminate columns from table and use 3 subforms to create 3 records (but since you says questions are not repeated for each stage this seems not appropriate after all)

3. current table structure with 3 subforms and code to manage input to same record

4. 3 tables and 3 subforms

 

[danbl]

Options 1 & 4 seem the most logical to me as I have no experience with code.
Option 1 - this might be a large form that would require user to have to scroll through.
Option 4 - would let me use the current forms and just fix queries etc. Each table would have a primary key. My question is if this is used what is the best way to link them - Primary / Foreign key? "vid" is the primary key in the Visit table and the foreign key for the others.

 

[June7]

Option 4: Certainly primary and foreign keys. Each of the 3 operative tables would link to Visit with vid.

 

[danbl]

Great ... I got one right The visit Id was what I thought I should use. I will work on this and repost later after I test with adding records again.

Many thanks for the help as I learn along the way

 

[danbl]

I have created the new tables / queries / forms. When I open the forms they appear with all the questions as they should. However the preop, interop, and post op forms are not visible in the sub form. ??

 

[June7]

Don't use queries joining tables as RecordSource. Just reference the tables.