Product Costing Query (1 Viewer)

[Greyowlsl]

Hello,

So i have a table with Columns: Index, Part, Part cost, Product 1, Product 2, Product 3...

'Index' is the unique no. for each part.
'Part' has the names of part.
'Part costs' have the cost per 1 part.
'Product'# have a number of how many of that part is used in that product.

I am trying to create a query that tells me the total cost for a each product.

Is there anyway to do this... maybe with code?

Also please note that there are over 1000 different parts, and 120 different products.

Any kind of help would be appreciated.

Thank you,

Leon

 

[CJ_London]

Looks like your table is not set up correctly and probably comes from an excel environment.

should be two tables

tblParts
Index
Part
Partcost

tblProducts
Product
Partindex
Quantity

then it would simply be

 select product, part, quantity, partcost, partcost*quantity as cost
 from tblProducts inner join tblparts on tblproducts.partindex=tblparts.index

which you can easily convert to a total cost of products with multiple parts by changing to a group by query and/or set a criteria to limit which products are calculated

Note that index is a reserved word - using it as a field name will cause unexplained issues at some point

with your current setup, you will need to write something like

 select "[COLOR=red]ProductName[/COLOR]", part, [COLOR=red]product#[/COLOR] as Quantity, partcost, partcost*quantity as cost
 from mytable

in 120 different queries, one for each product - changing the bits in red for each one

I guess you could write some looping vba code to construct a query for each product - plenty of examples on this and other forums for constructing and executing sql in vba. But strongly recommend you read up on normalisation and modify your table construction - you will find it a lot easier in the long run.

 

[Greyowlsl]

Looks like your table is not set up correctly and probably comes from an excel environment.

should be two tables

tblParts
Index
Part
Partcost

tblProducts
Product
Partindex
Quantity

Hi CJ,

Thanks so much for your reply. It was as i feared.

With the tlbProducts, what would it look like, what is the p-key?

tblProducts
Product -PartIndex -Quantity
Product 1 -1 -1
Product 1 -2 -2
Product 1 -3 -3
Product 1 -4 -4
Product 1 -5 -5
Product 2 -1 -1
Product 2 -2 -2
Product 2 -3 -3
Product 2 -4 -4
Product 2 -5 -5
… … …

Thanks,
Leon

 

[CJ_London]

just use an autonumber for a primary key, although it sound like this is what index is for your parts. To do it properly, I would expect you to have three tables because you have a many to many relationship between parts and products. Note I have used my field naming convention

tblParts
PartPK - autonumberPK (currently index?)
PartDesc - text (description of part)
PartCost - currency (cost of part)

tblProducts
ProductPK - autonumber
ProductDesc - text (description of Product)

tblAllocations
AllocationPK - autonumber
ProductFK - long (links to tblProducts)
PartFK - long (links to tblParts)
Quantity - number

tables contain for example

tblParts
partPK..PartDesc..PartCost
1..........partA......$10.00
2..........partB......$5.00
3..........partZ......$15.00

tblProducts
ProductPK..ProductDesc
1..............ProductA
2..............ProductB
3..............ProductZ

tblAllocations
AllocationPK..ProductFK..PartFK..Quantity
1.................1...............2.........10
2.................1...............2.........5
3.................2...............1.........14
4.................2...............2.........3
5.................2...............3.........10

 

[Greyowlsl]

Tested it and it works. Thanks!

 

[CJ_London]

happy to help...