Total Cost of components parts in 2 databases

[EnFEngraving]

Hi all,

very new to Access and just trying to use it to set up a simply Stock Inventory system for myself.

Basically I have a table called 'Product Parts' and another table called 'Products' .

in 'Products' I want to add items as components from the 'Product Parts' table and get a Total cost price of the Component parts added together.

I've set the products table up with a 'Look up' on the Component Fields and assigned them to the elements in the Product Parts table.

I now have no idea how to get the Cost prices of the components and product the Total Sum which would show up on my Form/table.

I have no idea what to look for to help me with my problem, Query or Coding?





Ive attached a couple of screenshots which will show my problem just for complete clarity.

John

[Micron]

none of that sounds right. first, ditch the table lookup field.
the evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
About calculated table fields - http://allenbrowne.com/casu-14.html

One part has several components and there is a components table and a parts table?
tblParts - any fields that relate to only the part
tblComponents - all fields related to components only, regardless of what part they might go into
tblPartComponent - junction having tblParts.PartID and tblComponent.CompID as foreign keys

Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.com...on-part-i.html
and/or
http://holowczak.com/database-normalization/

[June7]

Multiple fields for product items is not a normalized data structure. You are limited to 2 items. What if someday need 3 or more?

Conventional approach is to have a related dependent table for the components. Each component would be a record. Then use aggregate function Sum() to total the component records.

Saving calculated data is usually unnecessary and even dangerous - calculated value can get 'out of sync' with raw data. Saving also requires code - macro or VBA.

If you stick with multiple fields, then calculation would be: Nz(field1,0) + Nz(field2,0)

This is really basic Access functionality. Have you studied an introductory tutorial book?

[orange]

I agree with Micron and June ---your basic structure is not Normalized.
You should review some Relational Database concepts and principles.
See the Database Planning and Design link in my signature for more info.
I recommend you work through 1 or 2 of the tutorials from RogersAccessLibrary mentioned in that link.
You will learn and experience the concepts if you spend 30-45 minutes and work through the tutorials.

If you have assembled Components that can contain Parts and you want to record information about the "assembly", then the following diagram (at last the Parts/Final Product side) may be helpful.



It was designed for a poster who had a number of individual Parts. He sold Final products to Customers. In his case some Final Products were individual Parts, and some were Assemblies made up of multiple Parts. Also, some Parts were sourced from external suppliers.

Good luck with your project.