A builds Database

[Mah]

I have an excel workbook that performs a series of functions and want to know whether it would be easier to manage if I set up it up in an access database. I don't know whether Access can perform the calculations required.

Essentially what I have is a series of component build requirements that are then put together to make the final product. Each product always uses the same number of components and raw materials. But I need to be able to select how many of each product I want and for the database to tell me the quantity and cost involved. There is also added complexity because sometimes it is cheaper to buy components off the shelf than to buy them in so I need to be able to modify the prices of everything including the expected sale value of the product to determine whether I should buy in the components or build them from scratch.
I also want to be able to add new product lines into the database by way of a simple form.

The best way I can illustrate it here is:

Raw Material Component Product
1,2,3 --> X,Y --> A
2,3,4 --> Y,Z --> B


1,2,5 --> X,V --> C


Any visionaries out there that can make sense of my rambling. As stated this is already all set up in excel but to update it for a new product line is a nightmare.

[June7]

My vote was for Access until I got to 'There is added complexity ...'

Access is a relational database, designed to manage data and is probably not the best app for 'What If' comparison analysis you describe. Might be able to make it work but could be more frustration than its worth.

[orange]

I agree with June7 - sounds like a database application until I see the part on where

I need to be able to modify the prices of everything including the expected sale value of the product to determine whether I should buy in the components or build them from scratch

.

The database as a storage mechanism seems to follow some sort of hierarchy where a product can be made of 1 or more components and a component can be made from a series of parts. The storage aspect of your post would fit a database structure.
see http://www.databaseanswers.org/data_...ysical_bom.htm and
http://www.databaseanswers.org/data_..._warehouse.htm for high level models

Your complexity may come in the form of cost optimization algorithms or whatever to determine probability or other value on which to make decisions of Make/Build vs Buy.

You may have 2 issues in your post
-1-- data storage
-2-- cost comparison with parameters and uncertainty

Perhaps, database is the storage mechanism and Excel is the Analytical tool (if you have the analytical part already programmed in Excel).