not DMAX or MAX

[Guest]

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

tblMileageExpens
MileageExpenseID (PK
MileageTypeID (FK
RateI
StartMile
EndMile
MileageDat

tblMileageRat
RateID (PK
MileageTypeID (FK
Rat
EffDate ' effective dat

tblMileageTyp
MileageTypeID (PK
TypeDes

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 there

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

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

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

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 appreciate

TI

rp

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 dat

 

[Brian Camire]

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.

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 there:

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

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.

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.

hi everyone,

i'd
like to provide enought support info. here's the layout of the db - i've
got 3 tables:

MileageExpenseID (PK)
MileageTypeID (FK)
RateID
StartMiles
EndMiles
MileageDate
RateID (PK)
MileageTypeID (FK)
Rate
EffDate ' effective date
MileageTypeID (PK)
TypeDesc

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) 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 in
"cannot combine aggregate functions" errors source
for RateID on the Expense form, i get circular reference errors comparing
MileageDate to EffDate until EffDate is no longer less than Mileage date
(but i don't know how to do this) select which
rate to use - i want it automated to select the rate that applies to the
time frame of the mileage date

 

[Guest]

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 work

thanks again!!

rp

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

Sorry, that first query should have been

SELEC
tblMileageExpense.MileageExpenseID
Max(tblMileageRate.EffDate) AS [EffDate
FRO
tblMileageExpens
INNER JOI
tblMileageRat
O
tblMileageExpense.MileageTypeID = tblMileageRate.MileageTypeI
WHER
tblMileageRate.EffDate <= tblMileageExpense.MileageDat
GROUP B
tblMileageExpense.MileageExpenseI

I forgot the GROUP BY

Brian

two queries together, how to use TOP function in a query, doing th
calculation in the query as opposed to in a control on the report (cool!)"sub queries" returned no results, I doubted that as a solution any longer
I guess sub queries is not the proper terexecute a query that does not include the specified expression as part of a
aggregate function or grouping. Possible cause: *You did not enter a
aggregate function in the TRANSFORM statement." The Access message box sai
"You tried execute a query that does not include the specified expressio
'MileageExpenseID' as part of an aggregatee function. OK Help

I couldn't figure out what was wrong so I tried your TOP query and i worked
Thanks again for your help
rp
----- Brian Camire wrote: ----
One way might be to try two queries -- one (say, named "Query1") tha

identifies the mileage rate effective date for each expense, an whose SQ
looks something like this

SELEC

tblMileageExpense.MileageExpenseID
Max(tblMileageRate.EffDate) AS [EffDate
FRO
tblMileageExpens
INNER JOI
tblMileageRat
O
tblMileageExpense.MileageTypeID = tblMileageRate.MileageTypeI
WHER
tblMileageRate.EffDate <= tblMileageExpense.MileageDat

and another that uses the first query to look up the related mileag

rat
and calculate the expense amount

SELEC

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
FRO
((tblMileageTyp
INNER JOI
tblMileageExpens
O
tblMileageType.MileageTypeID = tblMileageExpense.MileageTypeID
INNER JOI
Query
O
tblMileageExpense.MileageExpenseID = Query1.MileageExpenseID
INNER JOI
tblMileageRat
O
tblMileageExpense.MileageTypeID = tblMileageRate.MileageTypeI
AN
Query1.EffDate = tblMileageRate.EffDat

Another approach for identifying the mileage rate might be to use

SELEC
TOP correlated scalar subquery, which for starters might loo something lik
this

SELEC

tblMileageExpense.MileageExpenseID
(SELECT TOP
tblMileageRate.Rat
FRO
tblMileageRat
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.

hi everyone,

i'd
like to provide enought support info. here's the layout of the db - i've
got 3 tables:

MileageExpenseID (PK)
MileageTypeID (FK)
RateID
StartMiles
EndMiles
MileageDate
RateID (PK)
MileageTypeID (FK)
Rate
EffDate ' effective date
MileageTypeID (PK)
TypeDesc

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) 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 in
"cannot combine aggregate functions" errors source
for RateID on the Expense form, i get circular reference errors comparing
MileageDate to EffDate until EffDate is no longer less than Mileage date
(but i don't know how to do this) select which
rate to use - i want it automated to select the rate that applies to the
time frame of the mileage date