Solved Weight Conversion

[KINGOFCHAOS17]

Hello All. I've got a simple table containing product data, one of the fields contains the weight and another contains what unit of measure has been used (either pounds or ounces), I'm trying to write a query that will convert the weight to kilograms but struggling with how to handle two different conversions within the same query and the possibility of no weight being entered. I'm thinking its going to multiple IF statements but not sure, can anyone help please?

 

[NauticalGent]

Can you give an example?

how to handle two different conversions within the same query

 

[Gasman]

Have a conversion table for unit and factor and link to that.?

 

[plog]

Do this with a new table to handle conversion:

tblKilogramConversion
kc_ID, kc_Unit, kc_KG
1, OZ, 0.0283495
1, LB, 0.453592

Then in your query you LEFT JOIN (show all from Products, just those that match in tblKilogramConversion) on [Weight Fig] to [kc_Unit] and you make a calculated field:

Kilograms: Weight*kc_KG

 

[jdraw]

You can try this where
1 lb = 16 oz and 1 Kg = 2.2 lb

SELECT Products.PartNbr
, Products.Description
, Products.Weight
, Products.[Weight Fig]
, iif(Products.[Weight Fig]="OZ",(Products.Weight/16)/2.2,(Products.Weight/2.2)) AS WtinKg
FROM Products;

 

[MajP]

tblConversion
tblConversion tblConversion

ConversionID	MeasureIn	MeasureOut	Multiplier
1​	LB	KG	0.453592​
2​	OZ	KG	0.0283495​
3​	KG	LB	2.24​

SELECT Products.PartNbr, Products.Description, Products.Weight, Products.[Weight Fig], tblConversion.MeasureOut, [Weight]*[Multiplier] AS WeightKG
FROM Products INNER JOIN tblConversion ON Products.[Weight Fig] = tblConversion.MeasureIn
WHERE (((tblConversion.MeasureOut)="KG"));

Query1 Query1

PartNbr	Description	Weight	Weight Fig	MeasureOut	WeightKG
1600-080-010	CIRCUIT BREAKER, PUSHBUTT	1​	LB	KG	0.453592​
19-290-1P4	ANTENNA	10​	LB	KG	4.53592​
AF25312	FILTER, AIR OUTER	13​	LB	KG	5.896696​
172936-001	SWITCH	5​	LB	KG	2.26796​
AMX5010-AM	SWITCH,MATRIX	16​	LB	KG	7.257472​
1600-080-020	CIRCUIT BREAKER, PUSHBUTT	1​	LB	KG	0.453592​
116307-004	FILTER, FINAL (16X20X4)	8​	LB	KG	3.628736​
AMX5130-001	USER STATION,ANALOG MATRI	5​	LB	KG	2.26796​
AMIQ-USB	INTERFACE,MODULE,USB SYST	1​	LB	KG	0.453592​
AF1086804	TEMP SENSOR	25​	OZ	KG	0.7087375​
AF1086801	TEMP SENSOR	20​	OZ	KG	0.56699​
15321-002	LAMP BASE	1​	OZ	KG	0.0283495​
AF1086806	TEMP SENSOR	45​	OZ	KG	1.2757275​
AF1086803	SENSOR TEMPERATURE	25​	OZ	KG	0.7087375​
1153260-1-1	THERMOSTAT 35 DEGREE	60​	OZ	KG	1.70097​
15320-002	LAMP BASE	1​	OZ	KG	0.0283495​
15320-001	LAMP BASE	1​	OZ	KG	0.0283495​

 

[KINGOFCHAOS17]

tblConversion
tblConversion tblConversion

ConversionID	MeasureIn	MeasureOut	Multiplier
1​	LB	KG	0.453592​
2​	OZ	KG	0.0283495​
3​	KG	LB	2.24​

SELECT Products.PartNbr, Products.Description, Products.Weight, Products.[Weight Fig], tblConversion.MeasureOut, [Weight]*[Multiplier] AS WeightKG
FROM Products INNER JOIN tblConversion ON Products.[Weight Fig] = tblConversion.MeasureIn
WHERE (((tblConversion.MeasureOut)="KG"));

Query1 Query1

PartNbr	Description	Weight	Weight Fig	MeasureOut	WeightKG
1600-080-010	CIRCUIT BREAKER, PUSHBUTT	1​	LB	KG	0.453592​
19-290-1P4	ANTENNA	10​	LB	KG	4.53592​
AF25312	FILTER, AIR OUTER	13​	LB	KG	5.896696​
172936-001	SWITCH	5​	LB	KG	2.26796​
AMX5010-AM	SWITCH,MATRIX	16​	LB	KG	7.257472​
1600-080-020	CIRCUIT BREAKER, PUSHBUTT	1​	LB	KG	0.453592​
116307-004	FILTER, FINAL (16X20X4)	8​	LB	KG	3.628736​
AMX5130-001	USER STATION,ANALOG MATRI	5​	LB	KG	2.26796​
AMIQ-USB	INTERFACE,MODULE,USB SYST	1​	LB	KG	0.453592​
AF1086804	TEMP SENSOR	25​	OZ	KG	0.7087375​
AF1086801	TEMP SENSOR	20​	OZ	KG	0.56699​
15321-002	LAMP BASE	1​	OZ	KG	0.0283495​
AF1086806	TEMP SENSOR	45​	OZ	KG	1.2757275​
AF1086803	SENSOR TEMPERATURE	25​	OZ	KG	0.7087375​
1153260-1-1	THERMOSTAT 35 DEGREE	60​	OZ	KG	1.70097​
15320-002	LAMP BASE	1​	OZ	KG	0.0283495​
15320-001	LAMP BASE	1​	OZ	KG	0.0283495​

Thank you, that worked perfectly. Much simpler than I was thinking.