sum values from the same item in a single column?

Hi,

I have a query to get a table like this:
Item   Num    Values
A         1          10
A         2          22
A         3          78
B         1          32
B         2          40
B         3          87
C         1          34
C         2          76
C         3          98
actually each "Item" has more than a thousand of "Num".
how to sum all "Item" (A+B+C) at each "Num"? 
Like: 
Num  Sum
  1      76
  2      138
  3     263

Thanks!

pemt
0
Utf
12/8/2009 9:59:02 PM
access 16762 articles. 3 followers. Follow

4 Replies
1273 Views

Similar Articles

[PageSpeed] 46

pemt,

Have a look at Crosstab queries under Help, that should give you what you 
need.

-- 
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors 
II

http://www.regina-whipp.com/index_files/TipList.htm

"pemt" <pemt@discussions.microsoft.com> wrote in message 
news:214FE9B4-39BE-437B-990B-B488795751C2@microsoft.com...
> Hi,
>
> I have a query to get a table like this:
> Item   Num    Values
> A         1          10
> A         2          22
> A         3          78
> B         1          32
> B         2          40
> B         3          87
> C         1          34
> C         2          76
> C         3          98
> actually each "Item" has more than a thousand of "Num".
> how to sum all "Item" (A+B+C) at each "Num"?
> Like:
> Num  Sum
>  1      76
>  2      138
>  3     263
>
> Thanks!
>
> pemt 


0
Gina
12/8/2009 10:15:21 PM
Group the query by Num and Sum the values:

SELECT Num, SUM(Values) AS Sum
FROM YourQuery
GROUP BY Num;

Ken Sheridan
Stafford, England

pemt wrote:
>Hi,
>
>I have a query to get a table like this:
>Item   Num    Values
>A         1          10
>A         2          22
>A         3          78
>B         1          32
>B         2          40
>B         3          87
>C         1          34
>C         2          76
>C         3          98
>actually each "Item" has more than a thousand of "Num".
>how to sum all "Item" (A+B+C) at each "Num"? 
>Like: 
>Num  Sum
>  1      76
>  2      138
>  3     263
>
>Thanks!
>
>pemt

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access/200912/1

0
KenSheridan
12/8/2009 10:31:16 PM
Maybe I am not following what you want but just drop Item from the query like 
this --
SELECT Num, Sum([Values]) AS Total_Values
FROM YourTable
GROUP BY Num;


-- 
Build a little, test a little.


"pemt" wrote:

> Hi,
> 
> I have a query to get a table like this:
> Item   Num    Values
> A         1          10
> A         2          22
> A         3          78
> B         1          32
> B         2          40
> B         3          87
> C         1          34
> C         2          76
> C         3          98
> actually each "Item" has more than a thousand of "Num".
> how to sum all "Item" (A+B+C) at each "Num"? 
> Like: 
> Num  Sum
>   1      76
>   2      138
>   3     263
> 
> Thanks!
> 
> pemt
0
Utf
12/8/2009 11:04:01 PM
Hi Karl,

Thanks a lot!

pemt

"KARL DEWEY" wrote:

> Maybe I am not following what you want but just drop Item from the query like 
> this --
> SELECT Num, Sum([Values]) AS Total_Values
> FROM YourTable
> GROUP BY Num;
> 
> 
> -- 
> Build a little, test a little.
> 
> 
> "pemt" wrote:
> 
> > Hi,
> > 
> > I have a query to get a table like this:
> > Item   Num    Values
> > A         1          10
> > A         2          22
> > A         3          78
> > B         1          32
> > B         2          40
> > B         3          87
> > C         1          34
> > C         2          76
> > C         3          98
> > actually each "Item" has more than a thousand of "Num".
> > how to sum all "Item" (A+B+C) at each "Num"? 
> > Like: 
> > Num  Sum
> >   1      76
> >   2      138
> >   3     263
> > 
> > Thanks!
> > 
> > pemt
0
Utf
12/9/2009 3:30:01 PM
Reply:

Similar Artilces:

How to prevent item changed in a CListCtrl?
I use MFC Visual C++ 4.2. I have a CListCtrl in a Detailed View. Below the CListCtrl there are a few CEdits to display the value of each column. So it's kind of the data form. I need to do like this: If users has changed one of the CEdits BUT they haven't save the data and they want to change the selected item of the CListCtrl to other item, a confirmation message will appear to ask if the data they have been changed to be saved or not. There are 3 options: Yes: the data change is saved and the current focus is on the item user desired. No : the data change is NOT saved and th...

sales line item workflow and process holds and requested ship date
It would be nice if we could set up workflow tracking per line item as well as assign process holds per line item in Sales Trx Entry. It would also be nice to have the abilility to fulfill all items on an order through a specified requested ship date and leave items with future requested ship dates unfulfilled. Then to be able to transfer and ship items that have been fulfilled without having to fulfill those with future requested ship dates. Right now I have to backorder those items that have future requested ship dates just so I can transfer and fulfill the other items on the order...

Lookup and return value
I have 2 worksheets, W1 has a unique id that I need to place on W2, if the names match. If name matches from W1, then place Unique ID on W2 Worksheet 1 - 2 columns Unique Id Name 100 Little 200 Smith 201 Blue 303 Yellow Worksheet 2 - 2 columns - Need to display Id Name ID Little Yellow Blue Smith On worksheet 2, cell b2: =INDEX('Worksheet 1'!A:A,MATCH(A2,'Worksheet 1'!B:B,0)) With error trapping: =IF(ISNUMBER(MATCH(A2,'Worksheet 1'!B:B,0)),INDEX('Worksheet 1'!A:A,MATCH(A2,'Worksheet 1'!B:B,0)),"") ...

Data labels: need value AND % change
I'm working on a series of column charts that show annual data for # sold, $ volume, average price, median price, etc. I want it to show the 2002 and 2003 values for each of these but I ALSO want to be able to show the % change between the 2, so we can see trends. So far, I only see that you can label the data with the value, or the percentage. I haven't seen how you can show the values AND the % change. Any ideas? I'd really appreciate it! --- Message posted from http://www.ExcelForum.com/ Create the desired label in a separate column (the 2003 price concatenated with t...

refer to second column of listbox
Hi, I have a multi selected listbox. How can I refer to the second column of the listbox? Me.lstContract.ItemData(varItem).column(1) ??? Dim varItem As Variant For Each varItem In Me.lstContract.ItemsSelected gstrReportFilter = "[Ck_ContractID]='" & Me.lstContract.ItemData(varItem) & "'" ''DoCmd.OpenReport "rptCoFundActivity_k" '', , , gstrReportFilter DoCmd.OpenReport "rptCostShareContribution_k", acViewPreview '', , gstrReportFilter Next varItem SF ...

Allow Multiple Values check box not showing up
I am trying to create a Multivalued Lookup Field. I am using the Lookup Wizard, but when I get to the place where is should let me choose to store multiple values for the lookup that section does not come up. I went online and printed the instructions off the help page so I know I am doing it right. Help! IMO, multivalued and lookup fields are not a good idea. You didn't mention what version of Access you are using. -- Duane Hookom Microsoft Access MVP "mschurter" wrote: > I am trying to create a Multivalued Lookup Field. I am using the Lookup > ...

Receivables Management Aged Trial Balance by Item
Customer needs the ability to run the Receivables Aged Trial Balance report by Inventory Item. Currently there is no way to link the Item Number from the SOP30300 table to the RM Aging Report. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/B...

Customizing menu item names, form names etc.?
Hi all, Is there any way to customize the names of forms - for example if I want to call an "account" a "business partner" and modify it in the menu items and form/view names as well, can I do it? Thanks in advance, -- Anurag ...

return a column no
I have a cell containing text. I need a formula that takes the text and finds an exact match in a row and return the column no. Eg Find Text: Name3 Name1 Name2 Name3 Name4 Name5 I want to return the column number which in this case is row 3. I know there is a simple formula but I cant find it Thanks =match(a2,1:1,0) will return the number of the column of the first match (if there is one). Assumes that the Name3 is in A2 and the names are listed in A1:IV1 (row 1) Craig McLaughlin wrote: > > I have a cell containing text. I need a formula that takes the text...

re: best way to move columns between 2 excel docs
Ok, here is my little project: In execel.doc A I have some columns that I want to move to Excel.doc B, and the columns are always positioned the same way in A, they never change their position. How do you transfer them to B, to append a running process of pre-existing prefilled from previous days ? Any code samples ? Macros ? Please help!!! :- ) I am reading a book on application-level programming, VBA for Access, so I understand more and more everyday. How much work do I have here ? Thanks. ...

Can't insert column; keep receiving error message
I was updating a large spreadsheet and all of a sudden I was unable to insert columns. I kept getting an error message that said, "...unable to shift nonblank cells off worksheet." I tried everything from unhiding all columns and rows toreformatting all the comments to move and size with their relative cells. I also removed all the comments and I was still unable to insert a column. Does anyone have a solution Thank you >-----Original Message----- >I was updating a large spreadsheet and all of a sudden I was unable to insert columns. I kept getting an error message th...

having every deleted items folder empty on exit
Hi. I have outlook 2003 set up so that my deleted items folder in my default personal folders empties when I exit. At the same time, my wife has a personal folder set up that has a deleted items folder in it as well. However, this one does not empty automatically as the default deleted items folder does upon exit. Is there any way to change this so all deleted items folders empty when I exit? Thanks for any help. No. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Author, Google and Othe...

Column Headers #7
How do I print Column Headers on every page? File | Page Setup | Sheet Put the row you want to Repeat in the spot provided for Rows to Repeat at top. tj "Cscritch" wrote: > How do I print Column Headers on every page? ...

Relating one column to another
Stupid newbie question coming up: Let's say that in column A I have a series (not sequential) of numbers. In column B I have a word that goes with that number. In column C, I have numbers, which represent the same words as the number in column a represents the word in column B. In other words, I have: Number Word Type: 1: Banana 2 2: Fruit 3: Sausage 4 4: Meat I want to end up with Number Word Type: 1: Banana Fruit 2: Fruit 3: Sausage Meat 4: M...

Excel should have a "Change Case..." menu Item like Word!!
See the subject, I know you can set up a formula to do it I want a button! PLEASE, for years I've just wanted to change case on the fly, YEARS! ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/community/en-us/default.mspx?mid=3c7554c...

Comparing two Columns #3
I have to columns full of data one column is "inventory" and the secon column is "charged items" inventory is what should be on the shel according to the computer, and charged items are the books that ar checked out. So the inventory - charged items would more less give m what "should" be on the shelf according to the computer. So I want column C to list me everything that is in Column A that i not in Column B. Column A is Inventory Column B is charged items (goes up to 6000). After I typed it out it seems very simply I just haven't figured ou how to...

How may I add more than 256 columns to an Excel spreadsheet?
I am trying to create a horizontal 12 month calendar in an Excel spreadsheet. I need 370 columns. Is there anyway to accomplish this within Excel? SPO, excel only has 256 columns -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "SPO" <SPO@discussions.microsoft.com> wrote in message news:75457654-C31C-4787-9790-D07E220849BA@microsoft.com... > I am trying to create a horizontal 12 month calendar in an Excel spreads...

Renaming a column label
How do I rename the column header labels from "A", "B", etc. to something else? Made a valiant effort. Can't figure it out. Mike -- DO NOT reply to the "from" address in this post. Instead, construct a reply address from this template: v6nos at yahoo dot com. Short answer: you don't - that capability (other than using numbers - check the Tools/Options/General R1C1 reference style checkbox) doesn't exist in XL. Longer answer: If you hide the row and column headers (Tools/Options/View) you can format Row 1 for column headers. You can even use t...

Grouping the scanned item quantity.
On POS screen of RMS 2.0, how can 2 identical items scanned separately display the qty of 2 in a single item, not as 2 separate items. Or if it's easier, the recipt prints out in group is fine too. Thank you in advance for your help. Either way this would require customization. If you are interested please contact me for an estimate. -- Thank you, Ryan Sakry Program Manager Retail Information Technology Enterprises 320-230-2288 rsakry@rite.us www.rite.us "FLASH" <FLASH@discussions.microsoft.com> wrote in message news:8E0653F8-1A8C-4D02-8E19-1575E9D01D0F@microsoft.co...

MF_GRAYED for menu items (run time)
HI. I would like to enable and able some of my menu items in run time. Here is the code I have been writing, but it seems not to work, as nothing is gonna happen: BEGIN_MESSAGE_MAP(CTestGLApp, CWinApp) //... ON_COMMAND(ID_REMOVE, OnRemoveData) //... END_MESSAGE_MAP() void CTestGLApp::OnRemoveData () { CSelectDlg SelectDlg; SelectDlg.DoModal(); MyGlobDat.erase(MyGlobDat.begin() + SelectDlg.GetIndex()); if(!MyGlobDat.size()) { CWnd *wind; wind = AfxGetMainWnd(); CMenu *menu = wind->GetMenu(); menu->EnableMenuItem(ID_REMOVE,MF_GRAYED); } } Thanks a lot for your help! ...

Matching Columns in work sheets and copying both rows to new
I am trying to match up two different spread sheets based on one column but to copy both rows to a new sheet. eg. First sheet has the following headings: "Owner Group " "Owner CC " "Type " "Product Categorization Tier 2 " "Product Categorization Tier 3 " "Device Name " "Status " "Model Number " "Mfg. Name " "CI ID " "Old Asset ID " "Serial Number " "Region " "Country " "Site " "Building " "Floor " "...

How do I combine 2 text columns in Microsoft Excel?
I have two columns of descriptive text, the second column is the end of the first column's sentence, however I can not find a way in the Help options to combine the text values to create one complete sentence in one column. Does anyone know of a way to do this? Try: =A5&" "&B5 if you want spaces between the columns or look at the CONCATENATE function example =CONCATENATE(A5,B5) Domaniman wrote: > I have two columns of descriptive text, the second column is the end of the > first column's sentence, however I can not find a way in the Help options to ...

sum values from the same item in a single column?
Hi, I have a query to get a table like this: Item Num Values A 1 10 A 2 22 A 3 78 B 1 32 B 2 40 B 3 87 C 1 34 C 2 76 C 3 98 actually each "Item" has more than a thousand of "Num". how to sum all "Item" (A+B+C) at each "Num"? Like: Num Sum 1 76 2 138 3 263 Thanks! pemt pemt, Have a look at Crosstab queries under Help, that should give you what you...

Extracting Data in Cells in order -- (or) eliminating empty cell space in a column
Hi I have this problem that I bet is easy to solve, but i am lost. I am an expert at the slow way to do things, but maybe there is a better way. The only way I can describe the problem is by means of an example. Lets say I have a column of numbers: >_A_|_B_| etc >> 1_1_|___| 2_3_|___| 3_2_|___| 5_5_|___| 5_3_|___| 6_4_|___| 7_7_|___| 8_3_|___| 9_1_|___| and then i write a little function in the adjoing cell, B1: =if(a1=3,a2,"") From there I fill down column B to B9. OK, pretty simple so far, right? What I am looking for is instances where I find a '3' in co...

unique values of a column
hi how can i get the unique values of a column in an array? thanks in advance I think Advanced Filter will do what you want, there is an excellent tutorial here from Debra Dalgleish http://www.contextures.com/xladvfilter01.html Regards <anonymous@discussions.microsoft.com> wrote in message news:073d01c49649$6e4e49e0$a401280a@phx.gbl... > > hi > > how can i get the unique values of a column in an array? > thanks in advance > > Hi, Additionally check out Chip Pearson's website at: http://www.cpearson.com/excel/duplicat.htm#ExtractingUnique >-----Orig...