Conversions - best approach

[cricketbird]

Hi there. I'm looking for some vision help:

We sell "beads", and people can order them in almost any volume or weight unit they want - cups, drums, kg, lb, etc. For orders, cost, etc. it needs to stay in these units. However, internally, we track everything in grams, so I need ways to convert all of these units to grams for use in several reports. My main tables relevant to this are listed at the end.

On my report that displays everything in grams:

1) Items ordered by weight (lbs, kg, g) should look up a standard weight table and multiply by the appropriate conversion factor to g (1 kg = 1000 g, etc.).

2) Items ordered by volume (cups, barrels, liters, etc) should look up the item number in a table that lists the appropriate conversion factor FOR THAT ITEM. For example, 1 cup of blue beads might weigh 30 g, but 1 cup of red beads might weigh 150 g.

3) If the unit is not already in either table (perhaps we never weighed a barrel of red beads before), then it should prompt us to enter the missing value

I'm trying to figure out the best way to approach this. It seems like you need to do this with if/then/else statements, so then it would be in VBA, perhaps with DLookUp lines. Yet, it seems most DLookUps can be better done with SQL, and then I'm back to doing queries with this. I don't know how to get a query to refer to one table in some circumstances, and a different one in others.

Any thoughts?

Thank you,
CB



Tables:

PRODUCTSTBL
ProductID
ProductName
etc.

UNITTBL
UnitID
UnitName (g, kg, lbs, cups, quarts, etc.)
ConversionFactor (only for weights, not volumes)

PRODUCTWEIGHTTBL
WeightID
ProductID
UnitID
GmWeight (weight in g of 1 unit of the product)

 

[jdraw]

Because your blue beads and red beads have different weights for the same volume, you are talking Density.

So beads may be beads to you, but they are as different as Iron and Aluminum from a density point of view. So I think you are going to have to explicitly identify gm/vol for each "type of bead".

Here' wikipedia's view of Density.

Good luck -- interesting situation.

 

[cricketbird]

Thanks. The problem is not so much about defining density as how to code this in Access, where some things need a density conversion (volumes) and some don't (weights) and some have missing data. The ProductWeight table already has the density values for each product in it (gram weight per unit of volume) (or at least, most of them).

The problem is more how to switch between units of volume (density if you prefer) and weight on the fly...

Thanks

 

[jdraw]

The problem is more how to switch between units of volume (density if you prefer) and weight on the fly...

I don't think it's arbitrary

Density = weight/volume

 

[catalin.petrut]

According to you info, i see it this way: tblProducts and tblUnits. Betwen them a many 2 many relationship. Why? Becouse each product can be saled in many units and the same unit can be used on many products. On the junction table you will have the "g" field.

 

[Mihail]

Here is how I see your database (a normalized one)

tblProducts
(Primary Key) ProductID
(Indexed Unique) ProductName

tblUnits
(Primary Key) UnitID
(Indexed Unique) UnitName (g, kg, lbs, cups, quarts, etc.)

tblConversion
(Primary Key) ProductID 'Look up on tblProducts
(Primary Key) FromUnitID 'Look up on tblUnits
(Primary Key) ToUnitID 'also Look up on tblUnits
ConversionFactor

This way you ensure that you have distinct combinations between
ProductID , FromUnitID , ToUnitID
with a ConversionFactor for each one.

Also, this approach will help you to make any conversion you wish (from Cups to Drums as example )

 

[catalin.petrut]

@Mihail i dont see the need for conversions from unit to other unit then grams.

 

[Mihail]

@catalin.petrut
Dupa cum vezi, zimbesc cind spun asta.
In English:
As you can see I smile when I say that.
Maybe is not necessary, but it is possible.
Neither you, neither me and even neither cricketbird know what the future will bring.