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 
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
-- 
RzaXL
0
Utf
1/22/2010 11:04:01 AM
access.queries 6343 articles. 1 followers. Follow

4 Replies
615 Views

Similar Articles

[PageSpeed] 25

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
0
John
1/22/2010 2:52:34 PM
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);

-- 
Daryl S


"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
> -- 
> RzaXL
0
Utf
1/22/2010 3:13:02 PM
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
> .
> 
0
Utf
2/14/2010 3:53:01 AM
On Sat, 13 Feb 2010 19:53:01 -0800, rzaxl <rzaxl@discussions.microsoft.com>
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]
0
John
2/14/2010 7:17:33 AM
Reply:

Similar Artilces:

how do I get outlook to go online?
I can't seem to find an answer to my problem. I have installed Microsoft Outlook 2003 but everytime I click on it it says it cannot find a connection to the internet even though I am CONNECTED! Do I have to subscribe to something? Can someone please help. I am fast giving up on Outlook. Thanx. What kind of e-mail account are you using and how did you set it up? -- Jocelyn Fiorello MVP - Outlook *** Messages sent to my e-mail address will NOT be answered -- please reply only to the newsgroup to preserve the message thread. *** "Res" wrote: > I can't seem to ...

Getting appts to print on one page
I am using calendar assistant to print multiple overlaid calendars at once on letter size paper. All the appointments show up on the first page, except for one. This one pushes to a second monthly page duplicating the first. ie - I get two November 2009s - one with all appts except one and the other page with only the one rogue appointment -- Thanks, Alison ...

Max Limit for Values in List Boxes and Combo Boxes in Outlook forms
Folks, a user is setting up a form. She wants to have several combo boxes and list boxes with between 75 and 300 (yes, 300 -- that's not a typo!) possible values in the combo box or list box. Before she starts work on this, she would like to know if there is a limit on the number of possible values for a combo box or a multi-select list box. Is there a limit? Is it defined by the number of characters in the values box, or the number of actual values? What is the limit? Any help here would be appreciated. Thanks. David consulttech2004@hotmail.com ...

OWA
SBS 2003 SP1, Exch 2003 SP2, FBA w/ SSL. If I add/delete a calendar event via OWA, I get a 5.1.7 NDR in my Inbox (pasted below). The calendar item is added/delete, and I see it in OWA or Outlook. I haven't seen any other posts similar to this, nothing on MS support. Any clues? ========== Your message did not reach some or all of the intended recipients. Subject: Sent: 12/21/2006 8:24 AM The following recipient(s) could not be reached: System Attendant on 12/21/2006 8:24 AM The e-mail address could not be found. Perhaps the recipient moved to a different e-mail organization, or t...

Unable to get DxgkDdiRecommendFunctionalVidPn call in windows 7
I am trying to add an initial vidpn topology by implementing the DxgkDdiRecommendFunctionalVidPn call in the WDDM miniport driver. But starting from windows 7 i am unable to get the DxgkDdiRecommendFunctionalVidPn function call from dxgkrnl.After searching through the MSDN documentation i found out that the function is deprecated starting from windows 7. msdn link: http://msdn.microsoft.com/en-us/library/ee220395.aspx According to msdn "On a computer running Windows 7, the display mode manager (DMM) determines an appropriate VidPN topology to apply using VidPN history d...

HELP!!! Can't get forumla figured out!
Hello, This is my first posting to this forum, and I haven't been able to search using the right criteria (not even sure what I would search under) to find what I need so I thought I would post a thread with the question. I have an access query that gives me the results in a spreadsheet layout, with 2 worksheets on it. I need to create a forumla that will look at the data on the 2nd sheet and compare the values in 2 particular columns with the value in one cell on the 1st sheet and populate a different cell on the 1st sheet with a count of entries from the 2nd sheet. If Sheet B, Colu...

How to compare "varchar" values?
I need to compare a student's score (that s/he got it after a test) with the score requirement. The problem is the scores are either a "character" only, i.e., "2" OR combined a "character" AND the '+'/ '-' character, i.e., 2+. For example, if the require score is 2+ and the student's score is 2 then that student is not qualified. The datatype of scores is varchar. Can you please help in programming how to compare these values? Thanks a lot in advance. I appreciate it. I see two approaches. The first one is a trick, ...

IS size vs EDB file size?? I don't get it!!
I am using Standard version of Ex 5.5. I realize the 16gig limit. My question is I have added up the Kbyte in Ex Admin and it does not come close to the actual size of the priv.edb file. I have done an offline defrag also but did not get any amount of space back. I have been through the MS knowledge base and have read the articles on this subject. The kbyte size is 1.6gig and my edb file size is 14.1gig. What is using over 12gig of space??? Any ideas would be very much appreciated. I don't have 12gig of rules as the MS knowledgebase suggests. "Orlando" <Oplasencia@tp...

get/change first operation on table 'uprEmployeeCount' failed
I created a 'test' company on V10, restored the backup from our real company into this test company. I have inactivated all the employees in the test company. Unless I enter Dynamics as sa, I get the following message: A get/change first operation on table 'uprEmployeeCount' failed accessing SQL Data. Under the more info button: [Microsoft][ODBC SQL Server Drive][SQL Server] The EXECUTE permission was denied on the object 'zDP_UPR41600F_1',database 'DYNAMICS', schemo 'dbo'. I have deleted the UPR41600 table and recreated it but I still get the...

Can't get my Focus
I have a form(frmProducts) on a tab control object (TabClt) that is on another form(frmOrders). I have a search box on frmProducts that filters "On change" and then keeps its focus in that box. Private Sub txtDescSearch_Change() DoCmd.ShowAllRecords Me.Filter = "Description like '" & txtDescSearch & "*'" Me.FilterOn = True Me.txtDescSearch.SetFocus SendKeys "{F2}" End Sub It works fine when I open frmProducts by itself but when I open the tab it is on from frmOrders. When I enter the first search letter it does fi...

Use cell value as cell address
Hello everyone. I have a worksheet "Main" of 39,000 rows in which column B contains a number between 1 and 7,500. Column C is an empty column I have added. The second sheet, "Names" in the book contains a single column - A - of 7,500 names. I want to get the value from the second sheet that matches the number column of the first sheet. In other words, if "Main" cell B3 contains 3780, I want to put the value from "Names" cell A3780 into "Main" C3. How do I do this please? Richard --- Message posted from http://www.ExcelForum.com/ Hi tr...

HELP SCREENS, can't get help..
HELP SCREENS, can't get help.. 100723 1900 no pun.. can't get help on how to turn off "TILE" windows if using the term correctly. had seen setting somewhere, to stop Excel from minimizing to a narrow window when help opens (how the heck supposed to compare help to work doing when reducing window on us: cannot read window). where is the setting to stop windows / excel help from resizing to a narrow screen, when you open help. Top left of the help window autotile icon (looks like double page). It toggles between the help andexcel windows being tiled and vo...

Add value when recording a macro
Hello, I recorded a macro to create a bar chart - I checked the checkbox to add the values. Stopped recording and showed me the chart with the values. If I run the macro again, it does not display the value. What is the code I need to always display the value? -- LizW After you create the chart, turn on the macro recorder again Select the chart, and choose Chart>Chart Options On the Data Labels tab, check Values Click OK Turn off the macro recorder In the recorded code, you'll see a line similar to this: ActiveChart.ApplyDataLabels AutoText:=True, LegendKey:=False, _ Ha...

Select null values in Crystal Report
I want to select those records in a Crystal Report where the country is not filled. I can select those where it is filled, however I get zero record back when I want to select where it is not filled. I use the following selection formula in the record selection part: Length ({account.address1_country}) < 1 Any idea is appreciated. Thanks, Miklos ...

a document has gone in compatibility mode how do I get it back
I lost a couple of documents that I had saved and was changing the saved title and as I have said the document has gone into compatibility and I cant get it back. ...

How to get total "conditional sum of cells" in a column?
Hi all, I have dollar amounts in one col, and status in another. I want the sum of those dollar amounts where the corresponding status cell is empty (blank). How do I do this? Thanks for any hints, cdj Status in Column A and dollar amounts in Column B: =SUMPRODUCT((A2:A100="")*B2:B100) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk "sherifffruitfly" <sherifffruitfly@gmail.com> wrote in message news:bc08584b-1338-4b3f-8ab3-cc3e1602e581@n1g2000prb.go...

how to program recurring value
I wish every entry of a colums to be a the same function of the entry above. How do I do that? Leo Hi Leo! Assuming that you have your first formula entered Select the range covered by the common entry formula F2 Ctrl + Enter -- -- Regards Norman Harker MVP (Excel) Sydney, Australia Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. njharker@optusnet.com.au Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. "Leo Kerner" <l.kerner@sympatico.ca> wrote in message news:404294EF....

passing non-field value into vba
I need to pass the line item sequence number (lnitmseq) from the sales item detail entry window into vba. This isnt a visible field in the window, so i cannot "add field to vba" using the TOOLS --> customize toolbar window. Anyone have any idea how this can be done? Using the Modifier you can make the field visible, add it to VBA and then remove the modified window or make the field invisible again. While in the Window Layout mode, make sure that the Invisible fields are shown (Layout >> Show Invisible Fields) and then change the Visual Property, Visible = true and mov...

Hide Picklist based on value of other picklist.
On the accounts form i have made a new picklist named Partner. In the picklist Customertype i have made a new entry called partner. When someone opens the form i want the picklist named partner to be hidden. When someone selects Partner in the Customertype picklist i want the picklist partner to be visible. If an other value is selected the fields needs to stay hidden. I know i have to make some code on the onchange of the field customertype and i need to make code on the onload of the page. I have tried multiple codes but i cant get it to work. Can someone please help me out please. Hi,...

Counting Null Values in a Report
I have a report that is grouped by people, then by Reason Closed. I want to count how many entries do not have a closed date. I tried the previous posts but could not get it to work. I have a group header for each person to group their categories together. Thanks. Hi Dea, Try: =Sum(-IsNull([ClosedDateFieldName])) Note the minus sign just after the first paranthesis. IsNull() returns a -1 when the item is null, otherwise 0. So by summing up the negative of each (-1)s you are in essence counting 1 for each null item. Alternatively you could move the minus...

display the value of an unbound textbox in a field within a table
I created 3 textboxs to calculate the number of business days between 2 dates. The 3rd textbox contains the value. I now want to have this value displayed in a table field. I'm sure this is easy but I'm a complete novice and have spent far too much time on this already. Can anyone help! Fibi, The general concept of tables is that they are for storage of data in the background. They are not for display of data. That is what forms and reports are for. Therefore, the appearance of the data in the tables is not normally relevant, and the display of calculated values is im...

Can get UserProperty Names but not Values?
>From C++ using UserProp->Name I can get the name of a UserProperty, but UserProp->Value.bstrVal always returns nothing, anyone have any ideas what I could be doing wrong and has anyone ever accessed UserProperties from C++? UserPropertiesPtr UserProps = Con->UserProperties; if (UserProps->Count > 0) { tot = UserProps->Count; UserPropertyPtr UserProp; for (i=1;i < tot + 1;i++) { UserProp = UserProps->Item(i); MessageBox(NULL,UserProp->Name,"Test - Name",MB_OK); char* sText; sText = (char*)UserProp->Val...

Getting machine name when connecting to db
We have an application in Access 2000, front end/back end design,10-12 users. I want to save the dates and times they enter and exitfrom the app from their workstations in a table in the back end.How do I get the name of their workstation to save to that table? Theworkstations have names like "sales1", "sales2", "Reception", etc.Paul Fenton Add error handling as required. Place in a module and call functionwhen needed.'example by Donavon Kuhn (Donavon.Kuhn@Nextel.com)Private Const MAX_COMPUTERNAME_LENGTH As Long = 31Private Declare Function GetComputerName L...

How to only "paste values" of cells that are not "hidden"?
Hello, In my document, many columns are hidden. Say column B is hidden, and I need to copy columns A and C and paste values into another Excel document. How can I do that? When I select columns A and C, copy and paste values, the other document contains columns A,B and C, instead of just columns A and C. Thank you! Don't drag-select, control-click A and then C, for scattered-selection. Danny On Sun, 17 Jul 2005 16:33:01 -0700, Sam <Sam@discussions.microsoft.com> wrote: > Hello, > > In my document, many columns are hidden. Say column B is hidden, &g...

Bug? Multiple values in merged cells
I found that merged cells can contain multiple values. Steps to reproduce: Type 1,2,3,4 in a1:d1 type sum(a1:d1) in e1 Select a1:b1 and merge Warning : MultipleData, overwrite? Say yes to merge Select the merged a1:b1 cells Copy Select c1 PasteSpecial Formats No warning.. no overwrite. c1:d1 are now merged BUT d1 still contains a value... and the SUM of a1:d1 = 8 !! Also happens with FormatPainter etc Behaviour observed in xl97,xlXP and xl2003 Error checking will find no fault in the sheet... and you can spend ages to find out WHY your cross sums dont match! (although now that i fou...