• ** There has been a recent site upgrade. Please clear your browser cache to avoid issues. **
  • New forum feature - post voting and best solution

    Check out this thread for the details: https://www.access-programmers.co.uk/forums/threads/new-forum-feature-post-voting-and-best-answer.314134/

    This new feature looks great to me! :)

  • We now have 3 forum themes

    Go for the default (light) theme, Shades of Grey or Shades of Blue. I just added the Blue one.

    The thread about it is here: https://www.access-programmers.co.uk/forums/threads/new-forum-theme-shades-of-blue.314136/

Solved Data Entry Form W/Subform Creates extra records? (1 Viewer)

June7

AWF VIP
Local time
Yesterday, 18:54
Joined
Mar 9, 2014
Messages
3,337
Yes, and it means redesigning the one table to eliminate the repeated fields for operative types. But since I suppose the existing data must be preserved, this gets a bit tricky. This is where a UNION query will be useful.

If you are tracking 3 operative types, why is table named t_Perioperative_Data ?
 

danbl

Registered User.
Local time
Today, 00:24
Joined
Mar 27, 2006
Messages
257
It would be good to maintain the data in this new database but may not be essential as it is preserved in the current database.

t_Perioperative_Data was used because the 3 operative types make up the perioperative data. I see your point that this could be broken down to 3 separate tables and linked by primary/foreign key relationships.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 22:54
Joined
Feb 19, 2002
Messages
29,690
Yes, each type would be a separate record and each subform would be bound to a query that selected only a specific record type.

If this is a learning experience, why not go all the way and learn how to actually create a normalized schema that can be used to support any set of questions?

June and I have been offering band aids because the alternative might be more work than you were prepared to do.
 

danbl

Registered User.
Local time
Today, 00:24
Joined
Mar 27, 2006
Messages
257
This will now be an opportunity to learn as I said .... I am already looking at normalizing and what was missed previously.
I get the point of each type being its own sub form with a related query for data retrieval but my question is how does these sub forms work for data entry? and what will prevent the same thing from Happening? Is it a matter of not using the primary key/ foreign key relationship between the main form "visit" table and other tables more than once??:unsure::unsure:
 

June7

AWF VIP
Local time
Yesterday, 18:54
Joined
Mar 9, 2014
Messages
3,337
Will still use pk/fk link for each form/subform and each subform will create a separate record. Therefore there will still be 3 child records associated with each parent (master) record but since the fields are more normalized, will not be 3 records with lots of empty fields in each.

Textbox for operative type should be set with a DefaultValue on each form for the appropriate value and then Locked so user cannot edit. Or don't even display this to user, set it as not visible.
 
Last edited:

danbl

Registered User.
Local time
Today, 00:24
Joined
Mar 27, 2006
Messages
257
I know have lots of time to work on this, so I would like to be able send this back to both of you in the future.
Is this a possibility
 

June7

AWF VIP
Local time
Yesterday, 18:54
Joined
Mar 9, 2014
Messages
3,337
Attach file to post in thread.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 22:54
Joined
Feb 19, 2002
Messages
29,690
Post it in stages so we can help along the way. Have fun :)
 

danbl

Registered User.
Local time
Today, 00:24
Joined
Mar 27, 2006
Messages
257
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

Registered User.
Local time
Today, 00:24
Joined
Mar 27, 2006
Messages
257
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

AWF VIP
Local time
Yesterday, 18:54
Joined
Mar 9, 2014
Messages
3,337
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

Registered User.
Local time
Today, 00:24
Joined
Mar 27, 2006
Messages
257
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

AWF VIP
Local time
Yesterday, 18:54
Joined
Mar 9, 2014
Messages
3,337
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 22:54
Joined
Feb 19, 2002
Messages
29,690
1-1 relationships are extremely rare in the real world. Do not make a group of 1-1 relationships because you want to separate related attributes into their own tables to reduce the column count of the original table. If you have too many columns in a table, it is most likely because there is one or more repeating groups that should be moved to a 1-m table.

The normalization process creates a set of small (in width) tables to ensure that one piece of data occurs in one and only one table. For example, you don't repeat the customer name in both the customer table and the order table. Customer name belongs ONLY in the customer table. When you want to create an order, you include a field to hold the CustomerID and bind it to a combo. That lets you link the order to a customer and the combo will show the customer name but the customer name isn't stored in the Order table, only the customerID is. That lets you pick up the bill to address information as well as the name without duplicating them.

Once the data has been normalized to eliminate redundancy, you use queries to join the tables back together to provide "information"
 

danbl

Registered User.
Local time
Today, 00:24
Joined
Mar 27, 2006
Messages
257
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

Registered User.
Local time
Today, 00:24
Joined
Mar 27, 2006
Messages
257
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?
 

Attachments

  • Example2.zip
    1.4 MB · Views: 9

June7

AWF VIP
Local time
Yesterday, 18:54
Joined
Mar 9, 2014
Messages
3,337
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

Registered User.
Local time
Today, 00:24
Joined
Mar 27, 2006
Messages
257
I will revert back to what we started with and try again? :unsure:
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

Registered User.
Local time
Today, 00:24
Joined
Mar 27, 2006
Messages
257
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

AWF VIP
Local time
Yesterday, 18:54
Joined
Mar 9, 2014
Messages
3,337
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?
 

Users who are viewing this thread

Top Bottom