How best to handle multi-step insertions on multiple 1 to Many Tables

[padlocked17]

Good Afternoon All,

Working on an app that has 6 major data types. Plans, Categories, Requirements, Solutions, Programs, and Vehicles. I have a plethora of 1 to Many tables that capture the relationships between these major types of data. What I'm trying to do next is build a multi-step form (that will utilize a tab control) to walk through building the 1 to many tables to capture a relationship between the data types. The data is fairly hierarchical (ie. a Vehicle must be assigned to a Program, a Program must be assigned to a Solution, a Solution must be assigned to a Requirement, a Requirement may be assigned to a Category, A Category to a Plan, etc.

Looking for ideas on how to implement a way to conduct a multi-step approach to building out the relationships to existing data and also allow for the creation of records at each step along the way.

Ideally I'd like to capture the user inputs and make all of the changes at the end, so that if they cancel the wizard, no changes would be made.

Any thoughts at all on how to best approach this? I was contemplating temp tables to capture all relationships built in 1-many tables and also temp tables for any newly created records, then running a mass insert at the end of the process. Wasn't 100% sure about the best way to handle the complexity in the multi-step process. I'm assuming just a VBA routing to display or not display tabs on a Form Control?

 

[Uncle Gizmo]

Any thoughts at all on how to best approach this?

It just sounds all wrong....

Like your initial design was flawed and you have kept going with it and ended up in a corner.

I have no idea about your database, it might well be perfect, but from what you describe, it just sounds like a big problem....

 

[June7]

You show 6 data entities and you say they are hierarchical. Forms can be nested to about 7 levels.

Otherwise, an unbound form with VBA to save data is an option - however, using autonumber keys to relate data complicates this approach. I have never used unbound form for data input.

 

[jdraw]

I think the best place to start would be to describe the business (processes) in terms of the subjects you have identified.
The business processes/rules identify the relationship(s) between the entities/subjects/tables. You need to structure the tables and relationships to support the business as a data model. You can test the model with sample data and sample scenarios. This will give you a chance to vet and adjust the model which will become a blueprint for your database.

 

[Pat Hartman]

Ideally I'd like to capture the user inputs and make all of the changes at the end, so that if they cancel the wizard, no changes would be made.

This will be a nightmare to implement if there is any chance at all that multiple people can be performing the process at the same time because of the autonumbers. And in a multi-user environment, you ALWAYS have this problem unless you use a queueing table where you can lock out everyone else from a specific process until person1 is done.

Are you trying to build an interface to create data that doesn't yet exist or are you trying to move existing data around which is a whole different problem.

Having six levels deep is going to make for a very confusing interface. This may be a case where you want to try to use subdatasheets. This method is clunky and limited to only three levels. So you have a main form in single view. A subform in dsview, and a subsubform in ds view.
The subform shows the plus sign to indicate that there is something lower. When you click to expand the subsubform, the + turns to a -.

This is not a pretty picture. I just built it to show that it is possible. You have to fight with Access to make it happen.
1. you need to modify the table to add a height to the subdatasheet and you have to provide a table name for it
2. When you first define the subform, leave it as single view so you can add the subsub form. After the form is added, you can then change the subform to DS view. If you do it the other way around Access complains and forces the subform to be single view.

Once you understand how this works, you can create multiple versions so you can drill down. The top version is lvl1 as a single form, then 2 as a subform and 3 as a subsubform, the Second one joins lvl1, lvl2, and lvl3 and then uses 4 as the subform and 5 as the subsub form. Then the third version joins 1,2,3,4 and uses 5 as the subform and 6 as the subsub form.

NOTICE how i named the navigation bar so you know what level you are navigating. This feature is the NavigationCaption on the Format tab. It is initially blank.

I added the database I used. It is a db where I test things so there is a lot of junk to ignore. Look at the objects named lvl1, 2, 3, 4 I was trying to make a four level sample when I discovered the three level limit so in this the sfrmLvl2 is the "main" form rather than lvl1 as you would expect.

And here's a picture of it in an actual application. This form is a little colorful because the user wanted to see green, yellow, red to know how complete the item was.

 

[padlocked17]

It just sounds all wrong....

Like your initial design was flawed and you have kept going with it and ended up in a corner.

I have no idea about your database, it might well be perfect, but from what you describe, it just sounds like a big problem....

I know it sounds wrong, but I'm pretty confident in the normalization of the design. The issue is that the data for the project is fairly complicated with a lot of messy relationships that can exist. Lots of many-to-many relationships that I need to capture, but the business rules of the organization drives some defined relationships amongst these 6 major data types.

I could incrementally only allow forms to be built to create the connections that are allowed, but I'd like to have a "Wizard" like interface that takes me from start to finish.

 

[padlocked17]

I think the best place to start would be to describe the business (processes) in terms of the subjects you have identified.
The business processes/rules identify the relationship(s) between the entities/subjects/tables. You need to structure the tables and relationships to support the business as a data model. You can test the model with sample data and sample scenarios. This will give you a chance to vet and adjust the model which will become a blueprint for your database.

Absolutely. Did this and the model support the business rules ... now it's just trying to streamline the interface to make it as easy and user friendly as possible.

 

[padlocked17]

This will be a nightmare to implement if there is any chance at all that multiple people can be performing the process at the same time because of the autonumbers. And in a multi-user environment, you ALWAYS have this problem unless you use a queueing table where you can lock out everyone else from a specific process until person1 is done.

Are you trying to build an interface to create data that doesn't yet exist or are you trying to move existing data around which is a whole different problem.

That is an extremely valid point and something I will have to consider.

The interface is intended to be used with existing records, and just to create an entry in a join table to create the relationship. The idea then was while we were going through a multi-step wizard to build the relationships, if the records didn't exist, I was wanting to create the record and insert into the join table at that point the correct references.

 

[padlocked17]

You show 6 data entities and you say they are hierarchical. Forms can be nested to about 7 levels.

Otherwise, an unbound form with VBA to save data is an option - however, using autonumber keys to relate data complicates this approach. I have never used unbound form for data input.

I don't think I'm looking at a nested form approach. Anytime the 6 entities can be related, they are always in a many to many relationships so I have join tables for every instance where they can be related. I'm just trying to think through a neat and nice way of allowing the user to start at the bottom of the "logical" relationships and work their way up. I could easily require the user to go into each entity type and create a record from each form to the respective join table. I was just hoping to do it in a multi-step wizard approach (ie. you review and create a record for the first many-to-many). Then after that you move to the next level up and create the next many-to-many via a join table and so on.

 

[jdraw]

Absolutely. Did this and the model support the business rules ... now it's just trying to streamline the interface to make it as easy and user friendly as possible.

Perhaps you could post the model and comment on your proposed activities in some sort of priority.

 

[June7]

Yes, you can build your 'wizard', most anything can be done with enough code. Again, using autonumber key complicates.

 

[Pat Hartman]

If you go with a wizard approach, I would go top down rather than bottom up. That is more logical and easier to manage if lower levels can be used on multiple upper levels so for example if record 45 on level6 can be used on 5 different level5 records, that's fine, but now when you are choosing for level4, you have to choose the level 5 record that has the correct level 6 records attached. You have multiplied the number of choices because you might have multiple paths of the child records.

You start with the Boeing 747 end item and drill down. You don't start with a rivet and work your way up. The other issue with doing it your way is change control. If you connect level 5 with 6, that relationship ends up with an ID. If you pick that ID and then change the level 6 item later, you have changed that relationship for all other instances that use that level 5-6 path.

Let me try this another way. When you go top down, you are picking individual items. Using your idea, each time you move up a level, you are no longer choosing an item, you are choosing a path.

Work through the process on a whiteboard and see what happens. If you really have assemblies the way you do when building an airplane, you have to KNOW you choose an assembly because you can't change any of the details of an assembly. The bill of material STOPS at the assembly. If you want to see the details of an assembly, you have to open the BOM for the assembly. They act like black boxes. Assemblies are interchangeable with parts except they cannot be further defined.