Need to select top 5 rows

I want to set up a select to get the sum of a value but I only want to sum up 
the first 5 rows.

Example:

select sum(x)
from tableA
where **ROWCOUNT <= 5
order by y

Is this possible in VBA/Access?? Example? Thanks
0
Utf
1/23/2008 11:22:01 PM
access 16762 articles. 3 followers. Follow

7 Replies
824 Views

Similar Articles

[PageSpeed] 54

Try this ---
select TOP 5 sum(x)
from tableA
order by y;

-- 
KARL DEWEY
Build a little - Test a little


"AJ" wrote:

> I want to set up a select to get the sum of a value but I only want to sum up 
> the first 5 rows.
> 
> Example:
> 
> select sum(x)
> from tableA
> where **ROWCOUNT <= 5
> order by y
> 
> Is this possible in VBA/Access?? Example? Thanks
0
Utf
1/23/2008 11:28:00 PM
Thanks Karl - If I do that I get the error "you tried to execute a query that 
does not include the specified expression 'Y' as part of an aggregate 
function. Any ideas?

"KARL DEWEY" wrote:

> Try this ---
> select TOP 5 sum(x)
> from tableA
> order by y;
> 
> -- 
> KARL DEWEY
> Build a little - Test a little
> 
> 
> "AJ" wrote:
> 
> > I want to set up a select to get the sum of a value but I only want to sum up 
> > the first 5 rows.
> > 
> > Example:
> > 
> > select sum(x)
> > from tableA
> > where **ROWCOUNT <= 5
> > order by y
> > 
> > Is this possible in VBA/Access?? Example? Thanks
0
Utf
1/23/2008 11:53:10 PM
Try this ---
select TOP 5 sum(x), y
from tableA
order by y;

-- 
KARL DEWEY
Build a little - Test a little


"AJ" wrote:

> Thanks Karl - If I do that I get the error "you tried to execute a query that 
> does not include the specified expression 'Y' as part of an aggregate 
> function. Any ideas?
> 
> "KARL DEWEY" wrote:
> 
> > Try this ---
> > select TOP 5 sum(x)
> > from tableA
> > order by y;
> > 
> > -- 
> > KARL DEWEY
> > Build a little - Test a little
> > 
> > 
> > "AJ" wrote:
> > 
> > > I want to set up a select to get the sum of a value but I only want to sum up 
> > > the first 5 rows.
> > > 
> > > Example:
> > > 
> > > select sum(x)
> > > from tableA
> > > where **ROWCOUNT <= 5
> > > order by y
> > > 
> > > Is this possible in VBA/Access?? Example? Thanks
0
Utf
1/24/2008 12:26:07 AM
Damn....no same error.

"KARL DEWEY" wrote:

> Try this ---
> select TOP 5 sum(x), y
> from tableA
> order by y;
> 
> -- 
> KARL DEWEY
> Build a little - Test a little
> 
> 
> "AJ" wrote:
> 
> > Thanks Karl - If I do that I get the error "you tried to execute a query that 
> > does not include the specified expression 'Y' as part of an aggregate 
> > function. Any ideas?
> > 
> > "KARL DEWEY" wrote:
> > 
> > > Try this ---
> > > select TOP 5 sum(x)
> > > from tableA
> > > order by y;
> > > 
> > > -- 
> > > KARL DEWEY
> > > Build a little - Test a little
> > > 
> > > 
> > > "AJ" wrote:
> > > 
> > > > I want to set up a select to get the sum of a value but I only want to sum up 
> > > > the first 5 rows.
> > > > 
> > > > Example:
> > > > 
> > > > select sum(x)
> > > > from tableA
> > > > where **ROWCOUNT <= 5
> > > > order by y
> > > > 
> > > > Is this possible in VBA/Access?? Example? Thanks
0
Utf
1/24/2008 12:40:00 AM
"AJ" <AJ@discussions.microsoft.com> wrote in message
news:3EE145C4-2A1C-4DD9-A113-007FE8EB3D3F@microsoft.com...
> Damn....no same error.

Sum(x) without any GROUP BY will yield only one record.
Which Y do you expect to see with that total?

GROUP BY y.
Or perhaps try First(y)

(It also helps to explicitly alias aggregated fields.)

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

0
Allen
1/24/2008 1:52:15 AM
Hello Allen: I understand that sum will only return one record and I want to 
only sum the first five rows, based on column Y.
Can this be done?

"Allen Browne" wrote:

> "AJ" <AJ@discussions.microsoft.com> wrote in message
> news:3EE145C4-2A1C-4DD9-A113-007FE8EB3D3F@microsoft.com...
> > Damn....no same error.
> 
> Sum(x) without any GROUP BY will yield only one record.
> Which Y do you expect to see with that total?
> 
> GROUP BY y.
> Or perhaps try First(y)
> 
> (It also helps to explicitly alias aggregated fields.)
> 
> -- 
> Allen Browne - Microsoft MVP.  Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
> 
> 
0
Utf
1/24/2008 2:04:02 AM
Perhaps something like this:

SELECT Sum(x) AS SumOfX
FROM Table1
WHERE Table1.ID IN
  (SELECT TOP 5 ID
  FROM Table1 AS Dupe
  ORDER BY Dupe.y, Dupe.ID);

If subqueries are new, see:
    http://allenbrowne.com/subquery-01.html

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"AJ" <AJ@discussions.microsoft.com> wrote in message
news:378EDF24-84F8-4507-974C-32BC27843FF9@microsoft.com...
> Hello Allen: I understand that sum will only return one record and I want 
> to
> only sum the first five rows, based on column Y.
> Can this be done? 

0
Allen
1/24/2008 2:24:49 AM
Reply:

Similar Artilces:

Need help with DST
After applying DST uptades at the O.S level and on Exchange Servers, Users are reporting meeting organizer is change after the update, and strange clock icon next to the appoinments, and duplicate appoinments entries in Outlook. Does anyone know how to fix the above issue? What additional updates do I need after patching OS and Exchage server with latest DST patches? Any help is appreciated. Thanks Have you rebased appointments already? This may help: http://exchangepedia.com/blog/2007/02/dst-2007-understanding-what-needs-to-be.html -- Bharat Suneja MVP - Exchange www.zenprise.com ...

On printing out a set of rows and columns at the beginning of every sheet.
I seem to remember that there is a way of specifying a set of rows and columns that get printed out at the top and left of each sheet when printing a worksheet that gets split into multiple sheets. Don J ---------------------------------------------------------------- File (menu) | Page Setup | Sheet (tab) "Rows to repeat at top" "Columns to repeat at left" -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Don J" <dej8801@comcast.net> wrote in message I seem to remember...

Need help on this....thanks
I have an Excel worksheet which stored a survey outcome of over 1200 members. There are over 30 questions in the survey and the first column captured the member ID. The problem is 3 of the survey questions that allow multiple answers, instead the answer for those three questions are captured in seperate column, the answers are stored in a single column and it makes it very difficult to analysis those result. As a result I created addittional columns depending on the total number of the answers available for that question and assign the result manually to that column. It is very time consuming ...

5.5 to 2003 Exchange
Hi All, Do I need to stop any services on my Exch 5.5 before I run the migration setup? How about the incoming email messages? I read the white paper from Microsoft but does not mention regarding to this matter. Please advice Thank you On Tue, 24 Aug 2004 09:28:44 -0700, "im_tigerwoods" <im_tigerwoods@hotmail.com> wrote: >Hi All, > >Do I need to stop any services on my Exch 5.5 before I run the migration >setup? How about the incoming email messages? I read the white paper from >Microsoft but does not mention regarding to this matter. > >Please advic...

need assistance with sorting a list of numbers
I've tried to achieve this problem with formulas and lookups and if statements and conditinal formatting however was not able to achieve desired results, so asking for help since I am not good with XL marcros..if someone can start me up I can navigate myself.. There is a list.. say of numbers and there is a possiblity that numbers will be repeats like: 12345 fail 12345 fail 12345 pass 11111 fail 11111 pass 22222 pass 33333 fail 33333 fail 44444 pass I want to read say A1 (numbers) disregard the repeats and put info A2 beside it 12345 fail fail pass 11111 fail pass 22222 pass 333...

Move Public Folders from 5.5 to 5.5 Same Org
Hi All, I'm new the exchange and i need to accomplish the following: 1. Identify which public folders are "homed" on the source server. 2. Move them to another exchange 5.5 server. 3. Properly remove/decommission the source exchange server from the org. Need your expert help on what steps i should take to accomplish this. Thank you. this is good reading, it includes some links that is also good reading before doing anything http://support.microsoft.com/kb/152959/en-us /Lasse "Marcos" <Marcos@discussions.microsoft.com> wrote in message news:06FA8B72-169D...

change page setup from printing 1 business card to 2 rows of 5
the program wont let me make this simple change. all i want to do is set up the page so that it prints the card in two rows of 5 but there is no way to change it. Oh but there is, give this a try. Here's I do it with Publisher 2000. File Page Setup Special Size Business Card from the drop down menu OK Create card. Assuming you are using standard business card stock of 2 wide and 5 high; File Print Page options button Print multiple copies per sheet Custom options button Margins = .5" Gap = 0" Side Margins =...

Prevent row from being deleted
Hi all I have a form with a subform on it. The subform is displayed in Datasheet view. If a user right clicks on a row it allows for the user to insert a new record or deleted the current record. Is there a way to prevent the user from deleting the record? Thanks This is a multi-part message in MIME format. ------=_NextPart_000_013B_01C87499.8575B040 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Hi Murray,=20 Sure - set the sub-form's property 'allow deletions' (in design mode, = property sheet) to false.=20 Gordon &quo...

how to get the row and col count of a TMGrid using sendmessage?
I have a 3rd party application which uses TMGrid, I want to get the row and col count of this TMGrid using SendMessage. The Parent Control is a TTabSheet. Anyone know how to do it? I tried the LVM_ message, but failed. Hi, try to find out what messages this control can handle, e.g. with Spy++. Just give it a try,... Regards Kerem -- ----------------------- Beste Gr�sse / Best regards / Votre bien devoue Kerem G�mr�kc� Microsoft Live Space: http://kerem-g.spaces.live.com/ Latest Open-Source Projects: http://entwicklung.junetz.de ----------------------- "This reply is provided as ...

Selecting evenly spaced cells
Hello, I have data set up such that I need to sum the value of a certain cell and all its offsets i.e. I have data in B2, B12, B22, B32, B42 and I want to sum up only these values, however I don't want to explicitly state each cell. For my other summation I want to sum up B3, B13, B23, B33, B43 and etc. What would this group suggest be the most elegant way in doing so Let me try to further explain My worksheet has this sort of setup Summary A's - Summed Up Value B's - Summed Up Value C's - Summed Up Value Issue 1 A - 5 B - 4 C - 2 Issue 2 A - 10 B - 5 C - 9 Issue 3 A...

Outlook Toolbar #5
Hi, Added the advanced toolbar. Exit Outlook. Relaunch Outlook, Advanced Toolbar disappear. I am unable to customise the look and feel of Outlook. Please kindly help. Thanks, Ash If all toolbar customization don't stick you might want to do a hard reset of the toolbar by renaming outcmd.dat to outcmd.old -- Roady [MVP] www.sparnaaij.net Microsoft Office and Microsoft Office related News Also Outlook FAQ, How To's, Downloads and more... Tip of the month: -Tips for cleaning up your mailbox Subscribe to the newsletter to receive news and tips & tricks in your mailbox! www.sp...

Does anyone use ArcServe 11.5 for Restoring
We have a problem when restoring a the exchange database with Arcserve 11.5. This works the first time 26 hours after the backup. Any idea? http://support.microsoft.com/kb/555375 -- Ed Crowley MVP - Exchange "Protecting the world from PSTs and brick backups!" "Restoring exchange database" <Restoringexchangedatabase@discussions.microsoft.com> wrote in message news:FD4AC97D-782E-4CCB-9962-95F8B089F2D6@microsoft.com... > We have a problem when restoring a the exchange database with Arcserve > 11.5. > This works the first time 26 hours after the backup. ...

HELP need index of the actual selected point
Hi, I need help. I must get the index of a the currently selectd point in the chart. I can't use the Mouseup event. It will be better to determinate the x and y value from the actual selected point but the index will also be good. Please help! thx Jens You need to use an old-style XLM command for this, as Stephen Bullen pointed out in his post 14-Jan-2001: Sub WhichPoint() Dim sPoint As String Dim iSeries As Integer, iPoint As Integer sPoint = ExecuteExcel4Macro("SELECTION()") If sPoint Like "S*P*" Then iSeries = Val(Mid$(sPoint, 2...

Vlookup in vba
Hi I have the following snippet of code: Sheets("mdata").Select Range("E1").Select ActiveCell.FormulaR1C1 = "RVU" Range("E2").Select ActiveCell.FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC[-1],rvu!R[-1]C[-4]:R[7238]C[-3],2)),0,VLOOKUP(RC[-1],rvu!R[-1]C[-4]:R[7238]C[-3],2))" Range("E2").Select Selection.AutoFill Destination:=Range("E2:E" & LastRow), Type:=xlFillDefault This fills about 5,000 rows with look up data from the rvu ws, and puts in 0 if #N/A is obtained. HOWEVER, if I was ...

Unable to report selected records in Contact and Account
Hello, I have a very strange problem. I have to make a report from the entity Contact (and Account). I have maked a report in Visual Studio 2005. When I run the report on selected records then I get a blank report-screen. When I do exact the same on an own/new entity then it works fine. Can anyone help me? Thanks in advance, Dirk ...

auto correct option under proof reading is not available to select
when i go into mail format and select proof reading I can not select any option other than auto correct select text when you type. the other options are grey and not available. i need to put on auto capital etc for emailing What version of Outlook? Was it installed as part of a suite? -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips by email: mailto:dailytips-subscribe-request@lists.outlooktips.net EMO - a weekly newsletter about Outlook and Exchange: mailto:EMO...

The need for speed
Hi, I'm am running queries to return the postcode. The New Zealand Post have supplied a data file that contains 1,829,432 distinct delivery points. Do you have any suggestions/recommendations to help speed up the performance of a search; particularly over a network. Both the data file and front end are in Access 2003. To add to the mix, some addresses have alternative street names and/or alternative suburb names and/or alternative town names. These alternatives are contained in separate tables. That is if a matching address is not found in the main table, a search is then made for ...

Conditional Formatting and selection
I have one column that has both name/address(in one row) and telephone numbers(in another row). I'd like to move the name/address info into it's own column. I've been able to do a conditional format to change the text info to a new color but I can't seem to mass select that info so I can move it all at once. Any ideas would be appreciated. if either the names or the telepphe numbers are all in even rows insert a helper column and enter =mod(row(),2) and copy down to the end of the data use autofilter on this column and select 0 select all and paste somewhere change the aut...

find&select button,help please?
Hi everybody, i'm using excel 2007,when i press the find&select button it dosen't sow me the cell i'm looking for,it has been disabled,although when i look the rows one by one i can find it,how can i able the find&select. i'd appreciate if u'd let me know,cuz i'm in trouble here ...

Select which file to use in Integration Manager
I would like to be able to select the file that is to be imported with Integration Manager at the time the integration is run. Is that possible? Or can it only be set up for one file. Thanks for any help. Bob You can use vbscript to prompt you for the file name. You could use IM to use the standard Graphical UI to open file etc but that can get complicated... HS "Bob Harrison" <bobh@tds.net> wrote in message news:1139499714.660048.85750@g43g2000cwa.googlegroups.com... >I would like to be able to select the file that is to be imported with > Integration Manager at th...

Rebuild Exchange 5.5 Server
Just need a little feedback. Here's the scoop. I have a three site environment. The main site is in San Francisco, there are VPN tunnels from SF to our site in England and New York. It is purley an Exchange 5.5 org, one exchange server in each site. All external mail is delivered to the Los Angeles site, then delievered to the remote sites via a Site to Site and an X 400 connector. Here is the problem, OWA is being flaky as hell in our main site and I can't find out why. The system reboots occasionally on it's own, OWA just dies inexplicabbly. that sort of thing. I would l...

VBA code needing alteration
I am a novice with VBA and require alteration to this code, if possible What i have now is the first 3 rows copying across. What i would lik is to have selected cells in the row copy. ie: a1, a2 and a4. Is thi possible?? Thank you Private Sub Commandbutton1_click() CopyData Range("E90:E100"), "MODEL" CopyData Range("E105:E113"), "UV CURING" CopyData Range("E118:E134"), "TRANSFORMER" End Sub Private Sub CopyData(rngC As Range, Target As String) Dim rng As Range, cell As Range Dim rng1 As Range, rng2 As Range Dim rng3 As Range Dim nrow...

Need top record for each contract number
Hi everyone. I'm using A02 on XP. I have a query with: SELECT [Enrollment Table].[Plan #], [Enrollment Table].[Date Received], [Enrollment Table].New FROM [Enrollment Table] WHERE ((([Enrollment Table].New)=Yes)) ORDER BY [Enrollment Table].[Plan #], [Enrollment Table].[Date Received] DESC WITH OWNERACCESS OPTION; This pulls all the 'New' Enrollment records. There could be 560 records for one contract and only 2 for another. I need to pull only the most recent [DateReceived] for each [PlanNum]. Can't figure it out. Tried Max, tried Top but sure I'm doing somethi...

Matching an image file to a drop down box selection
Is there a method to display an image file in a cell (or cell range) conditional on the selection from a drop down box? For instance, to select a colour from the list, and have the colour displayed in the column adjacent (as in colour swatches from a paint store, or to select a car model, and to have an image of it displayed adjacent to the selection). Thanks, Ed JE McGimpsey shows a way at: http://www.mcgimpsey.com/excel/lookuppics.html Ed Crawford wrote: > > Is there a method to display an image file in a cell (or cell range) > conditional on the selection from a drop d...

Parameter or if Null select all?
I am trying to make a report that asks for a Branch Name. If the user does not enter a branch name, I then want it to default to showing all branches. How do I go about doing this? I think you want your criteria looking like this; Like Nz([Enter Branch Name], "*") this way it will return * if left blank, resulting in all branches. That NZ function simply means to use the 2nd option if the first is null. I would create a form with a combo box (cboBranchName) for users to select the Branch Name. Then use the command button wizard to create a button that opens your report. ...