UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Convert pounds to kilograms, pounds to ounces, etc.    
 
   
Ddew
post Nov 12 2010, 02:18 PM
Post#1



Posts: 8
Joined: 29-September 08



Hi - storing some inventory product weights in pounds, but need a query (preferably via a formula, but perhaps SQL is necessary) that will convert the pounds to display the weights in Kilograms. In another query, I need to see the pounds displayed in Ounces. Good ole' Excel Convert function won't work in Access Please help. Thanks in advance.
Go to the top of the page
 
Doug Steele
post Nov 12 2010, 02:24 PM
Post#2


UtterAccess VIP
Posts: 22,064
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Welcome to UtterAccess.
You need a table that contains the conversion factors (and your inventory table needs to include a Units field).
Link the conversion table to your inventory table on the Units field and multiply by the conversion factor:
SELECT Inventory.Field1, Inventory.Field2, Inventory.Quantity, Inventory.Unit, Inventory.Quantity * Nz(Conversion.ConvFactor, 1) As ConvertedQuantity, IIf(IsNull(Conversion.Unit), "NO FACTOR", Null) AS Comment
FROM Inventory LEFT JOIN Conversion
ON Inventory.Unit = Conversion.Unit
The purpose of the Comment field is to identify units that don't exist in the Conversion table.
Go to the top of the page
 
Ddew
post Nov 12 2010, 02:30 PM
Post#3



Posts: 8
Joined: 29-September 08



Thank you for such a quick response. Please give me an example of what you mean by a Units table. Unclear. <
Go to the top of the page
 
Ddew
post Nov 12 2010, 02:39 PM
Post#4



Posts: 8
Joined: 29-September 08



Rather what I mean is please give me an example of what the conversion factor unit table data might look like. Unclear what you mean there. Thanks again.
Go to the top of the page
 
Doug Steele
post Nov 12 2010, 02:42 PM
Post#5


UtterAccess VIP
Posts: 22,064
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Since 1 pound equals 0.45359237 kilograms, your table would need to include:
!--c1-->
CODE
Units  ConvFactor  ConvUnits
LB     0.45359237  KG


Since I've added that ConvUnits field, change the SQL to
SELECT Inventory.Field1, Inventory.Field2, Inventory.Quantity, Inventory.Unit, Inventory.Quantity * Nz(Conversion.ConvFactor, 1) As ConvertedQuantity, Nz(Conversion.ConvUnit, "NO FACTOR") AS ConvUnit
FROM Inventory LEFT JOIN Conversion
ON Inventory.Unit = Conversion.Unit
Go to the top of the page
 
nschoonover
post Nov 12 2010, 02:43 PM
Post#6



Posts: 551
Joined: 19-January 07
From: New Hampshire


You can get conversion factors fora wide range of data types from this neat program.
http://joshmadison.com/software/convert-for-windows/
Go to the top of the page
 
Ddew
post Nov 12 2010, 02:43 PM
Post#7



Posts: 8
Joined: 29-September 08



Gotcha! Thanks so much. It's a shame it can't be as simple as Excel. Best regards.
Go to the top of the page
 
Ddew
post Nov 12 2010, 02:44 PM
Post#8



Posts: 8
Joined: 29-September 08



Many thanks. I will check this out! Regards.
Go to the top of the page
 
rsneha
post Jan 11 2019, 06:37 AM
Post#9



Posts: 1
Joined: 11-January 19



Thanks or you can also check Unit converter to get accurate measurement calculation.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    22nd January 2019 - 09:58 AM