formula error

[Guest]

I am having trouble converting quantities to gallons. The problem begins
with each product is packed one of three ways: 750 * 12, 750* 6, and 375*12.
The sales quantities appear as 1.08 (1 cs 1 btl) or 1.17 (1 cs 1btl). If I
multiply the quantities by 2.38 ([qtyshipped]*2.38), the gallons are
incorrect because it does not take into account the pk size. Does anyone know
how I can fix this?

Thanks,

Travis

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

What do the numbers mean? Gallons, liters, pints, ounces?

I'll assume the cases are wine & the measure is milliliters.

Volume (ml) Item Count
=========== ==========
750 12
750 6
375 12


To get the gallon per bottle:

(ml volume / 1000) * 0.2642

per case:

(ml volume / 1000) * 0.2642 * item count

Therefore, you need to know how many bottles are in each case, which
means the table holding descriptions of the cases should have a case
type:

Volume (ml) Item Count Case Type
=========== ========== =========
750 12 1
750 6 2
375 12 3

In the query you need to determine the case type & apply a calculation
dependent on the case type.

(Volume / 1000) * 0.2642 * ItemCount * QtyShipped

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQkNuUIechKqOuFEgEQKDoQCfQvq4ZfPReq3ojL0oeS5ZS9Eohb0AniL9
gjQAT+dZRz/8dQm9wde/bvl5
=LzZC
-----END PGP SIGNATURE-----