retrieving maximum value from query

hi there,

I have a table of the following schema

build
---------------------------
build_id              AutoNumber
build_date          Date/Time
label                   Text
CL                     Number
----------------------------

for a simple query like
SELECT build.CL FROM build;

how can I apply a filter so that I only get the max CL value?

thanks in advanced.



0
Kok
8/13/2007 10:36:47 AM
access 16762 articles. 3 followers. Follow

1 Replies
702 Views

Similar Articles

[PageSpeed] 50

SELECT Max(CL) FROM build

If you want the details about the entry with the largest value, you could 
try:

SELECT build_id, build_date, label, CL
FROM build
WHERE CL IN (Select Max(CL) FROM build)

Recognize, of course, that the second query might return multiple rows.

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Kok Yong Lee" <lkoky@hotmail.com> wrote in message 
news:u5uFbXZ3HHA.4436@TK2MSFTNGP03.phx.gbl...
> hi there,
>
> I have a table of the following schema
>
> build
> ---------------------------
> build_id              AutoNumber
> build_date          Date/Time
> label                   Text
> CL                     Number
> ----------------------------
>
> for a simple query like
> SELECT build.CL FROM build;
>
> how can I apply a filter so that I only get the max CL value?
>
> thanks in advanced.
>
>
> 


0
Douglas
8/13/2007 10:48:59 AM
Reply:

Similar Artilces:

Unlinked Chart Treats Time Axis As Value
I have a series of charts in separate workbooks, all with a time X-axis. For distribution, I copy just the charts - not the underlying data - to a new workbook. All looks good until I come to open the new workbook, when I find that the Y-axis has migrated from outside left to inside right, and buggered up the alignment with a second chart on the same sheet. When I look at the properties of the X-axis, I find that it's no longer a time axis, but a value axis; and that, instead of the Y-axis crossing at 8/1/2004, it now crosses at 27338 - even though the X-axis is still formatted as a date. ...

How to retrieve the string from dialog box
Hi All, Does anyone know how to retrieve a string in IDC_STATIC on a dialog box? I have to print out a report with an application version at the bottom line for users to be aware of it in my application. I don't know how to retrieve the string in IDC_STATIC control on a dialog box. It would be very nice that someone let me know how to do that without calling the dialog box. TIA Hwasoo Lee Back 9 wrote: > Hi All, > > Does anyone know how to retrieve a string in IDC_STATIC on a dialog box? > I have to print out a report with an application version at the bottom line &g...

DTPicker: Incrementing Time Value
I have a form with the following DTpicker's InterviewStart (format= "mm/dd/yyyy HH:mm") InterviewEnd (format= "mm/dd/yyyy HH:mm") When the user inserts a record, the InterviewStart field updates to Now(). I would like to set the default value of InterviewEnd to be 1.5 hours from Now(). How does the VBA code look in order to accomplish this? TIA! Hi Brandon, You might try something like: Me.InterviewEnd = DateAdd("h", 1.5, Now()) or Me.InterviewEnd = DateAdd("h", 1.5, Me.InterviewStart Hope that helps... "Bran...

Limiting processing power for a specific query
I have a query (within an SSRS report) that does some very complex calculations across a large amount of data. This report takes a considerable amount of time to run (around 5 minutes). The amount of time is not the problem. The problem is that the SQL Server ends up being completely bogged down while this report runs and for a while afterwards as well. The other users (100+) that are accessing the database through my application are pretty much unable to use the system because the server's CPU usage is up around 100% until the report is finished. I was wondering if someone c...

Pasting Values
Hi, I have developed a quoting tool which creates an output sheet detailing all the info, What I would like to be able to do is take a copy of this and paste the values (and Format to a new workbook). I'm currently doing this manually and I unsure how to automate it! I have never used VB which I'm guessing is the only way of doing it, so please be gentle!! Thanks, Sam. -- sammy2x ------------------------------------------------------------------------ sammy2x's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29674 View this thread: http://www.excelf...

#VALUE! Occuring in formula result
I have a formula in cell A1: =IF(ISNA(VLOOKUP(B9,ALL!A6:E1091,5,FALSE)=TRUE),"",VLOOKUP(B9,ALL!A6:E1091,5,FALSE)) I used this formula to eliminate the #N/A that would appear if no value is found in the vlookup. The result in A1 was #N/A, but now it is blank due to ISNA formula. However, now if I add cell A1 with others cells. =SUM(A1:A10), it returns #VALUE!. The blank cell as a result of the ISNA formula is causing this cell not to add. Please help.....Thanks, Richard --- Message posted from http://www.ExcelForum.com/ Use a zero instead of "". -- Kind Reg...

retrieve an AVERAGE of a range using VLOOKUP to locate start/end d
Can I calculate the average of a range based on VLOOKUP? In other words, can I get Excel to "look up" the respective START / END dates of an investment position listed in Sheet 1 and then go and locate those 2 distinct cell values in in a different sheet (Sheet 2) utilizing VLOOKUP function and then return the calculated AVERAGE of the cells within that range back into Sheet 1? Appreciate any thoughts if doable in Excel in any way? tAKE A LOOK AT AverageIFS and see if that does what you want. If it were me, I'd probably enter the formula manually, record the resu...

Count records in a query
Hello! I have a query that runs pretty slow, and I'd like to alert the user before running the query. So I have a source query for my slow query, and I'd like to count the records in my source query and estimate the time the query will take to run. How can I do this in VBA? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201005/1 DCount("*","Your Source Query Name") This will give you the number of records in the Query "AtleDreier via AccessMonster.com" wrote: > Hello! >...

Retrieve mouse position
Hi, I am writing an app in which I need to retrieve the mouse coordinates even when it mouse is outside the main frame. How do I do it? Thanks! xg >Hi, I am writing an app in which I need to retrieve the mouse coordinates >even when it mouse is outside the main frame. How do I do it? Thanks! Have you tried calling GetCursorPos? If you want to know when the mouse is moving I think you'll need to use a hook - have a look at SetWindowsHookEx WH_MOUSE. Dave IN addition to David's answer, you have not stated the problem you are trying to solve. For examp...

Value Entered Isn't Valid...Error
I have a cbo ShopName on my subform which is populated by ltblShop. The cbo displays the Name, but is bound by the ShopID. If the user doesn't enter a value, I want a msgbox to open confirming this and then entering "None", which is an item in my ltblShop. Admittedly, the idea of having a tbl for Shops which then has a "None" entry which isn't a shop feels wrong but seemed to be the most expedient at the time and easiest for such the novice that I am. The code for this upon entering and exiting that control seems to work fine. The box opens upon a...

Retrieving comment using range name
I have singular cells in different places referenced by their range names. Is there a way to get the comments corresponding to the referenced ranges (singular cells)? Thanks in advance, Tomek Set testrng = Range("A3:A4", "C3:C4") comment1 = testrng(1).Comment.Text .... commentn = testrng(n).Comment.Text Stefi „topola” ezt írta: > I have singular cells in different places referenced by their range > names. Is there a way to get the comments corresponding to the > referenced ranges (singular cells)? > Thanks in advance, > Tomek > > did yo...

relation ship and query
How many table can I make relation between them in Query Design view? Because this message appear to me after I add 3 table only The SQL statement could not be executed because it contains ambiguous outer joins. To force one of the joins to be performed first, create a separate query that performs the first join and then include that query in your SQL statement. (Error 3258) You can add 32 tables in a query, but since you can query other queries, in theory you could probably build as many as 992. I'm sure you'd run out of memory way before that. Your error message is being c...

vlookup for words content when there are duplicate values
Hi, Can somebody help me here. Thanks in advance. When I vlookup Material 1 to Material 2 for the Project No, I get the vlookup Project No. as 222-56 for all the duplicate Material values. How formula to use so that when there are duplicate values in Material2, I want to have vlookup searching to match Project No. containg "AA" as 1st choice ? (if no Project No. containing "AA", then looks for its content "AB" and subsequently looks for "AC" if there is no "AB"). ColumnA ColumnB ColumnC Material 2 Duplicate? Project No. 224410 duplica...

How can a value be converted to words
Is there a fromula if I have a total i.e. 123 to be converted in words i.e. One hundrd and twenty three automatically. http://support.microsoft.com/default.aspx?scid=kb;en-us;213360 or http://www.xldynamic.com/source/xld.xlFAQ0004.html tj "Azar" wrote: > Is there a fromula if I have a total i.e. 123 to be converted in words i.e. > One hundrd and twenty three automatically. ...

Retrieving messages
Help, I mistakenly restored all the defaults on Outlook (the computer did not crash) losing all the messages and address book. I have had to set up the application from scratch entering account details etc. Question is: are all the messages and contacts still held on the computer even the program needs to be set up again and, if so, how do I retrieve them. Any assistance greatly appreciated. Thanks. P89 If you made a backup of your system or My Documents, search for a file called Outlook.pst. If you have that you can import all the information. Al "peterson89" <peterson...

Expression builder in a select query
I want to determine the qualifying % of commission based on Margin $ as follows <74.99 0.00% >75 and <149.99 8% >150 and <249.99 10% >25 and <349.99 12% >400 14% My expression below do not produce correct results.I need help. COMM_RATE: IIf([Net_Margin]<74.99,"0.00",IIf([Net_Margin]<149.99,"8.00",IIf([Net_Margin]<224.99,"10.00",IIf([Net_Margin]<399.99,"12.00",IIf([Net_Margin]>400,"14.00",0))))) COMM_RATE: IIf([Net_Margin]<74.99,"0.00" ,IIf([Net_Margin]<149.99,"...

How to increase the maximum account length value?
I want to add a sample company TWO into my Great Plains, however; I found that the maximum account length value is "8", and the length of segments are 4-2-2. Therefore I cannot add TWO into GP. In order to add TWO, the segments must be 3-4-2. Is there anyway to increase the maximum account length? Thanks! You would need Reformatter from Crporate Renaissance Group "Kane" <Kane@discussions.microsoft.com> wrote in message news:C0BDBDEC-67FD-4C61-8924-3ABF998E2821@microsoft.com... >I want to add a sample company TWO into my Great Plains, however; I found >...

Retrieving lost messages after Synchronization in Outlook Express
Setup for my hotmail messages to be downloaded to Outlook Express. When tried to connect to see an old message, didn't realize that it was setup to auto synch. My messages were deleted from hotmail, and are now lost on Outlook Express after synching. Is there anyway to retrieve the lost messages from Outlook Express? I really needed some of the items in there, and had downloaded them so they wouldn't be lost. Thanks! ...

How can a calculated value be displayed wiyhin a text string
I am trying to display calculated value in a text string. The value could change as the variables change. the use of the Ampersand "&" ="all " & (2+3) & " people" will show as all 5 people If you want the number to be in a given format check the text() funciton in help. "Furrukh" wrote: > I am trying to display calculated value in a text string. The value could > change as the variables change. You don't give any details, but perhaps something like ="The sum of the first 10 values is " & SUM(A1:A10) &a...

hiding values below/above a threshold
What I have is: FldA FldB FldC FldD FldE FldF cust1 1 1 1 1 1 6 cust2 1 2 1 1 1 1 cust3 1 1 1 1 1 1 cust4 1 1 1 4 1 1 cust5 1 1 1 1 1 1 cust6 1 7 1 1 1 3 What I need is more of an exception report so the user does not have to look to hard to extract the numbers that are important - any value larger than 1. ...

Retrieve
Hi how do i retrieve all the postings i have posted in this group. sometimes forget of where i posted my i will be glad to get to know how to pull out my own questions cheers Click on your name to view the profile...Then click 'Recent posts by this user' http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.excel.misc&p=1&tid=467a932d-65b6-4bb1-98e1-25def53a971a&mid=467a932d-65b6-4bb1-98e1-25def53a971a -- Jacob (MVP - Excel) "Amin" wrote: > Hi how ...

CSocket.Connect() Port Value
All: What is the best way to determine the "Host Port" value in the CSocket.Connect() function? The examples use the value 700 or greater. Can there be a conflict if I just use the value 700? -- Regards; Bruce Kingsley The correct port value to use is the port number of the port you are trying to connect to. That is determined by your host. If you are writing the host, unless you are implementing a standard protocol, it should not be < 1024. It must not be hardwired unless you have a registered port number (which you must get from IANA, the Internet Assigned Number Autho...

Query multiple records
Table/Fields Students/ Student ID, First name, last name.... Tests/ Student ID, Test Name, Test Score, Test Date. I need to be able to find students based on their record of tests. For example I need to find each student who got above a certain score on 3 or more tests consecutively and within a certain time period. In one to many relationships I have no idea how to find the one by querying the many related to it. I am new to access and any help would be appreciated greatly. Chaos EggHeadCafe - .NET Developer Portal of Choice http://www.eggheadcafe.com Doesn't matter. Create a q...

How can I extend maximum number of records when opening sylk file?
When opening a .slk file in Excel 2002 you can only see the first 32,767 records. I have a file that is larger than this , and therefore I cannot see all of the data. I know that you can extend the maximum number of records when opening a text file - I just need to know how to do it. Can anybody help me please? Opening a text file and a SYLK file are two different operations "under the hood". If 32,767 is the limit, there's probably not much you can do about it. Do you have control over the workbook from which the SLK file was produced? If so, can you split it into two ...

Adding Records with a Query?
I work for a small homeless shelter. I have a table called "Services" that contains records with a ServiceID, GuestID, ServiceDate, and ServiceType. The total number of records is the total number of services we have provided. Such as: ServiceID GuestID ServicesDate ServicesType 1875 4572 07/31/07 Men's Dorm 1876 9762 07/31/07 Dinner 1877 9762 07/31/07 Men's Dorm I want to find a way to go through and add a record for a "Dinner" service provided for each record where there is a "Men...