We help IT Professionals succeed at work.

Access Schema Relationship Design for Multi-Level Chart of Accounts

Tusitala
Tusitala asked
on
720 Views
Last Modified: 2017-05-04
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:

Accounts1.png  

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.  

Accounts2.png
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~
Comment
Watch Question

President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
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~
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
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~
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
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-

Author

Commented:
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:
boa2.png
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
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
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.

Author

Commented:
Hi Jim,

Thank you.

So, I did as you suggested:

boa3.png
and ended up with this:

boa4.png
It should read as:

boa5.png
Thanks again,
~Tala~
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
You need to enter another assembly in the table then.

So the highlighted 1 would need to be 32.

Jim.

Author

Commented:
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:

boa7.png
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~
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
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~
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
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.
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
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/Articles/treeview.asp.  Interestingly, I had already experienced some referencing issues with the MS version on my 64-bit OS development machine so I am very pleased that you introduced me to this.

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~
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.