Nested Headers - Nested Subqueries/ Normalization Correct?

[dalski]

Video linked for explanation.

I have 7 different HeaderTypes. Which I need to be displayed in a continuous form; similar to grouping on a report. Do not be fooled by the apparent sorted order of the BillTemsF. It appears sorted because correctly because BillHeadersAndItemsT ID has been created in order. In real-use this will not happen with the user making errors/ deleted items... I'm not greatly experienced in reports but I believe they do not allow data manipulation; hence a report is no use. I'm going to be grabbing data from different perspectives so need to grasp this.

Specs

• H0 (Sub-Bill) is optional & will not always be used. Having this optional H0 certainly complicates things, but is analytically beneficial for other purposes. If I have to remove it then I have to remove it but I know it is possible as other apps have this.
• H1 Will always be used & dependant on the complexity of the Bill subsequent levels of headers will be used.
• H6 (a Comment) will sometimes be used and is different from H0 - H5; because it is 'nested/ covariant' to whatever parent header it belongs to.
• H7 (a BillItem) is quite different from H0 - H5. Similar to H5 as it is 'nested/ covariant' to a parent header but it is the only record which will contain a Quantity, Rate & Total (in qry).

I know several nested tables & nested subforms may be suggested; every application I've used avoids this & displays it as a continual list (just referring to forms here). I imagine also if the header types are isolated the user cannot convert the HeaderType0 - H6 once they have been created; allowing for better flexibility. Not from the dbEngine anyhow, but a complex loop will need to be devised I imagine.

I was thinking of nested subqueries to grab & filter, but been experimenting for a week now & has not been fruitful. Also I know my design is not correct so I need help.


Current Design (Single Header Table)
- Violates 2nd Normal Form; transitive dependancy on ParentHeaderID. Will work for the current Tender but when copied to a new Tender it's reference will not change & incorrectly refer to the same ParentHeaderID.
- Advantages - allows user to change HeaderType allowing restructure of the bill.
- Disadvantages - currently have not ascertained how to sort properly.

View attachment 119448

 

[Pat Hartman]

I really don't understand the question so I'll try to translate to two different, more common problems such as an org chart and BOM. These are self referencing relationships. Each record has one and only one parent record and so has a FK that points to the parent. In the case of an org chart, if you want to move an employee and his dependents to a different place, you would simply change the FK of a record and automagically all children get transported with the lower structure intact. However a BOM is different because you might want to duplicate a leg rather than move it. In that case, you need to capture the new FK of the selected record as you insert it, then as you loop through all the child records, you have to modify the FK of each record to be the new ID of the parent record.

So, the org chart gets "moved" by replacing the FK of the top level record and that would be the same for the BOM but if you want to copy the BOM (which doesn't make sense for employees), you must do it in a loop so you can capture the new ID of the parent record to use for the FK.

 

[Pat Hartman]

@dalski, The experts here don't post to gather "likes" and there is no need to acknowledge posts so please use your words. Did my response solve your problem? Did I even understand your question? If you have an answer, please mark the question as solved.

 

[LarryE]

Video linked for explanation.

I have 7 different HeaderTypes. Which I need to be displayed in a continuous form; similar to grouping on a report. Do not be fooled by the apparent sorted order of the BillTemsF. It appears sorted because correctly because BillHeadersAndItemsT ID has been created in order. In real-use this will not happen with the user making errors/ deleted items... I'm not greatly experienced in reports but I believe they do not allow data manipulation; hence a report is no use. I'm going to be grabbing data from different perspectives so need to grasp this.

Specs

• H0 (Sub-Bill) is optional & will not always be used. Having this optional H0 certainly complicates things, but is analytically beneficial for other purposes. If I have to remove it then I have to remove it but I know it is possible as other apps have this.
• H1 Will always be used & dependant on the complexity of the Bill subsequent levels of headers will be used.
• H6 (a Comment) will sometimes be used and is different from H0 - H5; because it is 'nested/ covariant' to whatever parent header it belongs to.
• H7 (a BillItem) is quite different from H0 - H5. Similar to H5 as it is 'nested/ covariant' to a parent header but it is the only record which will contain a Quantity, Rate & Total (in qry).

I know several nested tables & nested subforms may be suggested; every application I've used avoids this & displays it as a continual list (just referring to forms here). I imagine also if the header types are isolated the user cannot convert the HeaderType0 - H6 once they have been created; allowing for better flexibility. Not from the dbEngine anyhow, but a complex loop will need to be devised I imagine.

I was thinking of nested subqueries to grab & filter, but been experimenting for a week now & has not been fruitful. Also I know my design is not correct so I need help.


Current Design (Single Header Table)
- Violates 2nd Normal Form; transitive dependancy on ParentHeaderID. Will work for the current Tender but when copied to a new Tender it's reference will not change & incorrectly refer to the same ParentHeaderID.
- Advantages - allows user to change HeaderType allowing restructure of the bill.
- Disadvantages - currently have not ascertained how to sort properly.

View attachment 119450


View attachment 119448

We need to understand what your business is and what you are attempting to keep track of. Please explain what Tenders, Headers, Units and Bills are and how they relate to one another. You already stated your design is flawed, so let's get that corrected before anything else. Once we can understand what these things are, someone may be able to help you get the design corrected.

 

[dalski]

org chart and BOM.

Thanks Pat, I always try to hit Like to show my appreciation to anyone who helps me. A belated response as googling BOM brought me no results so unsure what that BOM is & I struggle to convey what my issue is when i don't really know the subject; so appreciate i am unclear - apologies.
You have astutely identified that self-referencing headers is an issue & also as referred to in your BOM e.g. when the headers are duplicated this will cause an issue also. You're grasping my issue.

The issue is:
1 - I think my Schema is wrong to being able to sort & group headers; like Microsoft Word. Nested paragraphs like in a multi-level list. I am unsure if i should have separate tables - for each header type, - for BillItems?
2 - I'm unsure as how to query/ group these items in a query... when it happens. Like I said it appears that items are being sorted correctly but this is not the case & only because they have been entered in the correct order.

Sorry I know it's a very bad explanation; quite overwhelmed with it.

Please explain what Tenders, Headers, Units and Bills are and how they relate to one another... so let's get that corrected before anything else. Once we can understand what these things are, someone may be able to help you get the design corrected.

Thanks Larry,
I'm happy with Tenders & Bills; pretty sure that is correct. My question relates to the BillHeaders&ItemsT & Header Types mainly; should HeaderTypes be split into each individually table. I'll make another video now mentioning the other tables also.

 

[dalski]

Another video; hopefully clearer. Ultimately the topic is creating the schema & queries to display a multi-level list. like in Microsoft Word.

Tenders & all records will be copied as to avoid reinventing the wheel each time.

@Pat Hartman - it sounds like I'm not massively wrong with the logic with the ParentHeaderID field & a 1-1 relationship there. Looping on creating a new record... I had this in mind though I was hoping Access had something else in it's toolkit.

EDIT - attached new db; conditional formatting was wrong on previous upload; apologies.

 

[Pat Hartman]

Sorry. BOM is bill of material. It is used to describe the hierarchy of parts used to make a more complex part.

Acknowledging a post with a like may seem polite but it is not at all helpful.

The problem is that 1.1.1 is a string and will not sort correctly since 10.1.1 will come before 2.1.1 To solve this problem, you need to separate each level and store it as a numeric value so you can sort it correctly. OR, you need to use leading zeros and assume a fixed length for each node. 001.001.001. Then 002.001.001 will sort before 010.001.001 as you need. You can start by adding a level and a sequence number. It is probably simpler to store the 1.1.1 version when you create the record. You take the value from the parent and append to it .x with x being the sequence number assigned to this record.

However, storing the 1.1.1 string does cause more than the expected type of problems because if you want to move something around, you have to change all sibling records to resequencing them and also all child records of the siblings and their children, etc. So on balance, if you envision having to move things around after they are created, you are far better off creating a function to calculate the 1.1.1 on the fly. I haven't examined MagP's Treeview closely so I don't know how it works but it might actually solve this problem for you.

 

[LarryE]

should HeaderTypes be split into each individually table

No, but if each Bill could have one or multiple Headers, then you need a BillID FK in the HeaderTypes table.

 

[LarryE]

Sounds like you are looking for a Tree View system or something similar.

 

[dalski]

Sorry. BOM is bill of material. It is used to describe the hierarchy of parts used to make a more complex part.

No need for apologies, yes that's pretty much exactly what I'm doing.

string and will not sort correctly since 10.1.1 will come before 2.1.1...

Thanks, yes that's why I want a deeper sense of sorting. Ideally relating to the inherent 'level' of each item.

I haven't examined MagP's Treeview closely so I don't know how it works but it might actually solve this problem for you.

Thanks Pat, yes that's pretty much exactly what I'm after. A sort & group based on 'level type' sort of thing. Just watching the video on Youtube, man I wish i found this a week ago; thank you so much.

Sounds like you are looking for a Tree View system or something similar.

Thanks Larry, yes; hierarchical editing affects children, but I need to be able to show multiple columns.


MajP's Treeview
OMG, OMG, OMG - 30 mins in & this is awesome.