not DMAX or MAX

R

rpw

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
 
B

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.

rpw said:
hi everyone,

sorry for the length of this post, but before i state the problem, i'd
Click to expand...
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
Click to expand...
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,
Click to expand...
tblMileageExpense.StartMiles, tblMileageExpense.EndMiles,
tblMileageExpense.MileageTypeID, tblMileageType.TypeDesc,
tblMileageRate.Rate, tblMileageRate.EffDate, tblMileageExpense.RateID
FROM (tblMileageType INNER JOIN tblMileageExpense ON
Click to expand...
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
Click to expand...
"cannot combine aggregate functions" errors
if i try to put similar (using dlookup or dmax) into the control source
Click to expand...
for RateID on the Expense form, i get circular reference errors
i'm thinking that the solution is VBA code that loops through comparing
Click to expand...
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
Click to expand...
rate to use - i want it automated to select the rate that applies to the
time frame of the mileage date
 
B

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.

rpw said:
Brian,

Thanks guy! This worked and I learned a few things from you: how to use
Click to expand...
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
Click to expand...
"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
Click to expand...
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 said:
hi everyone,
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
Click to expand...
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
Click to expand...
 
R

rpw

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

rpw said:
Brian
Click to expand...
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
Click to expand...
identifies the mileage rate effective date for each expense, an whose SQ
looks something like this
SELEC
Click to expand...
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
Click to expand...
rat
and calculate the expense amount
SELEC
Click to expand...
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
Click to expand...
SELEC
TOP correlated scalar subquery, which for starters might loo something lik
this
SELEC
Click to expand...
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
Click to expand...
"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,
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
Click to expand...
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
Click to expand...
 
Top