Table relationship problem

[drkhelmt]

I'm not sure this is the correct place for this post, please let me know if not.

I'm trying to get these relationships to work. What I is a sales database in Access 2000. I need to get different currency conversions for the orers and the order discounts. My tables are as follows:

Sales (SalesID, ... , date,...)
Orders (orderID, saleID, amount, unitPrice, ... , currencyID)
orderDiscount (orderID, amount, unitPrice, currencyID, ... )
currencyConv (conversionID, currencyID, date, conversion)

I can change the tables if necessary. but I need a report as follows:

Report (saleID, sales: (orders.amount * (orders.unitPrice * conversion)), discount: (orderDiscount.amount * (orderDiscount.unitPrice * conversion)) ... )

Any ideas or help is greatly appreciated!

Andrew
a.k.a. Dark Helmet

"What's the matter Colonel Sandurz? Chicken?

 

[drkhelmt]

Oh, one detail I forgot to mention which complicates this- not all orders have discounts associated with it.

Thanks to anyone trying to figure this out!

Andrew
a.k.a. Dark Helmet

"What's the matter Colonel Sandurz? Chicken?

 

[PHV]

It would be easier if the currencyConv table had a beginning date and a ending date of validity.
So you get the conversion by a join on currencyID with a restriction on Sales.date Between currencyConv.DateBeg And currencyConv.DateEnd.
BUT it'd violates some Normal Form rule...
So, with your actual table design, one way to retrieve the conversion could be something like this:
SELECT
...
FROM ...,Sales S,Orders O,currencyConv V,...
WHERE ...
AND V.currencyID=O.currencyID AND V.date=
(SELECT Max(W.date) FROM currencyConv W WHERE W.currencyID=O.currencyID AND W.date<=S.date)
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244