grouping ...

 columnA    ColumnB    ColumnC  columnD
 12345       23        78956      10.00
 12345       23        89153      15.00
 56780       23        15687      20.00
 56780       23        15890      20.00
 12345       23        78853      30.00
 12345       23        87568      30.00

I am importing a table from an external source structured as above
and if I sum columnD based on grouping on columnA I would get total
for  
 12345 = 85.00 4 detail records
 56780 = 40.00 2 detail records

what I want to get however is 
 12345 = 25.00 2 detail records 
 56780 = 40.00 2 detail records
 12345 = 60.00 2 detail records

Apart from grouping is there any other query I can build that 
would give me the expected result  
-- 
thanks as always for the help
 jer
0
Utf
3/27/2010 8:56:01 PM
access.queries 6343 articles. 1 followers. Follow

4 Replies
884 Views

Similar Articles

[PageSpeed] 43

jer wrote:
>columnA    ColumnB    ColumnC  columnD
> 12345       23        78956      10.00
> 12345       23        89153      15.00
> 56780       23        15687      20.00
> 56780       23        15890      20.00
> 12345       23        78853      30.00
> 12345       23        87568      30.00
>
>I am importing a table from an external source structured as above
>and if I sum columnD based on grouping on columnA I would get total
>for  
> 12345 = 85.00 4 detail records
> 56780 = 40.00 2 detail records
>
>what I want to get however is 
> 12345 = 25.00 2 detail records 
> 56780 = 40.00 2 detail records
> 12345 = 60.00 2 detail records
>
>Apart from grouping is there any other query I can build that 
>would give me the expected result  

I am not clear on how the grouping is supposed to work.  What's the logic
behind it?  Do you want just the first two records per  ColumnA?

-- 
Message posted via http://www.accessmonster.com

0
PieterLinden
3/27/2010 9:55:26 PM
On Sat, 27 Mar 2010 13:56:01 -0700, jer <jearl45@hotmail.com> wrote:

> columnA    ColumnB    ColumnC  columnD
> 12345       23        78956      10.00
> 12345       23        89153      15.00
> 56780       23        15687      20.00
> 56780       23        15890      20.00
> 12345       23        78853      30.00
> 12345       23        87568      30.00
>
>I am importing a table from an external source structured as above
>and if I sum columnD based on grouping on columnA I would get total
>for  
> 12345 = 85.00 4 detail records
> 56780 = 40.00 2 detail records
>
>what I want to get however is 
> 12345 = 25.00 2 detail records 
> 56780 = 40.00 2 detail records
> 12345 = 60.00 2 detail records
>
>Apart from grouping is there any other query I can build that 
>would give me the expected result  

I don't understand your question. Tables have no order; the only things you
can use for grouping are values actually stored in the records in the table.
Are you assuming that the first two 12345 rows are somehow "grouped" already?
Because they aren't!

More info please.

-- 

             John W. Vinson [MVP]
0
John
3/27/2010 9:59:18 PM
jer wrote:

> columnA    ColumnB    ColumnC  columnD
> 12345       23        78956      10.00
> 12345       23        89153      15.00
> 56780       23        15687      20.00
> 56780       23        15890      20.00
> 12345       23        78853      30.00
> 12345       23        87568      30.00
>
>I am importing a table from an external source structured as above
>and if I sum columnD based on grouping on columnA I would get total
>for  
> 12345 = 85.00 4 detail records
> 56780 = 40.00 2 detail records
>
>what I want to get however is 
> 12345 = 25.00 2 detail records 
> 56780 = 40.00 2 detail records
> 12345 = 60.00 2 detail records
>
>Apart from grouping is there any other query I can build that 
>would give me the expected result  


Not unless there are other fields that can be used to
separate the 12345 records into two groups.

It is important for you to understand that the records in a
table are NOT ordered in any way that a human can make sense
out of the way they appear.  The ONLY way to sort records is
by using a query with an Order By clause.

Based on that, there is no guarantee that the records you
posted above will appear in that order.  Even if they appear
that way today, you can not rely on them appearing the same
way in the future.

-- 
Marsh
MVP [MS Access]
0
Marshall
3/27/2010 10:05:24 PM
thank you all - I understand, 
-- 
thanks as always for the help


"Marshall Barton" wrote:

> jer wrote:
> 
> > columnA    ColumnB    ColumnC  columnD
> > 12345       23        78956      10.00
> > 12345       23        89153      15.00
> > 56780       23        15687      20.00
> > 56780       23        15890      20.00
> > 12345       23        78853      30.00
> > 12345       23        87568      30.00
> >
> >I am importing a table from an external source structured as above
> >and if I sum columnD based on grouping on columnA I would get total
> >for  
> > 12345 = 85.00 4 detail records
> > 56780 = 40.00 2 detail records
> >
> >what I want to get however is 
> > 12345 = 25.00 2 detail records 
> > 56780 = 40.00 2 detail records
> > 12345 = 60.00 2 detail records
> >
> >Apart from grouping is there any other query I can build that 
> >would give me the expected result  
> 
> 
> Not unless there are other fields that can be used to
> separate the 12345 records into two groups.
> 
> It is important for you to understand that the records in a
> table are NOT ordered in any way that a human can make sense
> out of the way they appear.  The ONLY way to sort records is
> by using a query with an Order By clause.
> 
> Based on that, there is no guarantee that the records you
> posted above will appear in that order.  Even if they appear
> that way today, you can not rely on them appearing the same
> way in the future.
> 
> -- 
> Marsh
> MVP [MS Access]
> .
> 
0
Utf
3/27/2010 11:05:01 PM
Reply:

Similar Artilces:

Primary filed group full
I keep getting this message after each transaction: Primary filed group full...cannot locate space for object transaction in database quality market because the primary file group is full. Score MS oledb provder for sql server. Sql space 42000 native error 1105 no help file available. Set no account on Insert into transaction...ship to ib,store id, batch number, time in..........so forth and so on..... -- Thank You Vince :) Not sure, but it sounds like you've reached your 2 gig limit if your using MSDE. You might want to check the size of your database. If that's the cas...

distribution groups don't deliver to some addresses
This is a multi-part message in MIME format. --------------040204040500070505000200 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit I've been trying to create a distribution group that includes both internal and external contacts. I've created all the external contacts as contacts in AD, adding an Exchange email address (SMTP:user@externaldomain.com). When sending to this group messages fail to be delivered to some people on the list, it would appear that it won't deliver to hotmail.com, NTL or Wannadoo. I've checked that the...

formula for displaying only the last entry of a group of cells
How do you write the formula that will display only the latest entry in a succession of entries? For example, if I have 12 months in 12 columns, and the 13th column specifies "Last Update" , if I have made entries for Jan, Feb, Mar so far, I need only the latest and last entry to appear in the 13th column. When I enter April, then it would display in the 13th column cell and so on. Please help. -- Thank you Hi, This returns the rightmost entry in the row A2:M2 =LOOKUP(2,1/(A2:M2<>""),A2:M2) -- Mike When competing hypotheses are otherwise...

Group Contact was working before, now it will not work
I have a number of group contacts where I email my message to. It has been working well for months. However a week ago, Outlook returned the mails saying the contact addresses are not vaild. I cannot find any solution any where. Can anyone help? We have no idea what you mean by group contacts. If you mean a Distribution List, this is a common occurrence. DL's are not reliable or robust. They become corrupt frequently and cannot be repaired. Few of us rely upon them for that reason. You can always try restoring it from your most recent backup. -- Russ Valentine "LS...

Group email problem! PLEASE HELP!!!
We are receiving an error message when we try to send emails to some of the groups in our contacts. The message says that an internal support function error has occurred. Can anyone shed any light on the nature of this error and/or propose any solutions to this problem? Thanks! ...

The cursor in Excel select groups of cells and I can't get out.
I have found that my cursor seems to lock up for some unknown reason occasionally and when I try to escape I can't get out. The program just selects the adjacent cells wherever I move my mouse. It is maddening since I must shut down Excel to get out. Does anyone know why this is happening? http://www.mvps.org/dmcritchie/excel/ghosting.txt Regards, Peo Sjoblom "Jenn" wrote: > I have found that my cursor seems to lock up for some unknown reason > occasionally and when I try to escape I can't get out. The program just > selects the adjacent cells wherever ...

Configuring e@syfolder discussion group
We've started using e@syfolders in a Corporate or Workgroup environment. I've been testing this with a colleague, and we've discovered a flaw in the way we were hoping to use these. We had hoped to be able to put Word documents in the DD such that individual team members could update it with Track Changes enabled so that we could see who had done what and when. I got my colleague to open a test document from the e@syfolder DD on his PC whilst I already had it open. I then asked him to make some changes and save it which was all OK. I then made some changes from my PC on the co...

Hiding email addresses from group recipients
I have a lot of individual groups to whom I send emails. I do not want the recipients of these emails to have the email addresses of all the other people that receive that particular email. How can I hide their email addresses? I've searched everywhere for the answer but remain clueless! You need to go to "view" and pull it down to "show bcc." You click the addressess into BCC for Blind carbon copy. The recipient will not see the others. It's a great consideration. I hate it when people include me in a visible list, because that's how spam begins. >...

Word Group Deletion
I have a large column of titles that I would like to delete all th "The's" in the beginning of the titles. If anyone could help me on this it would be much appreciated. I am novice Excel user -- Message posted from http://www.ExcelForum.com Hi try the menu 'Edit - Replace' - enter your text to replace - in the second entry field enter nothing -- Regards Frank Kabel Frankfurt, Germany > I have a large column of titles that I would like to delete all the > "The's" > in the beginning of the titles. > If anyone could help me on this it would b...

Show In Groups Default
Having upgraded from Outlook 2000 to Outlook 2003, every time I go to a folder for the first time, the "Show In Groups" item is checked. Is there a way to set the default to NOT show in groups, rather than having to do it manually for each folder? Thanks Edit the Messages view or define a new one you apply to the folder on first use. -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -Backup and Restore -Create an Office XP CD slipstreamed with Service Pack 3 ----- "John Schneider" <JohnDONTSPAMME.Schneider@eWebITNOSPAM.Kom> wrote in...

Read yahoo news groups with OE?
Not being very familiar with Yahoo, and having been asked to contribute to a newsgroup there, I wonder if it is possible to set up an account for this in OE and read it with my other news groups rather than with a browser? If so, can someone advise me of the server settings. Cheers, S No, because they are html groups that must be viewed in a brower. "spamlet" <spam.morespam@invalid.invalid> wrote in message news:%23u%23$RWQoKHA.1548@TK2MSFTNGP04.phx.gbl... : Not being very familiar with Yahoo, and having been asked to contribute to a : newsgroup there...

.Re: Using Xsl to group by unique values
"Anas M. Nebuchadnezzar XXXVII" <Duck@Kronkltd.net> wrote in message news:DLWdneiY8IsBdKPdRVn-uQ@comcast.com... > I'm not sure if this is the right forum to ask this, seeing this problem > is pretty much straight XSL, but here goes. > > I'm writting a simple employee database, and I want to be able to: > A) get a node-set returning only unique values based off of a query. > (the names of all the managers) > b) loop through each of the nodes, sorted by the list of the managers. > > My Xml looks something like this: > > <root> &...

Grouping multiple data for one search
Hey, I have a table with 4 columns; Metro, St Cloud, Duluth, Rochester. In those tables are lists of counties associated with the column names (Metro -> Hennepin, Anoka, Wright) (Duluth -> St. Louis, Carlton) etc... To begin with, I am tweaking a form that is linked to a query (SQL). Ideally, I would like a drop down box with Metro, St Cloud, Duluth, Rochester as choices. If you search for one of these (Metro for example) all the counties associated with metro are returned. Also, I would like to be able to select multiple areas, Metro and Duluth, which will return ...

pivot table -repeating "months or days" after grouping
I have data that includes the month, day, hour, min. When I run th pivot table function to reduce the data to hourly I need the date t repeat in the date column. Example: 9/19/2002 0:00 2.7 9/19/2002 0:10 3.7 9/19/2002 0:20 3.8 9/19/2002 0:30 4.6 9/19/2002 0:40 4.8 9/19/2002 0:50 4.8 9/19/2002 1:00 5.6 9/19/2002 1:10 5.2 What I get when I group the Pivot table: Sep 19-Sep 12 AM 2.7 - - 1 AM 3.7 - - 2 AM 3.8 - - 3 AM 4.6 - - 4 AM 4.8 What I want is: Sep 19-Sep 12 AM 2.7 - 19-Sep 1 AM 3.7...

Category Groups #2
Is there any way to modify categroy groups in Money 2000? The default groups aren't really what I want - can I modify or add to them? Thanx, Allan Clearwaters In microsoft.public.money, Allan Clearwaters wrote: >Is there any way to modify categroy groups in Money 2000? The default >groups aren't really what I want - can I modify or add to them? See FAQ available at http://www.bollar.org/msmoney/ for information. ...

Why must the "Everyone" group have full NTFS permission on C drive
We ran the MSBA on our Win2003 Exchange 2000 server, and the result advised us to modify the NTFS permission for the "Everyone" security group from Full Control to Modify on the C drive. When we did that all the Mailbox Storage groups went down and would not start up again. Can anyone give us an explanation for this or point us to some documentation regarding this issue. The mdbdata directory was inheriting permissions and store.exe needed more than was granted? Exchange should be running as local system, so it sounds like you (or someone) have removed SYSTEM from the file...

Moving Servers Across Administrative Groups...
Here's the quick scenario... Exchange 5.5 original site. Exchange 2000 (non-cluster) brought up as first Exchange 2000 server in 5.5 site. (not doing anything) Exchange 2000 cluster brought up. Exchange site name is XXXNTDOMAIN which, in ESM shows up as the name of the First Administrative Group. This doesn't match the logical environment going forward so I was curious if it was possible to do: 1. Rename the XXXNTDOMAIN administrative group to something else? 2. Create a second administrative group and bring up a 2003 cluster at some point in the near future and migrate ...

Set "Reading Pane" and "Arrange By Groups" default to OFF in Outlook 2003
When I create a new folder in Outlook 2003 the default settings for this new folder are "Show Reading pane" and Arrange by Groups" Ok, I can disable these settings for each new folder individually. But is there an option to turn these settings off by default for ALL new folders resp. for all existing folders? Peter ...

Single or Multiple Routing Groups
Hi, Looking for a best practice type answer here. I have physical 3 sites (and 3 sites in AD Sites and Services), with an Exchange 2003 server in each. We only have a single Exchange Routing Group. One site has a fax server, and the connector's scope is set to Organisation. Without adding additional routing groups, is there any way to restrict the scope (and the routes it advertises) to it's local site? Also, what is the standard for placement of routing groups. The official Microsoft blurb is that you can use a single routing group when you have "permanent and reliable c...

can you spot alias in this group?
Men dressed in sheepskin costumes have a rest during a carnival celebration as they march to the village of Ituren in northern Spain, Monday. The tradition is to march through the village every year in a ritual to purify the harvest land from evil spirits and to welcome the coming Spring. Why they walk in relative safety on the serene streets of downtown Ituren is a question that has puzzled sociologists and anthropologists for decades. When asked, the men of Iturene state, "cuz that's where the booze is". For the curious, here's what the bravaduras looked l...

Hidden Recipients show up in query-based distribution group
I have several recipients that I have hidden from the GAL because they have left the company, but they still show up in the Preview tab of our query-based distribution groups. Does anyone have a solution to this problem?? Thanks, Dave modify the ldap query to exclude recipients with msExchHideFromAddressLists attribute (set to true)... ? -- Bharat Suneja MCSE, MCT -------------------------------- "DaveF" <DaveF@discussions.microsoft.com> wrote in message news:7E57B631-E4F4-4B39-99F0-C1F4FA784CC0@microsoft.com... >I have several recipients that I have hidden from th...

Crosstab Query
I've created a crosstab query that counts the number of calls per month. The call field only contains a date. I've used that field as the column header and chose to group it by month, but can't get it to seperate the months by year. For instance all of March is grouped together whether it's March, 2009 or March, 2010. I'm pretty new to Access so I'm not familiar with SQL or VBA. Any assistance would be greatly appreciated. Thanks, Nate Access 2003 "Nate" <Nate@discussions.microsoft.com> schreef in bericht news:CAC55303-3AA4-4057...

Send a personal email to a group of people
I am using the trial version of Send Personally (http://www.mapilab.com/outlook/send_personally/) with Outlook. Is there a freeware alternatiev for this? Thanks Mail merge with Word can send personal messages to several selected contacts. Z -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx "��" <news@kidsnews.au.org> wrote in message news:41610591$0$118$65c69314@mercury.nildram.net... >I am using the trial version of Send Personally &...

Disable wireless cards using group policies
I'm looking for a way to disable client wireless cards then they login to our domain but at the same time allow one IP schema to allow wirelss card connections (through a VPN). I haven't found any info as of yes on my own. Thanks, Todd Dear, can you explain that why you want to do this because these both are same access to your network. Regards, SMA "twood" <twood@discussions.microsoft.com> wrote in message news:049724A4-E71F-4A1B-B0E5-1E1E7476E056@microsoft.com... > I'm looking for a way to disable client wireless cards then they login to > our &g...

Group Folders
Hello : I am Vice President of our local Photography Club. I would like to set my Windows Mail up with all my club members in one folder so when I send out a club e-mail all members will receive the same letter. Also I would like to seperate them from the rest of my contacts. So I don't have to spend so much time sending the same letter one at a time. I know their is a way but I don't know how to set that up. If someone would walk me through step by step set up , it would help me so much I will thank you in advance. Thank you so much. You can't really create s...