How to select a single record to do calculations

[ManuelLavesa]

Hello, I am trying to make calculations at a single record level. I have many products and I want to select only 6 of them, which I just found out that I can do it by their SupplierID. then I have a query that finds the amount of pcs that I have for each product. The aim is to determine the final weight of this 6 products. So to start I need to find the weight of each individual product by multiplying the number of pieces (from the query) by the weight of a single piece. Obviously each product weights differently, and at the end I need to add all the weights of the individual products to make the total weight of the 6 products. So I just don't know how to approach the problem of selecting a single record. And also I don't know what would be best to do it in a query, a form or a report. Any help will be appreciated. Thanks

[NTC]

you want to rely on queries to establish the record set - which is all the records.....and use the form/report object just for the final sum

I don't know how your data is structured so can't give precise query instructions but based on your description one would join the product list to the pieces list.

[ManuelLavesa]

Hello NTC Thank you for your help. Here is the SQL for the query that contains the product table and the query that calculates the stockInHand.
SELECT qryStockInhand.fkProductID, Product.SupplierID, Product.Description, qryStockInhand.ProductName, qryStockInhand.StockInHand, ([StockInHand]*0.12) AS Green, ([StockInHand]*0.06) AS Yelow
FROM qryStockInhand INNER JOIN Product ON qryStockInhand.fkProductID = Product.ProductID
WHERE (((Product.SupplierID)<3))
ORDER BY qryStockInhand.fkProductID;


The problem I have is how to select a single product and multiply by the weight. I tried with green: and Yellow but it is not working. Hope you can help me with this. thank again

[June7]

So different StockInHand products need multiply by different constants (0.12 and 0.06). What is rule for determining which product gets which constant?

[ManuelLavesa]

Hello June7, Each product has their own weight, there is no rule as such to determine the multiplier. I was thinking that maybe I should make a query per ProductID (6 products, 6 queries) , So that I get a single product I multiply it by its weight and then make a big query with all the 6 subqueries to make the addition. Is this the best way of doing this or can I select single records within one query. Thanks

[June7]

Where does the weight factor come from? Do you have a table that has a product weight field?

If you want to provide db, follow instructions at bottom of my post.

What are the 0.12, 0.06 constants for?

[ManuelLavesa]

Well the weight factor comes from the product weight. I did not make a field called productWeight because I only need it for 6 products out of hundreds maybe thousands, and I thought it will be a waste of space having all those null values in the database. So I thought I could do it with the query as I described above. The product weight I have it in a list.

[June7]

The list is where - a sheet of paper in a notebook? If it's not in the db somewhere it is useless.

Maybe need an expression that has conditional parameters. Something like:

[StockInHand] * Switch([fkProductID]=1,2, [fkProductID]=2,3, [fkProductID]=3,4, [fkProductID]=4,5, [fkProductID]=5,6, [fkProductID]=6,7, True,1)

Use whatever weight is appropriate in place of 2 thru 7 as well as appropriate product ID. Is fkProductID a number type field?

[ManuelLavesa]

Yes the switch statement works great... Thank you. It just never occurred to me to use this statement in a query... Is excellent... Thanks again.