Solved Weight Conversion (1 Viewer)

KINGOFCHAOS17

Member
Local time
Today, 16:13
Joined
Mar 20, 2020
Messages
31
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: 121

Gasman

Enthusiastic Amateur
Local time
Today, 16:13
Joined
Sep 21, 2011
Messages
14,044
Have a conversion table for unit and factor and link to that.?
 

plog

Banishment Pending
Local time
Today, 11:13
Joined
May 11, 2011
Messages
11,611
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
Local time
Today, 12:13
Joined
Jan 23, 2006
Messages
15,364
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.
Local time
Today, 12:13
Joined
May 21, 2018
Messages
8,463
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

Member
Local time
Today, 16:13
Joined
Mar 20, 2020
Messages
31
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.
 

Users who are viewing this thread

Top Bottom