How to create a unit cost breakdown analysis program in ms-access

[shagiz]

i want to crate a database application in ms-access that stores, updates and displays the constituents of concrete per each distinct grade and unit price of the concrete by summing the unit prices of each ingredient which also varies with concrete grade, admixture type and cement grade. here is the sample made in excel i want to create it in ms-access . thanks in advance for your constructive ideas and mentoring.

[ranman256]

you will need tables:


tMaterials (like you showed)
tLaborByTrade
tEquipment


---and the project tables which will be loaded with data from the tables above that define the project
tProjects
tProjectMaterials
tProjectEquipment
tProjectLabor


and probably these if tracking worker hours
tWorkers
tWorkerHrs

[Micron]

Why Access? Excel just might be the right tool for this. I don't agree that any db table should look like that because calculations are being made across columns and not down as they should be in db tables or queries.

[NoellaG]

Hi, I've been working in the concrete business for more then 10 years, and one of the most important tables you'll need is the formula table. Each type of concrete is made up according to a specific formula, which needs a mix of the following material types: sands/aggregates/cements/water/additives. Each category can have several materials. So you'll also need MaterialCategory and Materials table.
The formula's will need to be recalculated depending of the water content of sand (think of your heap of sand in the plant yard when it starts to rain), or if you buy a different type of product with another vendor.
So if you're going to do this in a relational database, you'll need more tables. If you don't want to go all the way, maybe Excel is indeed the way to go.

[June7]

Creating a form and/or report to show that same arrangement in Access likely won't be practical. Materials, Equipment, Labor would probably be subforms and subreports. On a form, subforms could be on a Tab control or a Navigation Form. On a report, subreports would probably be positioned vertically.

Calculating across in RDBMS record is certainly a reasonable practice. Calculating Qty*Price is common and that's basically what takes place for these 3 entities, as well as grand total aggregation of records.

If you want to maintain history of these mixes, RDBMS may be preferred as workbooks can become unmanageable when they hold a lot of data.

I think ranman's suggested schema is a good start. Equate Project with MixDesign.

[dblife]

If I understand what you're asking, do you need to make sure you're passing on the costs of the materials to the customer based on what you pay for them?
For example if you pay $100 per ton for 100 tons of aggregate from one supplier in April but then have to pay $110 dollars for the same product in May from a different supplier?
Rather than charge high prices and hope your margins cover the difference?
Across multiple different ingredients, this is definetly a great use of Access.
The first step is defining exactly what you want to do.
What is the exact outcome you need. Is it a report for the MD or is it a way to make sure you are charging enough while remaining competitive.
Can you elaborate and we can help.