Construction Cost Estimating

[minimal]

Hi All

I am fairly new to Access but have been experimenting with it to design a construction cost estimating package. I have previously developed the following concept in Excel, and have seen it used in third party applications, so I am hopeful I can find a solution.

My problem is this:

In order to create construction unit prices (the price for building a brick wall per m2 for example), I need to combine 'resources' to formulate a price per unit (per m2/m3/nr etc).

There are 3 areas to this:

Unit Prices
Complex Resources
Simple Resources

I want the Unit Prices to be formulated in a form, with a sub-form to show the build-up of resources in the unit price.

So Unit Prices are made up of Resources:

There are two types of resources...:

Simple Resources: Basic prices from suppliers (a simple table can hold this information) - I.e. Sand/m3 OR Cement/Kg

Complex Resources: Combinations of Simple Resources (Many-Many relationship - complex resources are related to simple resources are via a join table and shown in a query in a query) - I.e. Mortar/m3 = 0.75m3 of Sand + 20kg of Cement. Each Complex Resource combines multiple qty x price per unit of Simple Resources to formulate the price.



A Unit Price then combines both Simple Resources (from a table) and Complex Resources (from a query, which are made up of Simple Resources from the table)

Each Unit Price combines multiple qty x price per unit of BOTH Simple Resources and Complex Resources to formulate the price. I.e. it uses simple raw data + Grouped data from the same source.

This may sound a bit complicated but the above is unnecessary to create the flexibility needed when pricing.

So I have managed to develop the structure to the point that:

I have a Table for Simple Resources

I have a join table linking Simple Resources to Complex Resources


I have a Query for showing the totals for each Complex Resource

I have a form for Complex Resources which incorporates a sub-form showing the Simple Resources included in each Complex Resource



I have a UNION query which combines both the Simple and Complex Resources into one table - I now refer to both Simple and Complex Resources as 'Resources' via a new code


I have a join table linking Unit Prices to 'Resources' (via the Union Query)

I have a query showing the totals for each Unit Price



Now my problem is, I need to create a Form with a Sub Form which allows the user to enter a Qty for each 'Resource' (either Simple or Complex) to formulate the unit price. The resource might appear more than once in the calculation.

While I can create the form, the presence of both the UNION query and join tables means the data is un editable in the form. I have experimented with using a combination of two sub forms - the 'Resources' and Unit Price Join Table (editable) and the calculated fields from the Unit Price Query. The problem is the fields don't sync and they are presented as two separate data sources.



I think I need to look at my many to many relationships, but because I have combinations of combinations of data, I am struggling to make the holy grail of a user-editable Unit Price form.

So to Summaries:

>Unit Prices are made up of Complex and Simple Resources ('Resources')- i.e Qty X 'Resource' Rate = Total per Unit Price (repeat on 'Resources' to formulate the Complex Resource Price)
>Complex Resources are made up of Simple Resources - i.e Qty X Simple Resource Rate = Total per Complex Resource (repeat on Simple Resources to formulate the Complex Resource Price)
>Simple Resources are directly into a table (I.e. a price list)
>Because of the Queries, the Sub-Forms are un-editable unless the calculated information is not shown, which is not idealCCMS.zip

I have inserted a slimmed down version of the databased for your info - sorry for the messy test data...

I would gratefully appreciate any advice here!



Thanks

Alan

[minimal]

Just thought I'd mention, I can resolve the problem by using D Lookups on the form, but this makes the data loading slow even with a few records, so I would rather keep the query sources if possible.