If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
not DMAX or MAX
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 |
#3
|
|||
|
|||
not DMAX or MAX
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 |
#4
|
|||
|
|||
not DMAX or MAX
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 |
#5
|
|||
|
|||
not DMAX or MAX
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 |
Thread Tools | |
Display Modes | |
|
|