Auto select Billing Cost

[kghays]

I need a query that automatically selects a work order billing cost from a
table of various billing costs. The selection depends on the date the work
order was issued. The table of billing costs contains an effective beginning
date and ending date for each cost. For example: a billing cost of $54.00
became effective 11/1/2008 and ended 1/31/2009 when a new cost of $55.50
became effective. I want the query to match the $54.00 billing cost with all
work orders dated between 11/1/2008 and 1/31/2009 and then match the $55.50
cost with all work orders dated after 1/31/2009.

 

[Jeff Boyce]

I can't quite visualize your situation, but it sounds like you could use
your table that holds the [DateWorkOrderIssued] and the table that holds the
billing cost information, and get the [BillingCost] using selection criteria
something like (untested):

= [EffectiveDate] And <= [EndingDate]

You'd need to substitute your actual fieldnames ...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Spencer]

You can't do this is in query design view, but must use SQL view to
build the query.

SELECT WorkOrder.*, BillingCosts.Cost
FROM WorkOrder INNER JOIN BillingCosts
ON WorkOrder.DateField >= BillingCosts.BeginDate
AND WorkOrder.DateField <= BillingCost.EndDate

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

 

[kghays]

John

Thank you! It worked perfect. My only problem is that my most recent
Billing cost has no "End Date" because I do not know when it may change. Is
there an easy way to incorporate that into my SQL statement?

 

[John Spencer MVP]

At least two ways to solve it that I can think of.

First way, is to set the EndDate to 1/1/2100 and then when you add a new end
date, be sure to edit the 1/1/2100 to the correct end date in the existing record.

Second way is to change the query to use the NZ statement on the End Date
field in the ON clause.

SELECT WorkOrder.*, BillingCosts.Cost
FROM WorkOrder INNER JOIN BillingCosts
ON WorkOrder.DateField >= BillingCosts.BeginDate
AND WorkOrder.DateField <= Nz(BillingCost.EndDate,#1/1/2100#)

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

 

[ken]

How about:

SELECT WorkOrder.*, BillingCosts.Cost
FROM WorkOrder INNER JOIN BillingCosts
ON WorkOrder.DateField >= BillingCosts.BeginDate
AND (WorkOrder.DateField <= BillingCost.EndDate
OR BillingCost.EndDate IS NULL);

Ken Sheridan
Stafford, England

 

[John Spencer MVP]

Ken,
That might work as well. However, I seem to recall that Access is not very
good with that structure and tends to complain of a syntax error. SQL server,
on the other hand, works well.

It could be that I am not remembering correctly or that it used to be that way
and later versions of JET SQL can handle this structure.

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

 

[kghays]

John

Thank you again. All of your suggestions have worked. But here is another
problem I'm having and I can't find the correct solution:

Using your SQL statement works, except when Work Order entry dates are the
same as the "End Date". Then the calculation uses the "Begin Date" of the
next date range period. To illustrate using my original example, $54.00 was
the billing cost for the period 11/1/2008 - 1/31/2009. The billing cost
changes to $55.50 effective 2/1/2009. But if my work order issue date is
1/31/2009, $55.50 is being used in the calculation instead of $54.00. I

 

[John Spencer MVP]

Does WorkOrder date contain just a date or is there a time component to the
WorkOrder date?

Even if WorkOrder.Date had a time component, I don't see how you could be
getting the result you are getting. 1/31/2009 is always going to be less than
2/1/2009. If WorkOrder Date had a time component then the example would not
fall into any date range and you should not see any record returned at all.

Two things to try to see what is happening.
1) Change the table and the query.
Change the table so the EndDate in one record is the same as the BeginDate in
the next record. Then change the query to the following: Note the change in
the last line of the join.

SELECT WorkOrder.*, BillingCosts.Cost
FROM WorkOrder INNER JOIN BillingCosts
ON WorkOrder.DateField >= BillingCosts.BeginDate
AND WorkOrder.DateField < Nz(BillingCost.EndDate,#1/1/2100#)


OR

2) Change the query to

SELECT WorkOrder.*, BillingCosts.Cost
FROM WorkOrder INNER JOIN BillingCosts
ON DateValue(WorkOrder.DateField) >= BillingCosts.BeginDate
AND DateValue(WorkOrder.DateField) <= Nz(BillingCost.EndDate,#1/1/2100#)

If that works then we can speculate that WorkOrder.DateField has a time
component and somehow it was affecting the results.


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