Microsoft Access
--
Questions
--
Followers
Top Experts
Access Schema Relationship Design for Multi-Level Chart of Accounts
Hi All,
I am in the process of creating a chart of accounts for a process costing tool but I am having some issues accommodating the multiple levels of cost object relationships in the design of my tables in Access. To add some context, a sample of one cost object heading, "Handling", is included below:
One key area of concern at this point is that in the sample above, all "Labor" related costs have the ability to cross over multiple headers. For example, "Handling -> Labor -> Direct Payments -> Wages", would also appear in a "Freight" header account i.e. "Freight -> Labor -> Direct Payments -> Wages". This duplication may well be warranted but perhaps there is a different way of approaching this?
So far, I have tried a generic multi-tiered table approach but it seems rather cumbersome, for me at least.
I am therefore seeking some Expert ideas on how others would approach this type of design schema and how I could improve upon what I have done already to make it more efficient.
TIA,
~Tala~
I am in the process of creating a chart of accounts for a process costing tool but I am having some issues accommodating the multiple levels of cost object relationships in the design of my tables in Access. To add some context, a sample of one cost object heading, "Handling", is included below:
One key area of concern at this point is that in the sample above, all "Labor" related costs have the ability to cross over multiple headers. For example, "Handling -> Labor -> Direct Payments -> Wages", would also appear in a "Freight" header account i.e. "Freight -> Labor -> Direct Payments -> Wages". This duplication may well be warranted but perhaps there is a different way of approaching this?
So far, I have tried a generic multi-tiered table approach but it seems rather cumbersome, for me at least.
I am therefore seeking some Expert ideas on how others would approach this type of design schema and how I could improve upon what I have done already to make it more efficient.
TIA,
~Tala~
Zero AI Policy
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
ASKER CERTIFIED SOLUTION
membership
Log in or create a free account to see answer.
Signing up is free and takes 30 seconds. No credit card required.
Hi Jim,
Many thanks for your response to my question.
I reviewed the excellent article you referred me to, and although it makes a lot of sense, I think its a bit of an overkill for what I am wanting to use it for. That said, it may be worth noting that although conceptually similar, what I am doing here is NOT for a GL, it is merely to allow grouping and aggregation of certain costs that are either statically stored or calculated and updated in various tables.
Perhaps I could try assembling something similar to what you have suggested, at least for now.
To this end, I am hopeful that some of the other Experts will chime in and share their thoughts today as well.
Thanks again Jim,
~Tala~
Many thanks for your response to my question.
I reviewed the excellent article you referred me to, and although it makes a lot of sense, I think its a bit of an overkill for what I am wanting to use it for. That said, it may be worth noting that although conceptually similar, what I am doing here is NOT for a GL, it is merely to allow grouping and aggregation of certain costs that are either statically stored or calculated and updated in various tables.
Perhaps I could try assembling something similar to what you have suggested, at least for now.
To this end, I am hopeful that some of the other Experts will chime in and share their thoughts today as well.
Thanks again Jim,
~Tala~
SOLUTION
membership
Log in or create a free account to see answer.
Signing up is free and takes 30 seconds. No credit card required.
Hi Pat,
Many thanks for participating in my question.
So, you mention two interesting points in your comment that I would really appreciate you expanding further on.
The first point was your comment about "duplication" when using the intelligent account number approach. Are you referring to records or accounts here?
The second point was related to "query complexities" when using mbizup's BOM approach. Assuming you have implemented something similar previously, what are some examples of the complexities you are referring to?
~Tala~
Many thanks for participating in my question.
So, you mention two interesting points in your comment that I would really appreciate you expanding further on.
The first point was your comment about "duplication" when using the intelligent account number approach. Are you referring to records or accounts here?
The second point was related to "query complexities" when using mbizup's BOM approach. Assuming you have implemented something similar previously, what are some examples of the complexities you are referring to?
~Tala~
SOLUTION
membership
Log in or create a free account to see answer.
Signing up is free and takes 30 seconds. No credit card required.
Hi Pat,
Thanks again.
This is one of those times when I really kick myself for not learning how to use graph databases which from what I can tell, would handle this task with ease!
That said, time is not something I have a lot of so based on both yours and Jim's feedback, I am going to take a shot at using mbizup's approach to my project as I can already see other areas where I could use it.
However, I will admit that this is well outside my depth of knowledge and it is likely I would need to ask for further help.
I would therefore greatly appreciate you both allowing me to keep this question open for a couple more days while I tease out any issues I might encounter while trying to adapt to this method.
Thanks to you both.
-Tala-
Thanks again.
This is one of those times when I really kick myself for not learning how to use graph databases which from what I can tell, would handle this task with ease!
That said, time is not something I have a lot of so based on both yours and Jim's feedback, I am going to take a shot at using mbizup's approach to my project as I can already see other areas where I could use it.
However, I will admit that this is well outside my depth of knowledge and it is likely I would need to ask for further help.
I would therefore greatly appreciate you both allowing me to keep this question open for a couple more days while I tease out any issues I might encounter while trying to adapt to this method.
Thanks to you both.
-Tala-
EARN REWARDS FOR ASKING, ANSWERING, AND MORE.
Earn free swag for participating on the platform.
Hi guys,
So, I spent some time familiarizing myself with Mbizup's approach to expanding a hierarchical relationship in Access and have hit a roadblock that is absolutely driving me insane!
Using the sample db in the article as a guide, I created a test db and removed the quantity calculations from the source code that was posted.
Functionally, my db works as expected, however, I have noticed that when creating my bill of accounts, the account levels are not synchronizing correctly. See below:
In the above example, Level 3: 33-Equipment Leases should appear underneath Level 1: 32 Handling Equipment.
I have reviewed the article's sample database numerous times over but cannot seem to identify what I am doing wrong.
Could someone please review my test db and advise what I need to do to get this working properly?
TIA,
~Tala~
bom_accounts.accdb
So, I spent some time familiarizing myself with Mbizup's approach to expanding a hierarchical relationship in Access and have hit a roadblock that is absolutely driving me insane!
Using the sample db in the article as a guide, I created a test db and removed the quantity calculations from the source code that was posted.
Functionally, my db works as expected, however, I have noticed that when creating my bill of accounts, the account levels are not synchronizing correctly. See below:
In the above example, Level 3: 33-Equipment Leases should appear underneath Level 1: 32 Handling Equipment.
I have reviewed the article's sample database numerous times over but cannot seem to identify what I am doing wrong.
Could someone please review my test db and advise what I need to do to get this working properly?
TIA,
~Tala~
bom_accounts.accdb
You have the account_assembly_ID as a "3" in the data. Should be "1".
Normally when you enter a BOM like this "Flat", you specify:
parent assembly line item child element (which may or may not be an assembly itself).
so it looks like:
A 1 B
A 2 C
B 1 D
B 2 E
Result:
A
B
D
E
C
Jim.
Normally when you enter a BOM like this "Flat", you specify:
parent assembly line item child element (which may or may not be an assembly itself).
so it looks like:
A 1 B
A 2 C
B 1 D
B 2 E
Result:
A
B
D
E
C
Jim.
Hi Jim,
Thank you.
So, I did as you suggested:
and ended up with this:
It should read as:
Thanks again,
~Tala~
Thank you.
So, I did as you suggested:
and ended up with this:
It should read as:
Thanks again,
~Tala~
Get a FREE t-shirt when you ask your first question.
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
You need to enter another assembly in the table then.
So the highlighted 1 would need to be 32.
Jim.
So the highlighted 1 would need to be 32.
Jim.
I see.. :-)
Being a simple kind of gal I couldn't quite grasp the concept of it all but I think I get it now!
Results are looking good:
Jim, you mentioned earlier that there are two options available for entry:
<<Entry is handled in one of two ways:
1. One level at a time with a main/subform setup (Parent and children).
2. With a Treeview control - all levels at one time. >>
Could you please point me in the right direction of evaluating option 2 so I can choose which one I prefer?
Thanks again Jim.
~Tala~
Being a simple kind of gal I couldn't quite grasp the concept of it all but I think I get it now!
Results are looking good:
Jim, you mentioned earlier that there are two options available for entry:
<<Entry is handled in one of two ways:
1. One level at a time with a main/subform setup (Parent and children).
2. With a Treeview control - all levels at one time. >>
Could you please point me in the right direction of evaluating option 2 so I can choose which one I prefer?
Thanks again Jim.
~Tala~
SOLUTION
membership
Log in or create a free account to see answer.
Signing up is free and takes 30 seconds. No credit card required.
Jim, thanks so much for your help.
As I said earlier, I am not too familiar with BOM's or Tree View controls but that is why I am here on Experts Exchange, to learn from knowledgeable people like you :)
So, for me to get my feet wet using a Tree View to enter my chart of account structure, would you prefer that I open a new question and we can go from there?
~Tala~
As I said earlier, I am not too familiar with BOM's or Tree View controls but that is why I am here on Experts Exchange, to learn from knowledgeable people like you :)
So, for me to get my feet wet using a Tree View to enter my chart of account structure, would you prefer that I open a new question and we can go from there?
~Tala~
EARN REWARDS FOR ASKING, ANSWERING, AND MORE.
Earn free swag for participating on the platform.
New question please.
Always want to try and keep things to one question/one answer because then all the comments pertain to the question and it's easier for other members reading this in the future.
But before leaving this, I will say if you don't have a lot of programming experience, you'll find the Tree view difficult to work with. It's one of the most complex controls to work with.
Jim.
Always want to try and keep things to one question/one answer because then all the comments pertain to the question and it's easier for other members reading this in the future.
But before leaving this, I will say if you don't have a lot of programming experience, you'll find the Tree view difficult to work with. It's one of the most complex controls to work with.
Jim.
SOLUTION
membership
Log in or create a free account to see answer.
Signing up is free and takes 30 seconds. No credit card required.
Thanks to both of you for your comments.
@ Jim - Copy that. I will open a new question when I need to.
@ Pat, I am reviewing the alternative Tree View control I think you were referring me to at http://www.jkp-ads.com/Art
To this end, I am going to close this question and in the spirit of collaborative efforts, I am going to split the points (Jim - 300 / Pat - 200) between both of you. I hope neither of you object to this.
God bless
~Tala~
@ Jim - Copy that. I will open a new question when I need to.
@ Pat, I am reviewing the alternative Tree View control I think you were referring me to at http://www.jkp-ads.com/Art
To this end, I am going to close this question and in the spirit of collaborative efforts, I am going to split the points (Jim - 300 / Pat - 200) between both of you. I hope neither of you object to this.
God bless
~Tala~
Yep, that's the one. Glad you found it. Since the ActiveX controls were built to work with VB and coincidentally worked with VBA, we can no longer rely on that technology. MS killed VB quite abruptly a number of years ago and forced everyone to VB.Net. That left a big hole for Access since no longer would any developer build ActiveX controls that VBA could use. MS still recommends that customers install the 32-bit version of Office. There is nothing in the 64-bit version that is in any way better. The difference is that the register size is larger so Applications like Excel can use obscenely large workbooks that probably should be tables in Access or SQL Server anyway.
Get a FREE t-shirt when you ask your first question.
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
Microsoft Access
--
Questions
--
Followers
Top Experts
Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.