Access Relationship Question

[glassjames]

My experience is low so please be gentle.

Currently working with Access to move away from excel and flat file estimating system.
I am building a estimating program for industrial division 1500 work(mechanical) and have a relationship question. I am currently doing the following

See photo.(top row of table)

I am pretty sure the term is pass through relationships
as i 1 to many from clients to client locations to client contacts to Bids to Systems to line numbers and eventually to take off
1 to many as you can see i use a new primary key for each subsequent table and that becomes the primary and then the foreign key in the next relationship. is the correct method? It logically works but when i try to make forms with multiple subforms i can only drill down my table 2 sub-forms before it stops working properly. for example i can make a form Clients with sub-forms client locations and subform client contact. However if i try to make a form client with subform systems and subform line no. it breaks and no longer works.

I also notice you can only (using the wizard) attach two sub-forms without errors. is this common? I assume is i need a form with with three subforms i would need to add it in design mode.



Thanks all for the help and I am glad to join and hopefully become a part of the community. Thanks all. Jim

(I initially thought of adding a clientID foreign key to each subsequent table but the errors were massive with trying to make forms and subforms.)

[June7]

Almost every estimating tool returned by google search was an Excel product. Finding an Access template may be near impossible. But this might give you some guidance http://www.freedownloadscenter.com/B..._Software.html

Otherwise, need to review project to analyse if you want to provide it. Follow instructions at bottom of my post.

[glassjames]

Thanks for the reply. I currently use a excel program that I wrote which works perfectly but as excel is what it is, the files can get really heavy and slow and the estimates i sometimes do are at the upper limits of what excel is capable of. We are currently working on a 360 million dollar expansion budget for a client and excel is failing to keep up. We have a database program called Quickpen and another called Hard Dollar these are data base driven and are extremely expensive The reason these programs are not used allot is because they are very difficult to use. I am looking to personally move my estimating from Excel to Access because of the speed and history you can maintain, and obviously the cost. I will package my db tomorrow and send it you. Thanks for looking. Jim