Summing the Price of Multiple Parts (Records) in Different Tables

[ban1k]

Greetings - I'm trying to get my head wrapped around this one; I may need just some general guidance, as I have light experience with Access.

I am creating a database that is meant to A) find all possible combinations of given parts/components, and then B) bundle together the price of each of those parts into one complete item with total price. So, to illustrate - I would combine the below 3 'Part' tables on the left into one 'complete item' table having just two columns/fields: 'Complete Item' & 'Complete Price'.

BasePart1	BasePart1_Price
WTL	100

Options1	Options1_Price
-ABC	'0
-123	25

Options2	Options2_Price
	'0
-Z2	25
-Z3	50

COMPLETE-ITEM (All Combinations)	COMPLETE-PRICE
WTL-ABC	???
WTL-123	???
WTL-ABC-Z2	???
WTL-123-Z2	???
WTL-ABC-Z3	???
WTL-123-Z3	???

I used the following Query to create all possible combinations ('Complete-Item') table/field:

SELECT BasePart1.BasePart1 & Options1.Options1 & Options2.Options2 AS Complete-Item INTO Complete-Item
FROM BasePart1, Options1, Options2;

I greatly appreciate any insight or direction on solving and tying in the "Complete Price" portion to match up with the given Complete items.

Thanks!

[pbaldy]

Try

SELECT BasePart1.BasePart1 & Options1.Options1 & Options2.Options2 AS Complete-Item,
BasePart1.BasePart1_Price + Options1.Options1_Price + Options2.Options2_Price AS CompletePrice INTO Complete-Item
FROM BasePart1, Options1, Options2;

[ban1k]

Hi Paul - thanks for the quick reply! When I try inserting the suggested SQL code, I get the following result:

CompleteItem
CompleteItem	CompletePrice
WTL-ABC	100
WTL-123	125
WTL-ABC-Z3	125
WTL-123-Z3	150
WTL-ABC-Z3	150
WTL-123-Z3	175

However, we would expect the result, based on the price in each part/piece table to come out to the below (it appears the complete price and product are not coming in sync).

Expected Result:

CompleteItem
CompleteItem	CompletePrice
WTL-ABC	100
WTL-123	125
WTL-ABC-Z3	150
WTL-123-Z3	175
WTL-ABC-Z3	150
WTL-123-Z3	175

Any thoughts as to how to amend this disconnect?
Thanks again!

[ban1k]

Oops! Paul - see the error of my ways here. I reentered my "sample" data incorrectly with the test.
When I corrected Z2-Z3 mixup I pasted above, the suggested code works as expected!

CompleteItem
CompleteItem	CompletePrice
WTL-ABC	100
WTL-123	125
WTL-ABC-Z2	125
WTL-123-Z2	150
WTL-ABC-Z3	150
WTL-123-Z3	175

Thanks so much for straightening me out!

[pbaldy]

Happy to help, and welcome to the site by the way!