SolvedWeight Conversion (1 Viewer)

KINGOFCHAOS17

New member
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?

Attachments

• Database.accdb
632 KB · Views: 50

NauticalGent

CopyPaster of the First Order
Can you give an example?
how to handle two different conversions within the same query

Gasman

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

plog

Banishment Pending
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

Super Moderator
Staff member
You can try this where
1 lb = 16 oz and 1 Kg = 2.2 lb
Code:
``````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

You've got your good things, and you've got mine.
tblConversion
tblConversion tblConversion

ConversionIDMeasureInMeasureOutMultiplier
1​
LBKG
0.453592​
2​
OZKG
0.0283495​
3​
KGLB
2.24​
Code:
``````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

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

KINGOFCHAOS17

New member
tblConversion
tblConversion tblConversion

ConversionIDMeasureInMeasureOutMultiplier
1​
LBKG
0.453592​
2​
OZKG
0.0283495​
3​
KGLB
2.24​
Code:
``````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

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

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

Replies
27
Views
263
Replies
6
Views
371
Replies
7
Views
471
Replies
4
Views
122
Replies
12
Views
132