Budget Effective Dates

[naridshank]

I have set up a table of job monthly budgets. When a budget is changed,a new entry is created and ofcourse the a new effective date is there. Key to remember here...two separate records.

I am looking for an easy query solution to determine which budget to compare to [monthly total expenses] for a variance report.

I have achieved written a simlar query before in order to determine tenure for employee's who have quit and been rehired, but is was ugly. I am just wondering if there isn't a function out there I do not know of that knows to eliminate a dated recoord of there is a more suitable record elsewhere in the table.

 

[Chris RR]

Don't know if this is pretty or not. Let's say that you have a form that contains the date for your variance report, and a button to kick off the report itself.

Couldn't you write a query that would only find the current budget records, as of that report date (using group by to get the max record, where the date is less than or equal to the form date?) Then, use the results of that query, not your entire budget table, when you create the query for your variance report.

This won't work if each item has to be compared by date...darn...but if it's the budget as of the EOM, it might be OK.