Lookup to unrelated table

  • Thread starter Thread starter Peter Marshall
  • Start date Start date
P

Peter Marshall

I have a table of invoices called tblInvoices with fields InvoiceNbr and
InvoiceDate. I also have a table of surcharges called tblSurcharges with
fields EffectiveDate (primary sort) and SurchargeAmount. The 2 tables are
unrelated, ie. there is no matching field between the two. In my
query/report, I would like to lookup the applicable surcharge for an invoice
where tblInvoices!InvoiceDate >= tblSurcharges!EffectiveDate. Can this be
done?
 
Peter,

There are, no doubt, more elegant ways of doing this. But using a
couple of domain aggregate functions, I think you will get the desired
result like this... Make a query based on your tblInvoices table, and
then make two calculated fields like this...
SurchargeDate:
DMax("[EffectiveDate]","tblSurcharges","[EffectiveDate]<=" &
CLng([InvoiceDate]))
Surcharge:
Val(DLookUp("[SurchargeAmount]","tblSurcharges","[EffectiveDate]=" &
CLng(DateValue([SurchargeDate]))))
 
Another approach might be to use a calculated field with a correlated
subquery like this:

SurchargeAmount: (SELECT TOP 1 SurchargeAmount FROM tblSurcharges WHERE
tblSurcharges.EffectiveDate <= tblInvoices.InvoiceDate ORDER BY
tblSurcharges.EffectiveDate DESC)
 
Back
Top