can't get query to group

here is the sql.  

can someone help me determine why the query isn't grouping by GoupNumber?  
The GroupName is the same for the GroupNumber.  ID is unique.  

SELECT Mid([ID],3,2) AS GroupNumber, Accounts.GroupName
FROM Accounts
GROUP BY Mid([ID],3,2), Accounts.GroupName, Accounts.ID
HAVING (((Accounts.ID) Like "01*"));

Example
ID             GroupNumber               GroupName
010101          01                              red
010102          01                              red
010201          02                              blue
010301          03                              green
0
Utf
3/4/2010 2:42:11 PM
access.queries 6343 articles. 1 followers. Follow

3 Replies
790 Views

Similar Articles

[PageSpeed] 53

It is grouping by "GroupNumber", then GroupName, then ID. Since ID is unique, 
it will return every record. If you want to group by GroupNumber and 
GroupName, run this:

SELECT Mid([ID],3,2) AS GroupNumber, Accounts.GroupName
FROM Accounts
GROUP BY Mid([ID],3,2), Accounts.GroupName
HAVING (((Accounts.ID) Like "01*"));

-- 
Jerry Whittle, Microsoft Access MVP 
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"gator" wrote:

> here is the sql.  
> 
> can someone help me determine why the query isn't grouping by GoupNumber?  
> The GroupName is the same for the GroupNumber.  ID is unique.  
> 
> SELECT Mid([ID],3,2) AS GroupNumber, Accounts.GroupName
> FROM Accounts
> GROUP BY Mid([ID],3,2), Accounts.GroupName, Accounts.ID
> HAVING (((Accounts.ID) Like "01*"));
> 
> Example
> ID             GroupNumber               GroupName
> 010101          01                              red
> 010102          01                              red
> 010201          02                              blue
> 010301          03                              green
0
Utf
3/4/2010 2:51:02 PM
error:
You tried to execute a query that does not include the specified expression 
'Accounts.ID Like "01*"' as part of an aggregate function.

"Jerry Whittle" wrote:

> It is grouping by "GroupNumber", then GroupName, then ID. Since ID is unique, 
> it will return every record. If you want to group by GroupNumber and 
> GroupName, run this:
> 
> SELECT Mid([ID],3,2) AS GroupNumber, Accounts.GroupName
> FROM Accounts
> GROUP BY Mid([ID],3,2), Accounts.GroupName
> HAVING (((Accounts.ID) Like "01*"));
> 
> -- 
> Jerry Whittle, Microsoft Access MVP 
> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> 
> 
> "gator" wrote:
> 
> > here is the sql.  
> > 
> > can someone help me determine why the query isn't grouping by GoupNumber?  
> > The GroupName is the same for the GroupNumber.  ID is unique.  
> > 
> > SELECT Mid([ID],3,2) AS GroupNumber, Accounts.GroupName
> > FROM Accounts
> > GROUP BY Mid([ID],3,2), Accounts.GroupName, Accounts.ID
> > HAVING (((Accounts.ID) Like "01*"));
> > 
> > Example
> > ID             GroupNumber               GroupName
> > 010101          01                              red
> > 010102          01                              red
> > 010201          02                              blue
> > 010301          03                              green
0
Utf
3/4/2010 3:19:01 PM
SELECT Mid([ID],3,2) AS GroupNumber, Accounts.GroupName
FROM Accounts
WHERE (((Accounts.ID) Like "01*"))
GROUP BY Mid([ID],3,2), Accounts.GroupName



Vanderghast, Access MVP





"gator" <gator@discussions.microsoft.com> wrote in message 
news:67AF3D74-4A4A-4BF1-8948-94E0F4F9729D@microsoft.com...
> error:
> You tried to execute a query that does not include the specified 
> expression
> 'Accounts.ID Like "01*"' as part of an aggregate function.
>
> "Jerry Whittle" wrote:
>
>> It is grouping by "GroupNumber", then GroupName, then ID. Since ID is 
>> unique,
>> it will return every record. If you want to group by GroupNumber and
>> GroupName, run this:
>>
>> SELECT Mid([ID],3,2) AS GroupNumber, Accounts.GroupName
>> FROM Accounts
>> GROUP BY Mid([ID],3,2), Accounts.GroupName
>> HAVING (((Accounts.ID) Like "01*"));
>>
>> -- 
>> Jerry Whittle, Microsoft Access MVP
>> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
>>
>>
>> "gator" wrote:
>>
>> > here is the sql.
>> >
>> > can someone help me determine why the query isn't grouping by 
>> > GoupNumber?
>> > The GroupName is the same for the GroupNumber.  ID is unique.
>> >
>> > SELECT Mid([ID],3,2) AS GroupNumber, Accounts.GroupName
>> > FROM Accounts
>> > GROUP BY Mid([ID],3,2), Accounts.GroupName, Accounts.ID
>> > HAVING (((Accounts.ID) Like "01*"));
>> >
>> > Example
>> > ID             GroupNumber               GroupName
>> > 010101          01                              red
>> > 010102          01                              red
>> > 010201          02                              blue
>> > 010301          03                              green 

0
vanderghast
3/4/2010 3:30:21 PM
Reply:

Similar Artilces:

New storage group and moving mailboxes
I created a new storage group and have moved some mailboxes into it. Now my users that were moved into the new storage group can still logon OWA but when they try to view their mail they get a page can no be displayed instead. When they try and view mail in outlook they get and error telling them that they cannot open the item. If I move the mailbox to the storage group that it was originally in everything works fine. Thanks for any help you can give. I'm running one server with Exchange 2003 on a Win 2003 server. Folder permissions? "Steven" <Steven@discussions.m...

get background brush
Hello everyone!!!! :)))) anyone could suggest me how I could get parent's background brush whatever the parent window of the control is (a CPropertyPage, a CDialog, a CWnd...)? Maybe a way to get defaults background brush from windows theme because an user could change the current theme colors?.. Thanks a lot in advance Ciao Luigi I haven't done this myself, but this should work GetClassLong(GetParent()->GetSafeHwnd(),GCL_HBRBACKGROUND); AliR. "Luigino" <npuleio@rocketmail.com> wrote in message news:9e6f1954-f8b2-44c4-9540-9e1902766926@d2g2000...

how can i get a cell to hold a zero in front of a digit ie. 01 or.
I have tried checking the zero values box but still just takes the zero from the front which I need for the table I am trying to plan. I don't want any decimal points in front You can do it three ways 1) If the 'number' you want to end up with is fixed length, say six characters, then Format>Cells...>Custom>000000 will do it 2) Format the cells as text in advance of entry 3) Prefix with a single apostrophe ('). It will not show but will treat the entry as text -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS ...

Earn MOney ONline Get Payments Weekly Thru Checks
Hi Friends, Visit http://36lives.com/gallery/b30c25ba and make money online at home.Just refer friends and earn more.Very Low Payout Just INR.100 .Payments made daily.Join Now. Regards Jagan ...

Can an update query be used to update/produce a query?
I'd like to use an update query in a chain of nested queries. Is this possible? In this case the update query will not be updating a table, it will be changing certain values from the preceding query before proceeding with the next query in the chain. My particular need is to substitute a dummy value (-9999) for all null values in a final table which my query chain makes (the final query in the chain is a make-table query which produces the table). I have to make this substitution to several columns of the final table before exporting the table for use in some other software. I...

How to create this query. Not easy
Hi. I nedd a query that eliminates or update records and the crietria is the diferent of two columns. I have a tabel with records like this: Cod LoteNo LoteQt QtNeeded 12 A 20 15 12 B 20 15 To have this: Cod LoteNo LoteQt QtNeeded 12 A 20 15 In that example I have the an item code (12) with two Lotes, A and B but I only need to consume 15 so I would like to remover the second line because the quantaty ...

Restrict resources from getting email
I am working through another thread on setting up resources in exchange 2003. I am not ready to setup auto accept so i am doing this manually. I have the resources completed but read a good post about it being a good idea to restrict permissions to send emails to these resources by using the exchange general tab on the users account. however that does seems to work using outlook and for myself but I have add permssions, when I use a test user it seems like the meeting might book, but the user gets an email back as undeliverable for "no permission to send to sender"? Which ...

Queries
I am attempting a Theta join, but either getting records from both tables or no records. I have checked the source table and know that the desired records exist. Can anyone advise? I've never heard of a Theta Join until today. Anyway, look at this example: http://technet.microsoft.com/en-us/library/cc966426.aspx If you can't figure it out, post your SQL. And/or, post your question in the 'Queries' area. -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "DW" wrote: > I am attempting a Th...

How can you get back a lost part of a document?
After typing 3 or 4 additional pages to saved document, I don't know what I hit but I lost all the added work. Is there a way to get it back? If the document has not been closed since the error - CTRL+Z (Undo) will restore the text. If the document has been closed and the changes not saved, then there is nothing to recover. -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<...

Getting exchange email to go out to the internet from the intranet.
I should start by stating that I don't know a heck of a lot about exchange! So please, if possible, provide concise steps. I know, I'm asking for alot! And free too! We have an exchange 2000 server, and two domains... @joe.com and @john.com Everyone is running Xp with Office 2003 and use Outlook 2003. We have setup SMTP on each outlook 2003 to download from the ISP email (@joe.com or @jane.com) which moves it onto the exchange server upon the user demand. We would like email to go out to the internet rather than straight to the exchange server. For example: say we have alice@joe.com a...

Visio 2007 "Samples" missing from Getting Started and Help Menu
Hello- I just purchased Visio 2007 standard. I installed the complete program (all features) but the option for the Samples is not available from the Getting Started menu or from the Help menu. I additionally installed SP1 but Samples is still MIA. Any suggestions? Thanks, Cathy On Wed, 4 Feb 2009 18:40:36 -0800, Portland_Girl <PortlandGirl@discussions.microsoft.com> wrote: >Hello- I just purchased Visio 2007 standard. I installed the complete program >(all features) but the option for the Samples is not available from the >Getting Started menu or from the Help menu. I a...

Grouping records per sheet
I need to Group somehow records to one report in order to achieve print out of particular form that we use at work. Currently we use MS Word for this task and I want to simplify this task with MS Access, so far so good until I came to this barrier: I need records grouped so the records from the table appear in groups of 3 in one page Page1 Record 1,2,3 Page2 Record 4,5,6 PageX Record x1,x2,x3... One records has about 20 various items like: text, number, check box .... I have pasted original layout of the MS Word form for my MS Access report and inserted fields from my table...

Not show the data in the query
Hello, I transfer the excel data to database, since I want bulit it's data base. I found it that query will not show anything, if nothing filled in the other table, like gender table. My question is, is it the reason?. in the linked field ( foreign key) is empty, can we proceed entering anther records. For example we have member table( member id) lingked to called table, if there is no call for this member, I will proceed to the next member with the call data, can we proceed entering the data? Thanks in advance for any idea provided. -- H. Frank Situmorang Use an outer join Det...

Sorting and Grouping Help Needed!!
I have a report that is grouped by a fieldname called "Group_Name". It is listing the Group Names by alphabetical order. However, I want the grouping to be sorted by my own choice. We have a meeting agenda that has group names that are not in alphabetical order. How do I program the reports to group by: "W2K3", then "BlackBerry", then etc.. Thanks Ben You'd have to have another table that indicates the desired sort order, join that new table to your existing query and sort by the appropriate field from that new table. -- Doug Steele, Microsoft Ac...

Excel 2007 query
Question: In Excel 2007, when clicking on the: 1) =91Data=92 Ribbon/Tab=85 and choosing.. 2) =91From Access=92 and selecting any Access mdb, the database will be opened in =91exclusive=92 mode and will be locked for all other users on the network! Why??? When recording these steps, I see that the default system query uses =93Mode=3DShare Deny Write=94. Any workaround? In Excel versions, prior to 2007, the default query connected to the mdb in =91readonly=92 mode, which was fine. Thanks. mj ...

Paging a query
I am trying to page my data and am using ROW_NUMBER() to accomplish this. I want to be able to pass the starting and ending row numbers, as well as the order by column. I can change the following query with parameters. ************************************** With PagedResults AS ( SELECT EmployeeId, FirstName, MiddleName, LastName, JobTitle, ROW_NUMBER() OVER (ORDER BY LastName, FirstName) AS ResultSetRowNumber FROM HumanResources.vEmployee ) SELECT * FROM PagedResults WHERE ResultSetRowNumber > 40 AND ResultSetRowNumber <= 50 *****************************************...

Query 90 expiration
I would like to run a qry that will show me when my insurance policyies expire. Some expire in 30 days others in 90. Table: Customers Fields: CustomerName ContactName PolicyNumber PolicyExpirationDate On Mon, 26 Apr 2010 08:37:01 -0700, dar <dar@discussions.microsoft.com> wrote: >I would like to run a qry that will show me when my insurance policyies >expire. Some expire in 30 days others in 90. > >Table: Customers >Fields: >CustomerName >ContactName >PolicyNumber >PolicyExpirationDate 30 or 90 days from when? How (based on the info...

Best Way To Query The Following Information
I have a form and a database in MS Access 2003 that was developed by a programmer over 4 years ago. The database was created through unconventional methods so one table holds a majority of the fields. The form is used to input help desk tickets now, but management wants to start querying different types of information from it. I was tasked to create a section on the form that indicates if a technician group has either "passed", "failed" or is in a "failed but resubmitted" for a certain ticket. I have a checkbox to indicate each state. There are a series of for...

How do I protect a sheet that has a group/outline in it
so that the group can still be expanded and retracted once the sheet is protected? If you already have the outline applied, you can protect the worksheet in code (auto_open/workbook_open??). Option Explicit Sub auto_open() With Worksheets("sheet1") .Protect Password:="hi", userinterfaceonly:=True .EnableOutlining = True End With End Sub It needs to be reset each time you open the workbook. (excel doesn't remember it after closing the workbook.) IThelper wrote: > > so that the group can still be expanded and retracted once the sheet i...

excel autofilter arrow custom query
hello and thanks for any future help you can give me. i am making a spreadsheet to record data for statistical purposes. m problem is; that i have one column of numbers, that i want to hav saved on this spreadsheet when i open it, and then another column o numbers, that i will put on this spreadsheet after i have opened it some of the numbers in the columns may well be duplications, copies and so i would like to set-up a third column, which shows th duplicated numbers. i have put on an autofilter, and using the arrow selected custom thinking that i could put in a formula for this thir column t...

I need to get a form for "Ask the President" of the company
I have a meeting coming up where we want to send out informal forms to "Ask the President" questions without a name attached. Any ideas? Huh? "Karen Kunzelmann" <Karen Kunzelmann@discussions.microsoft.com> wrote in message news:60587D4F-F8EC-47BE-BE5A-600DB33B49CB@microsoft.com... :I have a meeting coming up where we want to send out informal forms to "Ask : the President" questions without a name attached. Any ideas? ...

What does the [Group] in a file name mean?
I have worked with 2 to 3 Excel files that have the designation of "[Group]" in them. Does anybody know what the relevance of this and if it is known to cause any problems. Thanks for your feedback. EES Hi this indicated that you have 'grouped' several sheets (selected them together). Simply select on single tab name to remove this. Note: In group modus a change done on the active sheet will also be done in all grouped sheets -- Regards Frank Kabel Frankfurt, Germany EES wrote: > I have worked with 2 to 3 Excel files that have the > designation of "[Grou...

Weird query issue
I'm working on a query which calculates a paycheck witholding amount. I take the employees desired yearly contribution and divide it by the number of paychecks in a year, in our case, that's 26. This gives me the amount that needs to be witheld from each paycheck, provided the employee in question was employeed at the first of the year. Depending on how I write the expression in the query, I get 2 different numbers. Examples of the field expression in question follow: Bi-Weekly: ([curContrib]/[Weeks]) This gives me the wrong result, even if Weeks = 26. Bi-Weekly: ([curCo...

Update/Append Queries minus confirmation Dialouge
Does anyone know how to silence the confirmation dialogue appear when you run an update or append query. The first message box asks for confirmation before you run and update table records. The second shows the number of records that will be updated. I'm trying to automate an application with various queries and macros and need to eliminate these message boxes from the end-user. Thanks for the help! News, In a macro, you just need to put a SetWarnings/No action before the first OpenQuery action. -- Steve Schapel, Microsoft Access MVP news.newsguy.com wrote: > Does anyo...

How to get a report to default to a default printer...
Keeps saying "previously formatted for..." and asking for another printer that is not available, additionally, once we "redirect" it to an available printer, it will print THAT TIME, but will default back to the unavailable printer everytime it is opened. I didn't do anything in the code to set the printer so I'm a little baffled by this. Is there something that I CAN do in the code to keep the formatting as it should be? Example: Landscape, Legal paper and NOT to a specific printer? You can modify the report's printer and properties through c...