Solved Data Entry Form W/Subform Creates extra records?

[danbl]

The attached database sample is to be a replacement for one currently in use.
All records from the Current database have been imported, the associated queries and reports all function the way they are supposed to. (Validated by comparing reports between the two versions ..... I have only included one months data plus entered two additional records with future dates to show the issue.)
The last step was to validate the data entry function.
The data entry form is made up of a main form, visits, and a sub form with multiple tabs. When this data entry form is used and tables are checked, each record is found in the visit table (t_Visit), the DVT table (t_DVT_Dat), the temperature table (t_temperature_monitoring), but the perioperative data table (t_Perioperative data ) shows each record three times. Why is this happening?
Some of the queries do not seem to pull the data when you check for the records; also when some reports that are generated ae blank (DVT Reports) Why is the data not being pulled?
I am completely stumped as to why this is happening. Can someone tell me why this occurs and how to fix it?

 

[June7]

Exactly which form - frmVisit?

I suspect you are getting 3 records because you have 3 forms bound to the same RecordSource, each entering a record. I would expect similar outcome with the two forms using qryAD as RecordSource. Quick fix is to have 1 Operative form and arrange controls into 3 groups. Otherwise, redesign data structure to allow for multiple records and have another field for OperativeType. Do similar for Pat and Advance Directive. Your table structure is not fully normalized.

Should not include parent table t_Visit in subform RecordSource.

Which queries are not displaying data?

What parameters should I use for testing report?

 

[danbl]

Huuummmm .......

frmVisit is the correct form.

I am far from and experienced Access user; have had lots of assistance with some of this build. I am a bit lost on what you are trying to tell me??? How would I know where to make the changes? What is confusing is that the data appended into the database produces the correct info/reports.

 

[Pat Hartman]

1. Your schema is not properly normalized. Any time you add a numeric suffix, you should know that you have a 1-many relationship and the many-side data should reside in a separate table. You have PNA_1- PNA_22 with a couple of a's as well as PQ's, If PQ and PNA go together, they can both be in the same table.
2. The visit table is NOT needed in any of the subform queries. the Master/Child link is providing the Foreign Key when the record is saved.
3. The perioperative data table is bound to three subforms. You are probably getting three records because the main form is set to data entry and so as you move from form to form, Access is making sure you have a "new" record. I would fix the design issue with this table. Add a "group" to the table so you can have three groups of data. That way each form can be bound to the same table but each question ends up in a separate row. You will have to populate the "group" in the subform's BeforeUpdate event.

You can verify that the duplication is being caused by the visit form being opened as data entry. Just temporarily change the Switchboard Items table to test.

I just tried removing the unnecessary joins as well as changing the way the form is opened and neither solve the duplication. Fix the table design issue and move on.

 

[danbl]

Okay ...... at this point I am lost as how to fix this.
1. when this was originally built no one was not aware that number suffixes were a bad thing. Not sure what would happen pulling this to another table
2. what happens when you remove the visit table from the sub queries to the rest of the functions?

 

[Pat Hartman]

I thought I answered that question.

The two issues I though might be causing the problem were not. Removing the extraneous joins didn't fix the duplication and changing the way the form opens didn't fix it either.

To fix the design flaw, you will have to also convert the data. Easy enough but tedious since you'll either need 22 queries or you'll need to write VBA code to do it. Then once the tables are correct, you will need to change the subforms on the three tabs to either continuous or DS view so they will show multiple records. I.e. one for each question.

It isn't suffixes per se that are the problem. It is the repeating group. Think of it this way. If you were creating an employee app and you needed to store dependent information, would you just add some random number of columns to the employee record and hope you had added enough? Or would you create a separate table so you could keep each dependent's info in a separate row so it would be easy to keep multiple attributes. If you go the spreadsheet route, you end up with FName1,LName1,DOB1, FName2,Lname2,DOB2, etc until you think you've added enough.

We can help you do it step by step once you determine that there is no other way. I'm pretty sure I've used tabbed dialogs bound to the same query/table and not had this problem so maybe there is something else at play.

 

[June7]

I edited my previous post probably after you read it, review again for suggested solution.

 

[Pat Hartman]

Arranging the forms in three groups is a band aid that will solve the problem.

If it is possible, I would much prefer to actually solve the problem by fixing the table schema. You should be able to do it with our help in about 2-3 days. Less if you don't have multiple people offering conflicting advice. Someone experienced could do it in a couple of hours since they would have fewer false starts.

 

[danbl]

Having no experience with VBA that option is out. 22 Queries??? I am not sure where I would start.
I have watched several videos on forms and sub forms to create this so I am not sure how I created this multiple bound sub forms??

I would appreciate any assistance as this is the last piece to getting this revision in use.

Thanks ...DB

 

[danbl]

I am very happy to have your assistance!!! 2-3 days is not an issue and I would much prefer to have one point of contact

 

[June7]

I did say it was a quick fix, not 'best fix'. What you have can be made to work. My 'quick fix' is one way. Managing 2 or 3 subforms to edit the same record will likely involve VBA. "Best fix" is most likely redesigning data structure to allow for multiple records. You decide and I am sure guidance will be offered by someone. I am happy to defer to Pat.

 

[danbl]

I appreciate everyone's help!!!!! .. I will await Pat's reply and help

 

[Pat Hartman]

The 22 queries are one for each instance of the data. I said 22 but I think it is a couple more because you have some instances that have alpha suffixes. I can lay out the new table and create one of the queries. You would use the pattern and create the others. It's easy enough. Open the sample, change the column names, save as the next query. Then create either a macro or a VBA procedure (actually easier in my opinion) to run all of the queries. The point of this is, when you do conversions, you almost always need to run them more than once so you may as well make the conversion into a procedure so you can fix stuff as necessary and just rerun the procedure.

If you want to use June's suggestion, it will fix your problem but you'll still be left with a bad schema that will be harder to modify if the requirements change. Maybe someone will come up with a new procedure for anesthesia. What would you do to accommodate this in your existing schema. With the normalized schema, you will simply add a new row to your question table. I'm guessing that you are not doing any analysis of the data so that won't change. June's solution really is the simplest way home if you don't want to spread your wings and learn about normalization.

 

[June7]

It is a balancing act between normalization and ease of data entry/output. "Normalize until hurts, denormalize until it works." How far you take this is up to you. I recommend you study the topic and get a good understanding before making decision.

 

[Pat Hartman]

I looked at more of the tables. The rest have repeating groups also. So, danbl, you'll need to redo the whole app, not just 1 table and three subforms. Do what June suggested. It's wrong but won't be worse than what you have and it will solve the problem. As long as you have no expansion plans, you'll be fine.

 

[danbl]

Pat,
Thank you for your insight and suggestions. I found out last night we will not be continuing with the database!!

However I will use this as a tool to learn from. I think using the table analyzer as a starting point maybe useful and looking at how the primary and foreign key relationships are setup. Is it possible to communicate with you directly with questions??

Thanks, Dan

 

[Pat Hartman]

The database analyzer isn't sophisticated enough to identify the repeating groups. Repeating groups where the column names are the same with suffixes such as year1, year2, etc or child1, child2, etc are easy enough to identify. Your names are (or would be) all different. What you needed to recognize is that they are all questions and so the table schema would have been something like:

tblGroup (a way to organize groups of questions)
tblQuestions (each question plus a group and a sequence number to organize them within the group)
tblSurvey (the header table for a person)
tblResponse (one row for each question to hold the response)

 

[danbl]

I think I need to explain the why the names were done the way they are? There are three sections; Perioperative, Intraoperative, and Postoperative. The idea was to have a continuous flow or sequence.

What you are saying I believe is that the sections could be different tables and have different name sequencing? If this is the case then how would you suggest the names be different? Each question has a yes/no / NA possible answer.

 

[June7]

I understand the intent. It's the execution that fails. No one is suggesting you have 3 tables (although that is another non-normalized option). However, I did suggest you have 3 records instead of 1. This would require another field in table for operative type (Peri, Intra, Post). Populating this field can be automated by textbox DefaultValue property on each of the 3 subforms. Each subform would also have a RecordSource that filters for the appropriate OperativeType. So far design options are:

1. Current table structure with one subform with 3 grouped areas of controls for the 3 operative types OR 3 subforms and VBA code to synchronize the 3 forms to edit same record

2. Still 1 table but each operative event will be a separate record and a field will identify the operative type - allows for 3 subforms each with appropriate filter criteria

3. Three tables and 3 subforms - someday you will need all 3 tables data in one dataset and a UNION query will be needed

 

[danbl]

As I have said this database is being retired. So now this is a learning opportunity for me.
I get the idea of three operative types ... so each type would have its own sub form for data entry, (Option 2) ?