Lookup to unrelated table

[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?

 

[Steve Schapel]

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]))))

 

[Brian Camire]

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)