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
|
|||
|
|||
getting max value
SELECT TrainDetails.ch_TrainDetailsID_pk
, TrainDetails.ch_TrainDetailsID_fk , TrainDetails.ch_CurrentYrNo , TrainDetails.ch_CurrentWkNo , TrainDetails.ch_PlanYr , TrainDetails.ch_WON_WkNo , TrainDetails.ch_PPSwrksiteTSR_ref , TrainDetails.ch_NROL_PTO_ref , TrainDetails.ch_VehicleType , TrainDetails.ch_TrainID , TrainDetails.ch_RunDate from traindetails WHERE ch_currentWkNo (SELECT Max(ch_CurrentWeekNo) FROM TrainDetails) - 3 Your query was doomed to fail for a many reasons - just two are listed below First of all, any aggregate query (group by, sum, etc) requires that all fields in the SELECT clause be in the GROUP BY clause if they are not using one of the aggregate functions (First, Last, Sum, Max, Min, etc) Second you cannot compare the max value of some field to the value of the same field as you attempted. Also if the numbers are 1 to 10 then the last 3 are 8, 9, and 10. If you need 7, 8, 9, and 10 then change the operator to = or change the -3 to -4. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County rzaxl wrote: select TrainDetails.ch_TrainDetailsID_pk, TrainDetails.ch_TrainDetailsID_fk, TrainDetails.ch_CurrentYrNo, TrainDetails.ch_CurrentWkNo, TrainDetails.ch_PlanYr, TrainDetails.ch_WON_WkNo, TrainDetails.ch_PPSwrksiteTSR_ref, TrainDetails.ch_NROL_PTO_ref, TrainDetails.ch_VehicleType, TrainDetails.ch_TrainID, TrainDetails.ch_RunDate from traindetails GROUP BY TrainDetails.ch_TrainDetailsID_pk having ch_currentWkNo max(ch_currentWkNo) - 3; Hi all I'm trying to output data out by comparing the current week no (which is an integer type not a date type) with the maximum week no - 3, but it just keeps on giving me an error. Basically if there are records in the db numbered current week 1 - 10, I would like to grab the last 3 weeks which is weeks 10 - 7, but as i said the above query does not run, would appriciate and greatful some help |
#2
|
|||
|
|||
getting max value
i know this sounds like a stupid question but how do i close a thread when
it's been answed, the site does not seem to give you that option -- RzaXL "John Spencer" wrote: SELECT TrainDetails.ch_TrainDetailsID_pk , TrainDetails.ch_TrainDetailsID_fk , TrainDetails.ch_CurrentYrNo , TrainDetails.ch_CurrentWkNo , TrainDetails.ch_PlanYr , TrainDetails.ch_WON_WkNo , TrainDetails.ch_PPSwrksiteTSR_ref , TrainDetails.ch_NROL_PTO_ref , TrainDetails.ch_VehicleType , TrainDetails.ch_TrainID , TrainDetails.ch_RunDate from traindetails WHERE ch_currentWkNo (SELECT Max(ch_CurrentWeekNo) FROM TrainDetails) - 3 Your query was doomed to fail for a many reasons - just two are listed below First of all, any aggregate query (group by, sum, etc) requires that all fields in the SELECT clause be in the GROUP BY clause if they are not using one of the aggregate functions (First, Last, Sum, Max, Min, etc) Second you cannot compare the max value of some field to the value of the same field as you attempted. Also if the numbers are 1 to 10 then the last 3 are 8, 9, and 10. If you need 7, 8, 9, and 10 then change the operator to = or change the -3 to -4. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County rzaxl wrote: select TrainDetails.ch_TrainDetailsID_pk, TrainDetails.ch_TrainDetailsID_fk, TrainDetails.ch_CurrentYrNo, TrainDetails.ch_CurrentWkNo, TrainDetails.ch_PlanYr, TrainDetails.ch_WON_WkNo, TrainDetails.ch_PPSwrksiteTSR_ref, TrainDetails.ch_NROL_PTO_ref, TrainDetails.ch_VehicleType, TrainDetails.ch_TrainID, TrainDetails.ch_RunDate from traindetails GROUP BY TrainDetails.ch_TrainDetailsID_pk having ch_currentWkNo max(ch_currentWkNo) - 3; Hi all I'm trying to output data out by comparing the current week no (which is an integer type not a date type) with the maximum week no - 3, but it just keeps on giving me an error. Basically if there are records in the db numbered current week 1 - 10, I would like to grab the last 3 weeks which is weeks 10 - 7, but as i said the above query does not run, would appriciate and greatful some help . |
#3
|
|||
|
|||
getting max value
On Sat, 13 Feb 2010 19:53:01 -0800, rzaxl
wrote: i know this sounds like a stupid question but how do i close a thread when it's been answed, the site does not seem to give you that option Just stop posting. It'll fade away, or if you ignite a discussion between other folks, just let it go on and ignore it. There are no "closed" discussions on USENET. -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|