Lookup cost from date range

[kghays]

I need a query based on 2 tables:

Table 1 Billing cost:
Fields: Eff Date CostPerHour

Table 2 WorkOrder Info:
Fields: WO# Date Issued Location Category HrsWorked


There are currently 3 entries in Table1. The CostPerHour changed 3 times
within a year.

Query:
Total Cost for each WO#'s HrsWorked based on the Cost at the time the Work
Order was issued.

 

[Jerry Whittle]

This is a tough one as you don't have a real link between the two tables.

You also only have an effective date in the record which assumes that it's
good up until the next effective date in another record.

If you had another field, something like End Date, in the Billing table, you
could do a Between statement using the Eff Date and End Date fields. You have
to be sure that there are no gaps between an End Date and an Eff Date. You
also need to know if the Date Issued data is just a date or also includes
times as that could mess up the last day.

 

[kghays]

Thank you for your suggestion. I added EffEndDt to the Billing Table, but how
do I setup the Between statement?

kghays

 

[John Spencer]

PERHAPS the following will work for you

SELECT [WO#], [Date Issued], Location, Category, HrsWorked
, (SELECT First(CostPerHour)
FROM [Billing Cost]
WHERE [Eff Date] = (SELECT Min([Eff date])
FROM [Billing Cost]
WHERE [Eff Date] > [Date Issued)) as Cost
FROM [WorkOrder Info]

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Jerry Whittle]

I recommend trying John's solution first. It looks more elegant.

If that doesn't work, try the following to see if it is anywhere close.

SELECT [WO#],
[Date Issued],
Location,
Category,
HrsWorked,
CostPerHour
FROM [WorkOrder Info], [Billing Cost]
WHERE [WorkOrder Info].[Date Issued]
Between [Billing Cost].[eff date]
And [Billing Cost].[EffEndDt] ;