not DMAX or MAX

hi everyone,

sorry for the length of this post, but before i state the problem, i'd like to provide enought support info. here's the layout of the db - i've got 3 tables:

tblMileageExpense
MileageExpenseID (PK)
MileageTypeID (FK)
RateID
StartMiles
EndMiles
MileageDate

tblMileageRate
RateID (PK)
MileageTypeID (FK)
Rate
EffDate ' effective date

tblMileageType
MileageTypeID (PK)
TypeDesc

i want a report that will list the tblMileageExpense records and calculate mileage expenses by using the most recent Rate that is not more recent than the mileage date (where the MAX(EffDate) is = the MileageDate)

here's the SQL that gets me halfway the

SELECT tblMileageExpense.MileageExpenseID, tblMileageExpense.MileageDate, tblMileageExpense.StartMiles, tblMileageExpense.EndMiles, tblMileageExpense.MileageTypeID, tblMileageType.TypeDesc, tblMileageRate.Rate, tblMileageRate.EffDate, tblMileageExpense.RateID
FROM (tblMileageType INNER JOIN tblMileageExpense ON tblMileageType.MileageTypeID = tblMileageExpense.MileageTypeID) INNER JOIN tblMileageRate ON tblMileageType.MileageTypeID = tblMileageRate.MileageTypeID
WHERE (((tblMileageExpense.MileageDate)=[tblMileageRate]![EffDate]));

this would list the same MileageExpense record for every occurance of the WHERE clause

when i've tried to use MAX in the above report query, it results in "cannot combine aggregate functions" errors

if i try to put similar (using dlookup or dmax) into the control source for RateID on the Expense form, i get circular reference errors

i'm thinking that the solution is VBA code that loops through comparing MileageDate to EffDate until EffDate is no longer less than Mileage date (but i don't know how to do this)

any and all help is appreciated

TIA

rpw

btw, i don't want to have a combo on the form for the user to select which rate to use - i want it automated to select the rate that applies to the time frame of the mileage date

One way might be to try two queries -- one (say, named "Query1") that
identifies the mileage rate effective date for each expense, and whose SQL
looks something like this:

SELECT
tblMileageExpense.MileageExpenseID,
Max(tblMileageRate.EffDate) AS [EffDate]
FROM
tblMileageExpense
INNER JOIN
tblMileageRate
ON
tblMileageExpense.MileageTypeID = tblMileageRate.MileageTypeID
WHERE
tblMileageRate.EffDate = tblMileageExpense.MileageDate

and another that uses the first query to look up the related mileage rate
and calculate the expense amount:

SELECT
tblMileageExpense.MileageExpenseID,
tblMileageExpense.MileageDate,
tblMileageExpense.StartMiles,
tblMileageExpense.EndMiles,
tblMileageExpense.MileageTypeID,
tblMileageType.TypeDesc,
tblMileageRate.Rate,
tblMileageRate.EffDate,
tblMileageExpense.RateID,
(tblMileageExpense.EndMiles - tblMileageExpense.StartMiles) *
tblMileageRate.Rate AS [ExpenseAmount]
FROM
((tblMileageType
INNER JOIN
tblMileageExpense
ON
tblMileageType.MileageTypeID = tblMileageExpense.MileageTypeID)
INNER JOIN
Query1
ON
tblMileageExpense.MileageExpenseID = Query1.MileageExpenseID)
INNER JOIN
tblMileageRate
ON
tblMileageExpense.MileageTypeID = tblMileageRate.MileageTypeID
AND
Query1.EffDate = tblMileageRate.EffDate

Another approach for identifying the mileage rate might be to use a SELECT
TOP correlated scalar subquery, which for starters might look something like
this:

SELECT
tblMileageExpense.MileageExpenseID,
(SELECT TOP 1
tblMileageRate.Rate
FROM
tblMileageRate
WHERE
tblMileageRate.MileageTypeID = tblMileageExpense.MileageTypeID
AND
tblMileageRate.EffDate = tblMileageExpense.MileageDate
ORDER BY
tblMileageRate.EffDate DESC) AS [Rate]
FROM
tblMileageExpense

If you're using Access 2000 or later, I believe you can use the "Rate"
calculated field to calculate other fields (like the expense amount) in the
same query. Otherwise, I believe you may need to either: a) create a second
query based on this one to use "Rate" to calculate another field, or, b)
repeat the entire subquery in other calculations.

"rpw" wrote in message
...
hi everyone,

sorry for the length of this post, but before i state the problem, i'd
like to provide enought support info. here's the layout of the db - i've
got 3 tables:

tblMileageExpense
MileageExpenseID (PK)
MileageTypeID (FK)
RateID
StartMiles
EndMiles
MileageDate

tblMileageRate
RateID (PK)
MileageTypeID (FK)
Rate
EffDate ' effective date

tblMileageType
MileageTypeID (PK)
TypeDesc

i want a report that will list the tblMileageExpense records and calculate
mileage expenses by using the most recent Rate that is not more recent than
the mileage date (where the MAX(EffDate) is = the MileageDate)

here's the SQL that gets me halfway the

SELECT tblMileageExpense.MileageExpenseID, tblMileageExpense.MileageDate,
tblMileageExpense.StartMiles, tblMileageExpense.EndMiles,
tblMileageExpense.MileageTypeID, tblMileageType.TypeDesc,
tblMileageRate.Rate, tblMileageRate.EffDate, tblMileageExpense.RateID
FROM (tblMileageType INNER JOIN tblMileageExpense ON
tblMileageType.MileageTypeID = tblMileageExpense.MileageTypeID) INNER JOIN
tblMileageRate ON tblMileageType.MileageTypeID =
tblMileageRate.MileageTypeID
WHERE (((tblMileageExpense.MileageDate)=[tblMileageRate]![EffDate]));

this would list the same MileageExpense record for every occurance of the
WHERE clause

when i've tried to use MAX in the above report query, it results in
"cannot combine aggregate functions" errors

if i try to put similar (using dlookup or dmax) into the control source
for RateID on the Expense form, i get circular reference errors

i'm thinking that the solution is VBA code that loops through comparing
MileageDate to EffDate until EffDate is no longer less than Mileage date
(but i don't know how to do this)

any and all help is appreciated

TIA

rpw

btw, i don't want to have a combo on the form for the user to select which
rate to use - i want it automated to select the rate that applies to the
time frame of the mileage date

Brian,

Thanks guy! This worked and I learned a few things from you: how to use two queries together, how to use TOP function in a query, doing the calculation in the query as opposed to in a control on the report (cool!).

I had thought about using two queries, but after a search on this site for "sub queries" returned no results, I doubted that as a solution any longer - I guess sub queries is not the proper term

also, fyi, the SQL that uses MAX gave me error # 3122 "You tried to execute a query that does not include the specified expression as part of an aggregate function or grouping. Possible cause: *You did not enter an aggregate function in the TRANSFORM statement." The Access message box said "You tried execute a query that does not include the specified expression 'MileageExpenseID' as part of an aggregatee function. OK Help"

I couldn't figure out what was wrong so I tried your TOP query and it worked!

Thanks again for your help.

rpw

----- Brian Camire wrote: -----

One way might be to try two queries -- one (say, named "Query1") that
identifies the mileage rate effective date for each expense, and whose SQL
looks something like this:

SELECT
tblMileageExpense.MileageExpenseID,
Max(tblMileageRate.EffDate) AS [EffDate]
FROM
tblMileageExpense
INNER JOIN
tblMileageRate
ON
tblMileageExpense.MileageTypeID = tblMileageRate.MileageTypeID
WHERE
tblMileageRate.EffDate = tblMileageExpense.MileageDate

and another that uses the first query to look up the related mileage rate
and calculate the expense amount:

SELECT
tblMileageExpense.MileageExpenseID,
tblMileageExpense.MileageDate,
tblMileageExpense.StartMiles,
tblMileageExpense.EndMiles,
tblMileageExpense.MileageTypeID,
tblMileageType.TypeDesc,
tblMileageRate.Rate,
tblMileageRate.EffDate,
tblMileageExpense.RateID,
(tblMileageExpense.EndMiles - tblMileageExpense.StartMiles) *
tblMileageRate.Rate AS [ExpenseAmount]
FROM
((tblMileageType
INNER JOIN
tblMileageExpense
ON
tblMileageType.MileageTypeID = tblMileageExpense.MileageTypeID)
INNER JOIN
Query1
ON
tblMileageExpense.MileageExpenseID = Query1.MileageExpenseID)
INNER JOIN
tblMileageRate
ON
tblMileageExpense.MileageTypeID = tblMileageRate.MileageTypeID
AND
Query1.EffDate = tblMileageRate.EffDate

Another approach for identifying the mileage rate might be to use a SELECT
TOP correlated scalar subquery, which for starters might look something like
this:

SELECT
tblMileageExpense.MileageExpenseID,
(SELECT TOP 1
tblMileageRate.Rate
FROM
tblMileageRate
WHERE
tblMileageRate.MileageTypeID = tblMileageExpense.MileageTypeID
AND
tblMileageRate.EffDate = tblMileageExpense.MileageDate
ORDER BY
tblMileageRate.EffDate DESC) AS [Rate]
FROM
tblMileageExpense

If you're using Access 2000 or later, I believe you can use the "Rate"
calculated field to calculate other fields (like the expense amount) in the
same query. Otherwise, I believe you may need to either: a) create a second
query based on this one to use "Rate" to calculate another field, or, b)
repeat the entire subquery in other calculations.

"rpw" wrote in message
...
hi everyone,
sorry for the length of this post, but before i state the problem, i'd
like to provide enought support info. here's the layout of the db - i've
got 3 tables:
tblMileageExpense
MileageExpenseID (PK)
MileageTypeID (FK)
RateID
StartMiles
EndMiles
MileageDate
tblMileageRate
RateID (PK)
MileageTypeID (FK)
Rate
EffDate ' effective date
tblMileageType
MileageTypeID (PK)
TypeDesc
i want a report that will list the tblMileageExpense records and calculate
mileage expenses by using the most recent Rate that is not more recent than
the mileage date (where the MAX(EffDate) is = the MileageDate)
here's the SQL that gets me halfway the
SELECT tblMileageExpense.MileageExpenseID, tblMileageExpense.MileageDate,
tblMileageExpense.StartMiles, tblMileageExpense.EndMiles,
tblMileageExpense.MileageTypeID, tblMileageType.TypeDesc,
tblMileageRate.Rate, tblMileageRate.EffDate, tblMileageExpense.RateID
FROM (tblMileageType INNER JOIN tblMileageExpense ON
tblMileageType.MileageTypeID = tblMileageExpense.MileageTypeID) INNER JOIN
tblMileageRate ON tblMileageType.MileageTypeID =
tblMileageRate.MileageTypeID
WHERE (((tblMileageExpense.MileageDate)=[tblMileageRate]![EffDate]));
this would list the same MileageExpense record for every occurance of the
WHERE clause
when i've tried to use MAX in the above report query, it results in
"cannot combine aggregate functions" errors
if i try to put similar (using dlookup or dmax) into the control source
for RateID on the Expense form, i get circular reference errors
i'm thinking that the solution is VBA code that loops through comparing
MileageDate to EffDate until EffDate is no longer less than Mileage date
(but i don't know how to do this)
any and all help is appreciated
TIA
rpw
btw, i don't want to have a combo on the form for the user to select which
rate to use - i want it automated to select the rate that applies to the
time frame of the mileage date

Sorry, that first query should have been:

SELECT
tblMileageExpense.MileageExpenseID,
Max(tblMileageRate.EffDate) AS [EffDate]
FROM
tblMileageExpense
INNER JOIN
tblMileageRate
ON
tblMileageExpense.MileageTypeID = tblMileageRate.MileageTypeID
WHERE
tblMileageRate.EffDate = tblMileageExpense.MileageDate
GROUP BY
tblMileageExpense.MileageExpenseID

I forgot the GROUP BY.

"rpw" wrote in message
...
Brian,

Thanks guy! This worked and I learned a few things from you: how to use
two queries together, how to use TOP function in a query, doing the
calculation in the query as opposed to in a control on the report (cool!).

I had thought about using two queries, but after a search on this site for
"sub queries" returned no results, I doubted that as a solution any longer -
I guess sub queries is not the proper term

also, fyi, the SQL that uses MAX gave me error # 3122 "You tried to
execute a query that does not include the specified expression as part of an
aggregate function or grouping. Possible cause: *You did not enter an
aggregate function in the TRANSFORM statement." The Access message box said
"You tried execute a query that does not include the specified expression
'MileageExpenseID' as part of an aggregatee function. OK Help"

I couldn't figure out what was wrong so I tried your TOP query and it
worked!

Thanks again for your help.

rpw

----- Brian Camire wrote: -----

One way might be to try two queries -- one (say, named "Query1") that
identifies the mileage rate effective date for each expense, and
whose SQL
looks something like this:

SELECT
tblMileageExpense.MileageExpenseID,
Max(tblMileageRate.EffDate) AS [EffDate]
FROM
tblMileageExpense
INNER JOIN
tblMileageRate
ON
tblMileageExpense.MileageTypeID = tblMileageRate.MileageTypeID
WHERE
tblMileageRate.EffDate = tblMileageExpense.MileageDate

and another that uses the first query to look up the related mileage
rate
and calculate the expense amount:

SELECT
tblMileageExpense.MileageExpenseID,
tblMileageExpense.MileageDate,
tblMileageExpense.StartMiles,
tblMileageExpense.EndMiles,
tblMileageExpense.MileageTypeID,
tblMileageType.TypeDesc,
tblMileageRate.Rate,
tblMileageRate.EffDate,
tblMileageExpense.RateID,
(tblMileageExpense.EndMiles - tblMileageExpense.StartMiles) *
tblMileageRate.Rate AS [ExpenseAmount]
FROM
((tblMileageType
INNER JOIN
tblMileageExpense
ON
tblMileageType.MileageTypeID = tblMileageExpense.MileageTypeID)
INNER JOIN
Query1
ON
tblMileageExpense.MileageExpenseID = Query1.MileageExpenseID)
INNER JOIN
tblMileageRate
ON
tblMileageExpense.MileageTypeID = tblMileageRate.MileageTypeID
AND
Query1.EffDate = tblMileageRate.EffDate

Another approach for identifying the mileage rate might be to use a
SELECT
TOP correlated scalar subquery, which for starters might look
something like
this:

SELECT
tblMileageExpense.MileageExpenseID,
(SELECT TOP 1
tblMileageRate.Rate
FROM
tblMileageRate
WHERE
tblMileageRate.MileageTypeID = tblMileageExpense.MileageTypeID
AND
tblMileageRate.EffDate = tblMileageExpense.MileageDate
ORDER BY
tblMileageRate.EffDate DESC) AS [Rate]
FROM
tblMileageExpense

If you're using Access 2000 or later, I believe you can use the
"Rate"
calculated field to calculate other fields (like the expense amount)
in the
same query. Otherwise, I believe you may need to either: a) create a
second
query based on this one to use "Rate" to calculate another field, or,
b)
repeat the entire subquery in other calculations.

"rpw" wrote in message
...
hi everyone,
sorry for the length of this post, but before i state the problem,
i'd
like to provide enought support info. here's the layout of the db -
i've
got 3 tables:
tblMileageExpense
MileageExpenseID (PK)
MileageTypeID (FK)
RateID
StartMiles
EndMiles
MileageDate
tblMileageRate
RateID (PK)
MileageTypeID (FK)
Rate
EffDate ' effective date
tblMileageType
MileageTypeID (PK)
TypeDesc
i want a report that will list the tblMileageExpense records and
calculate
mileage expenses by using the most recent Rate that is not more
recent than
the mileage date (where the MAX(EffDate) is = the MileageDate)
here's the SQL that gets me halfway the
SELECT tblMileageExpense.MileageExpenseID,
tblMileageExpense.MileageDate,
tblMileageExpense.StartMiles, tblMileageExpense.EndMiles,
tblMileageExpense.MileageTypeID, tblMileageType.TypeDesc,
tblMileageRate.Rate, tblMileageRate.EffDate, tblMileageExpense.RateID
FROM (tblMileageType INNER JOIN tblMileageExpense ON
tblMileageType.MileageTypeID = tblMileageExpense.MileageTypeID) INNER
JOIN
tblMileageRate ON tblMileageType.MileageTypeID =
tblMileageRate.MileageTypeID
WHERE
(((tblMileageExpense.MileageDate)=[tblMileageRate]![EffDate]));
this would list the same MileageExpense record for every occurance
of the
WHERE clause
when i've tried to use MAX in the above report query, it results
in
"cannot combine aggregate functions" errors
if i try to put similar (using dlookup or dmax) into the control
source
for RateID on the Expense form, i get circular reference errors
i'm thinking that the solution is VBA code that loops through
comparing
MileageDate to EffDate until EffDate is no longer less than Mileage
date
(but i don't know how to do this)
any and all help is appreciated
TIA
rpw
btw, i don't want to have a combo on the form for the user to
select which
rate to use - i want it automated to select the rate that applies to
the
time frame of the mileage date

Brian,

Thanks again for all of the time you put into this, they both work now.

two different methods to arrive at the same result - this is going to be interesting learning how each works

thanks again!!!

rpw

----- Brian Camire wrote: -----

Sorry, that first query should have been:

SELECT
tblMileageExpense.MileageExpenseID,
Max(tblMileageRate.EffDate) AS [EffDate]
FROM
tblMileageExpense
INNER JOIN
tblMileageRate
ON
tblMileageExpense.MileageTypeID = tblMileageRate.MileageTypeID
WHERE
tblMileageRate.EffDate = tblMileageExpense.MileageDate
GROUP BY
tblMileageExpense.MileageExpenseID

I forgot the GROUP BY.

"rpw" wrote in message
...
Brian,
Thanks guy! This worked and I learned a few things from you: how to use
two queries together, how to use TOP function in a query, doing the
calculation in the query as opposed to in a control on the report (cool!).
I had thought about using two queries, but after a search on this site for
"sub queries" returned no results, I doubted that as a solution any longer -
I guess sub queries is not the proper term
also, fyi, the SQL that uses MAX gave me error # 3122 "You tried to
execute a query that does not include the specified expression as part of an
aggregate function or grouping. Possible cause: *You did not enter an
aggregate function in the TRANSFORM statement." The Access message box said
"You tried execute a query that does not include the specified expression
'MileageExpenseID' as part of an aggregatee function. OK Help"
I couldn't figure out what was wrong so I tried your TOP query and it
worked!
Thanks again for your help.
rpw
----- Brian Camire wrote: -----
One way might be to try two queries -- one (say, named "Query1") that
identifies the mileage rate effective date for each expense, and
whose SQL
looks something like this:
SELECT
tblMileageExpense.MileageExpenseID,
Max(tblMileageRate.EffDate) AS [EffDate]
FROM
tblMileageExpense
INNER JOIN
tblMileageRate
ON
tblMileageExpense.MileageTypeID = tblMileageRate.MileageTypeID
WHERE
tblMileageRate.EffDate = tblMileageExpense.MileageDate
and another that uses the first query to look up the related mileage
rate
and calculate the expense amount:
SELECT
tblMileageExpense.MileageExpenseID,
tblMileageExpense.MileageDate,
tblMileageExpense.StartMiles,
tblMileageExpense.EndMiles,
tblMileageExpense.MileageTypeID,
tblMileageType.TypeDesc,
tblMileageRate.Rate,
tblMileageRate.EffDate,
tblMileageExpense.RateID,
(tblMileageExpense.EndMiles - tblMileageExpense.StartMiles) *
tblMileageRate.Rate AS [ExpenseAmount]
FROM
((tblMileageType
INNER JOIN
tblMileageExpense
ON
tblMileageType.MileageTypeID = tblMileageExpense.MileageTypeID)
INNER JOIN
Query1
ON
tblMileageExpense.MileageExpenseID = Query1.MileageExpenseID)
INNER JOIN
tblMileageRate
ON
tblMileageExpense.MileageTypeID = tblMileageRate.MileageTypeID
AND
Query1.EffDate = tblMileageRate.EffDate
Another approach for identifying the mileage rate might be to use a
SELECT
TOP correlated scalar subquery, which for starters might look
something like
this:
SELECT
tblMileageExpense.MileageExpenseID,
(SELECT TOP 1
tblMileageRate.Rate
FROM
tblMileageRate
WHERE
tblMileageRate.MileageTypeID = tblMileageExpense.MileageTypeID
AND
tblMileageRate.EffDate = tblMileageExpense.MileageDate
ORDER BY
tblMileageRate.EffDate DESC) AS [Rate]
FROM
tblMileageExpense
If you're using Access 2000 or later, I believe you can use the
"Rate"
calculated field to calculate other fields (like the expense amount)
in the
same query. Otherwise, I believe you may need to either: a) create a
second
query based on this one to use "Rate" to calculate another field, or,
b)
repeat the entire subquery in other calculations.
"rpw" wrote in message
...
hi everyone,
sorry for the length of this post, but before i state the problem,
i'd
like to provide enought support info. here's the layout of the db -
i've
got 3 tables:
tblMileageExpense
MileageExpenseID (PK)
MileageTypeID (FK)
RateID
StartMiles
EndMiles
MileageDate
tblMileageRate
RateID (PK)
MileageTypeID (FK)
Rate
EffDate ' effective date
tblMileageType
MileageTypeID (PK)
TypeDesc
i want a report that will list the tblMileageExpense records and
calculate
mileage expenses by using the most recent Rate that is not more
recent than
the mileage date (where the MAX(EffDate) is = the MileageDate)
here's the SQL that gets me halfway the
SELECT tblMileageExpense.MileageExpenseID,
tblMileageExpense.MileageDate,
tblMileageExpense.StartMiles, tblMileageExpense.EndMiles,
tblMileageExpense.MileageTypeID, tblMileageType.TypeDesc,
tblMileageRate.Rate, tblMileageRate.EffDate, tblMileageExpense.RateID
FROM (tblMileageType INNER JOIN tblMileageExpense ON
tblMileageType.MileageTypeID = tblMileageExpense.MileageTypeID) INNER
JOIN
tblMileageRate ON tblMileageType.MileageTypeID =
tblMileageRate.MileageTypeID
WHERE
(((tblMileageExpense.MileageDate)=[tblMileageRate]![EffDate]));
this would list the same MileageExpense record for every occurance
of the
WHERE clause
when i've tried to use MAX in the above report query, it results
in
"cannot combine aggregate functions" errors
if i try to put similar (using dlookup or dmax) into the control
source
for RateID on the Expense form, i get circular reference errors
i'm thinking that the solution is VBA code that loops through
comparing
MileageDate to EffDate until EffDate is no longer less than Mileage
date
(but i don't know how to do this)
any and all help is appreciated
TIA
rpw
btw, i don't want to have a combo on the form for the user to
select which
rate to use - i want it automated to select the rate that applies to
the
time frame of the mileage date