Nested Headers - Nested Subqueries/ Normalization Correct? (2 Viewers)

[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.

 

[dalski]

you are far better off creating a function to calculate the 1.1.1 on the fly.

Sorry Pat, do you mean a subroutine/ function looping through with code?

 

[Edgar_]

A few tips:
1. Use autonumber fields for primary keys, you have tables without it.
2. Add a sort column for order control, you can calculate the current maximum Sort value in the filtered dataset by adding 1 to new entries using the default value, for example. This ensures items are inserted in the correct order automatically, but that depends on how you want to program the experience.
3. Sort your form using the sort column and since the data is hierarchical (e.g. parent-child relationships), include that in your sort logic as well.

4. Add buttons to automatically adjust sort order. For example, take this dataset:

Sort | Description
19   | Excavation
20   | Slabs
21   | Walls
22   | Finishes
23   | Backfill
24   | Cleaning Up

Let’s say you want to move Backfill right after Excavation. You can run an update query like this:
1. Increase the Sort value by 1 for all rows where Sort >= 20 AND Sort < 23 (current sort).
2. Set Backfill's Sort to 20.
This results in:

Sort | Description
19   | Excavation
20   | Backfill
20+1 | Slabs
21+1 | Walls
22+1 | Finishes
24   | Cleaning Up

That’s a simple way to do it quick. You can also create buttons to move records up/down one position at a time. I think you're already using an column for sorting, hRef might be your Sort column, you can use that to order your records regardless of their insert order.

5. This dataset is recursive, so a tree view would be ideal. However, Access’s built-in tree view doesn't support tabular layouts. You would have to resort to hacks like:
-Using a monospaced font..
-Setting max character lengths for descriptions to line up subsequent columns (e.g. unit, quantity, rate).

If you stick with a continuous form, you're not far off. Just make it simulate the features of assigning parents and moving up and down in a list.

For a real tree view in Access, consider embedding a Web Browser control and rendering a custom tree using HTML and JavaScript. This gives you full control over the structure and appearance, including collapsible rows. In HTML, collapsing and expanding rows is all about setting rows' display style to none to hide them. JavaScript would be required for that.

6. If you need a printable version and don’t want to create an Access report, exporting to Excel is a quick alternative.

Anyway, for any of these, VBA is going to be required.

 

[MajP]

I think you will be very interested in my presentation here starting at 21 minutes

Access Europe - Wed 7 Aug: Using Treeviews with Access Applications (Pete Poppe)

The next Access Europe User Group meeting will be on Wednesday 7 Aug 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome Pete Poppe who is very well known to Access World Forum members as MajP In this session, Pete will...

www.access-programmers.co.uk

You will see it pretty much does everything you are asking and far more.

You can add levels, Sort levels, Drag and drop items in new levels. It does the 1.1.1 numbering automatically as you add, delete, and drag and drop.
I would also look at the ItemGenie demo because that has a lot of these features with much more icons and formatting manipulation.

Access Europe - Wed 7 Aug: Using Treeviews with Access Applications (Pete Poppe)

The next Access Europe User Group meeting will be on Wednesday 7 Aug 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome Pete Poppe who is very well known to Access World Forum members as MajP In this session, Pete will...

www.access-programmers.co.uk

So using the item genie as the basis I would do something like this. (Need to add the 1.1.1 autonumbering)

I would envision a lot of the same features as Item Genie.
1. If you click on a Bill item it would appear on the right and you can edit it.
2. Click on a node and you can add a bill item or a sub header at that location
3. Drag drop and bulk movement of entire branches
4. You can expand and collapse a branch to work on it.

5. Formatting and colors are dynamic. The code determines the node level and then formats appropriately based on values in your tables.
6. You can add as many sub levels as you want or limit (through code) how many sub levels.

Two Most Important Things
1. Most importantly you can now normalize your data, which it is not now.
I have a seperate table for bill items and a seperate table for headers.
2. This organization and order can be easily sent to a report because I store what I call the Treeview Sort order. It is the order that you see here. It is dynamically updated as you drag and drop and move items.

So my tables are now properly normalized

Bill Items

Headers

The additional fields are used to maintain the sort order if you move things around and to do the auto numbering 1.1.1....

 

[Edgar_]

Here's an adaptation of a WIP of my Tree Grid using IE7 in the good old web browser control. It only requires a reference to MS Internet Controls and MS HTML. It has a tabular layout.

For it to resemble the rest of what you want, it would require a few extra columns in the data array, extra CSS and some VBA interactions, possibly, I'm not sure of the whole extent of your requirement.

 

[MajP]

So here is with incorporated Auto Paragraph Numbering.

None of those paragraphs numbers are manually entered. As you add or move items the numbering is automatic.

Although Comments can go in the header table, for me to make this work I probably pull into a seperate child table like BillItems. I think after reading this more a comment can come at any level in the Hierarchy, and likely have different rules than a Header. I assume a comment cannot be nested under another comment. Currently my formatting for comments is wrong because I assume comments can only appear at a certain level. So I have to work on that there are several ways to address.

 

[dalski]

Thanks @Edgar, really helpful input.

1. Use autonumber fields for primary keys, you have tables without it.

As there are so few items in HeaderTypesT & UnitsT & they will not change to minimize storage I avoided Autonumber & believe this is a rare exception.

2. Add a sort column for order control

Cannot believe I did not think of this. I have spent 12 hours a day; over a week trying to recursively loop through multi-levels... Different queries, redesigning db...

1. Increase the Sort value by 1 for all rows where Sort >= 20 AND Sort < 23 (current sort).
2. Set Backfill's Sort to 20...

Awesome.

5. ... For a real tree view in Access, consider embedding a Web Browser control and rendering a custom tree using HTML and JavaScript. This gives you full control over the structure and appearance, including collapsible rows. In HTML, collapsing and expanding rows is all about setting rows' display style to none to hide them. JavaScript would be required for that.

Thanks was not even aware of that.

6. If you need a printable version and don’t want to create an Access report, exporting to Excel is a quick alternative.

I'll be exporting to Excel 90% of the time. I am praying that autofit to row height exports from Access to Excel; which I do not think it does as I have found that autofit row height (in Access) only works when exported to printer.

Here's an adaptation of a WIP of my Tree Grid using IE7 in the good old web browser control. It only requires a reference to MS Internet Controls and MS HTML. It has a tabular layout.
View attachment 119571

For it to resemble the rest of what you want, it would require a few extra columns in the data array, extra CSS and some VBA interactions, possibly, I'm not sure of the whole extent of your requirement.

This is cool, but where is the HTML, CSS and Javascript, is it pulling from a live webpage? I have only just looked at it & am in a rush to post my thanks back to both of you for your help.

___________________________________________________________________________________________________________________

I think you will be very interested in my presentation here starting at 21 minutes

Thanks Pete, you'll notice a spike in viewing numbers recently - that is me! Also thank you for the .pdf. I can honestly say I have studied it continually since Pat's post. Obviously a lot to take in, especially for a novice like me so my head is spinning. I need a columnar format & concerned that the height of the text of BillItems (255 char max) will pose design challenges.

Two Most Important Things
I have a seperate table for bill items and a seperate table for headers.

Thanks, I thought BillItems may need it's own table.

2. I store what I call the Treeview Sort order. It is the order that you see here. It is dynamically updated as you drag and drop and move items.

Yeah that's what has me most interested atm. Trying to figure out how you're sorting multi-levels (the video & material is well explained [thank you]; just a lot to take in for a novice).

The additional fields are used to maintain the sort order if you move things around and to do the auto numbering 1.1.1....

This sounds exactly like what I've been lost on for a week or so. Recursively jumping in & out of child items & maintaining the nr. I made a bit of progress last night & tried to finish on a positive. I have created separate fields for each level (as Pat's recommendation). Is that what you have done (can't see to the right if you are insinuating more of many individual levels) or if you are concatenating a level and another level which is what I think you're doing, then splitting...

None of those paragraphs numbers are manually entered. As you add or move items the numbering is automatic.

This is beyond awesome; that's exactly what I'm after. User's time is not wasted with trivial numbering.

Although Comments can go in the header table, for me to make this work I probably pull into a seperate child table like BillItems. I think after reading this more a comment can come at any level in the Hierarchy, and likely have different rules than a Header. I assume a comment cannot be nested under another comment. Currently my formatting for comments is wrong because I assume comments can only appear at a certain level. So I have to work on that there are several ways to address.

A comment can be nested under another comment & often this will happen. I was planning to just have comments above all BillItems; behaving exactly like a header lv01-06 so you're dead right. But I think your way will be better; allows the user to put a comment wherever they desire. But if it makes your life hell it is not the end of the world.