getting max value

[rzaxl]

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

 

[John Spencer]

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

 

[Daryl S]

Rzaxl -

You had a Group By clause, but did not indicate what you wanted to do with
the various fields (e.g. sum, group by, etc.). I assume you only used the
Group By to get the max ch_CurrentWkNo. Instead, you just want that in your
criteria as a subquery. Try this:

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_currentWkNo) - 3 from TrainDetails);

 

[rzaxl]

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

 

[John W. Vinson]

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.