Using OR relationship in Query from one field to two fields

[snip6969]

I have a table PurchaseOrder that has ItemNo, QtyOrdered, PurchaseUOM and CostUOM. I have a another table called Conversions. I am trying to retrieve the appropriate conversion from the table.

For eg.

PurchaseOrder Table
ItemNo = Q1111
QtyOrdered = 60
PurchaseUOM = DRUM
CostUOM = GALLONS

Conversions Table
FromUOM = GALLONS
Operator = *
Factor = 30
ToUOM = DRUM

*** This means to get from GALLONS to DRUM multiply GALLONS by 30.

As you can see I only have it in the database from GALLONS to DRUM not the other way around. I need to create a query that shows me the conversion based on CostUOM matching either the FromUOM or ToUOM. Please note that one item could have multiple Conversions with different UOMs. So the Conversion Table UOM must match the CostUOM to be a valid conversion. Thank you.

 

[TonyJollans]

Hi snip6969,

You mean you might have a GALLONS to DRUMS conversion or you might have DRUMS to GALLONS conversion (or you might have both or you might have neither?) and you want to interpret it all and do the conversion in a single SELECT?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[DPlank]

This means to get from GALLONS to DRUM multiply GALLONS by 30.

Then surely to get the reverse, you divide DRUM by 30 to get GALLONS.

I'm guessing you're using Access?

Can't you add rows to the Conversions table to allow this?

Cheers,
Dave

Probably the only Test Analyst Manager on Tek-Tips...therefore whatever it was that went wrong, I'm to blame...

animadverto vos in Abyssus!

Take a look at Forum1393!

 

[snip6969]

Yes I am using MS ACCESS 2003

TonyJollans: What your saying is exactly correct. Is it possible to get all of that in one select or any other way?

DPlank: I can work it backwards with no problem. The issue is only retrieving the appropriate conversion.

What TonyJollans wrote is dead on.

Three different scenarios

1) PurchaseUOM = FromUOM and CostUOM = ToUOM -- OR --
2) CostUOM= FromUOM and PurchaseUOM = ToUOM -- OR --
3) No Matches

I have it to the point where for one record I get three lines due to three different conversions.

FromUOM Operator Factor ToUOM
GALLONS * 30 DRUM 30 Gallons in DRUM
KG * 100 DRUM 100 KG in DRUM
SKID / 5 DRUM 5 DRUMS per SKID

The PurchaseUOM is in GALLONS and CostUOM is DRUM. Thanks.

 

[TonyJollans]

Hi snip6969,

This should return a single row (or none if there is no conversion). It only caters for operators * and /:

[blue][tt] SELECT P.ItemNo,
P.QtyOrdered,
P.PurchaseUOM,
P.CostUOM,
C.FromUOM,
C.ToUOM,
C.Operator,
C.Factor,

IIf(P.PurchaseUOM = C.FromUOM,
IIf(C.Operator="*", P.QtyOrdered*C.Factor, P.QtyOrdered/C.Factor),
IIf(C.Operator="*", P.QtyOrdered/C.Factor, P.QtyOrdered*C.Factor))
AS ConvertedQtyOrdered

FROM Conversions C,
PurchaseOrder P

WHERE (P.PurchaseUOM = C.FromUOM AND P.CostUOM = C.ToUOM )
OR (P.PurchaseUOM = C.ToUOM AND P.CostUOM = C.FromUOM);
[/tt][/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[JerryKlmns]

snip6969

Since it is Ms-Access why don 't you join these forums??
forum700, forum701, forum702, forum703, forum705, forum709 & forum181