Sum of counted values in a query

Hello,

My question,....

I have a query which count the number of records in a table (as a
result of the menuoption view-> totals ->count) as a result of the
group by function for unique records.

The result is:
column1   column2
A             10
B             15
C             8

Now I want to insert a column3 which gives the sum of that counted
values. I'd tried something like:  expr1:Sum(Count[column2])

The result should be for each record in column3: 33  (result of the
sum 10+15+8)
It doesn't work.

Somebody knows a solution ?

Thanks
  regards,
   Johan
0
johan
12/24/2007 11:24:43 AM
access 16762 articles. 3 followers. Follow

2 Replies
561 Views

Similar Articles

[PageSpeed] 0

You can always write another query:

Select Sum([Column2]) As TotalCount From Query1
-- 
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"johan" <jmslab@xs4all.nl> wrote in message 
news:4588bfde-f926-44e4-8aec-45df565b69ed@s12g2000prg.googlegroups.com...
> Hello,
>
> My question,....
>
> I have a query which count the number of records in a table (as a
> result of the menuoption view-> totals ->count) as a result of the
> group by function for unique records.
>
> The result is:
> column1   column2
> A             10
> B             15
> C             8
>
> Now I want to insert a column3 which gives the sum of that counted
> values. I'd tried something like:  expr1:Sum(Count[column2])
>
> The result should be for each record in column3: 33  (result of the
> sum 10+15+8)
> It doesn't work.
>
> Somebody knows a solution ?
>
> Thanks
>  regards,
>   Johan 


0
Arvin
12/24/2007 3:35:46 PM
Thanks Arvin,

Simple. It works oke.
0
johan
12/27/2007 5:48:04 AM
Reply:

Similar Artilces:

Public folder unread message count
Hi There ! Is it possible to get subj. on public folders (inbox alike) where the unread message count is pr. user based ? With Kind Reguards Kim On Wed, 21 Jun 2006 07:25:01 -0700, Kim (KBData) <KimKBData@discussions.microsoft.com> wrote: >Hi There ! > >Is it possible to get subj. on public folders (inbox alike) where the unread >message count is pr. user based ? > >With Kind Reguards > >Kim huh? Kim, If you are running Exchange 2k3 you can change this setting on the General tab of the Public folder in question. If you are running Exchange 2k let me ...

Parameter query in Access 2003 report
I want to create a parameter query that asks the user to select one, many, or all values from a field when opening a report. I would like the selection to be available from a list, radio box, or pull down menu to minimize spelling errors, etc. Unfortunately the list is of names which number about 100. Can this be done? If so, could someone please point me to the help pages that describe this process? My solution seems clunky but it is the only way I can see how to do it. Any suggestions as to how else might I best approach this? Thanks in advance Debbie See my sample database that shows ...

a count field in a query?
Hello, I have a query in which I would like to create a field which increments by 1 for each record selected, so if there are 10 records selected by the query, this column would show numbers 1 to 10. Ideally I would like to have some text in front of each number, say invoice1, invoice2 etc. From a previous posting I realise that this is frowned upon but it would be the simplest solution for my problem! How can I do this? Thanks Geoff On Mon, 03 Dec 2007 08:54:00 +0000, Geoff Cox <<>> wrote: >Hello, > >I have a query in which I would like to create a field which >...

Macro do delete values in cells
If I tipe a value (text) in N I want the values in the same row in O P & A to be deleted. Please help ...

Retrieve Picklist Values from caseorrigincode, Incident Entity
Hi, Can someone please advise me on how to retrieve the caseorigincode, picklist values in vb.net? Thanks, Larry B. ...

VBA to count periods in a single cell
I am looking to count the number of periods within a single cell using vba. For instance if I have 4 cells that contain the following: 5M09-0000700.01.10 5M09-0000700.01.10.10 5M09-0000700.01.10.10.10 5M09-0000700.01.10.10.20 Thus the output of the code for the first cell should be '2', the second cell output would be '3', and the third/fourht cells would be '4' because that many period characters were encountered. Data within these cells will contain letters, numbers, periods, a few spaces and the "-" dash symbol. Any help is greatly appre...

auto email if cell value changed
I'm using a workbook to schedule staff. Each month is a worksheet then I have some counts that are done. The days of the month are in the top row and the names of the staff in the first column. We've set up autorefresh on web pages so the schedule is always available but... people still don't notice shift changes. I want excel to generate an email to the person (we're running exchange too) if a cell value is changed. I was thinking of a couple of steps. 1. So they only get emails about the current month and the month ahead -- Have a cell at the top with YES/NO in i...

CRM customization query
Hi, I have a customization query, I want to see the number of records available before I am getting the data for reports. I need this because, I want to generate a report by applying some filter criteria but at the same time I want the resultant record number below a specific limit. On the advance find, after I am applying the filters we get hte resultant data by clicking on the Find button. This takes us to the page where we can see the data. Now I want to add a new button on the Advance find screen on click of which I can get the record count of the result set for the filter i have ...

Write-Output of XML value has type format like "System.Xml.X..."
Hi, I want to output some value using Write-Output or Write-Host along with some comments. After reading the xml file and while executing the write-output command, its not printing the value, its printing the variable type along with xml structure not the value :( Please let me know how to print the correct output. See below the example for details. --------------------------------------------------------------------------------------------------------------------------- Variable.xml ------------------------------------------------------------------------------------...

including dblink in a query to retrieve data
I connect to a table using a db link i want to execute an spl query and populate results in to a pivo table. My MS Query does not allow me to use a dblink and connect to th table. Please help -- Message posted from http://www.ExcelForum.com ...

Summing over multiple worksheets
Hi. I have weekly data by Contract number over multiple worksheets. I want to sum the hours associated with the contract. The contract information will not always be in the same cell, as new contracts are constantly added. My question is, should I use an If and then a SumIf or would a SumIf by sufficient? =SUMIF("'Apr 4:Jun 27'!, A5:A33",A5,"'Apr 4:Jun 27'!, G5:G33") I'm obviously missing something here. I want this formula to look in worksheets Apr 4 to Jun 27 in cells A5 through A33 and if there is a match to cell A5 on my summary...

Counting highlighted cells?
Is there a way to count cells in a column which have been highlighted or text that has been italicised??. As i have a column full of dates, and when something is completed the suggested date is changed to the actual date, highlighted and italicised. I need to keep a count on the completed dates. Check out http://cpearson.com/excel/colors.htm In article <BC6F544F-DF88-41A8-8F15-5A8D460F73FE@microsoft.com>, "Josh - Westfield Australia" <Josh - Westfield Australia@discussions.microsoft.com> wrote: > Is there a way to count cells in a column which have been ...

Database degrag query
Hi , I am using exchange 2003 with SP1. I have calculated the total size of mailboxes , it is total 30 GB. But the size if edb file is 50 GB and stm file is 25 GB . I have defraged the database offline so that it will purge the white space if there is any. but after defrag no difference in size of the database. it is still 50gb edb and 25 gb stm file. can anuone explain if i have only 30gb total mailbox size then why it is taking so much space. there is no rules and othere things define. Waiting for your help. Regards, Abhi. On 25 Oct 2004 20:45:43 -0700, pandeyabhishek2000@yahoo.com (abhi...

Union Query with division
Hello, it would like to join two tables, Table1 and Table2, however when joining table2 the items listed in that table need to be divided by 2. I normally do this division in a query then join that query to the union query with table1. I wonder if I can accomplish the same thing directly from the union query only. Also, is there a way to rename a customer ID with the same query? (See Customer 9 as example, changing to Customer 15) Example: Table1: [Customer] [Hours] Customer 1 10 Customer 3 5 Table2: [Customer] [Hours] Customer 9 ...

Entering the same value into two table fields at the same time
Say that there is a table which has 2 fields (A, B) There is a form [Form1] (data entry type) to enter data for A by `TextBoxA`. There is also one more `TextBoxB` which is Not Visible. My target is to record the value of (entered thtough `TextBoxA`) into B field of the table as well. So I write down a code "Before Insert" of the Form1 that: " Form_Form1.TextBoxB = Form_Form1.TextBoxA " It is not recording A values into the field B... Where am I wrong? Can you help me please? Sory !!! When I write down the same code into `OnExit` of TextBoxA, it is working...!! yes, ...

Query to count between list of number (Predicting Start/End that may occur in data range)
Hi, I have a below list of numbers. 566667 566668 566669 566665 566666 566671 566672 566680 I want a query that would return a count between start and end of range. Like Start End Quantity 566665 566669 5 566671 566672 2 566680 566680 1 Thank you. On 2 apr, 07:17, Angela <ims...@gmail.com> wrote: > Hi, > > I have a below list of numbers. > > 566667 > 566668 > 566669 > 566665 > 566666 > 566671 > 566672 > 566680 > > I want a query that would return a coun...

Save query result in a variable
hi! hope you can help me AGAIN! i have a form that let's the user pick a name, job, between dates, tax(with or without) and percentage. this is for calculating the comision each employee has earned. i did this by a query that gets the info from the form adn then opens a report and in the report i make the calculations. but it turns out that several employees have 2 or more "jobs" and for each one diferent % of comission. the form and the reports handdle it fine, i'm looking for a way to add all of this reports for each person. i was thinking of saving the sum of each que...

collect clipboard value into string
I have a macro that pastes the clipboard string (I think it's the "clipboard"), Selection.Paste, into the body of a letter. I would like to, within vba, inspect that string first before pasting. How do I capture it into a var which I can inspect? Thank you. "oldyork90" <oldyork90@yahoo.com> wrote in message news:ec8d0dd3-e9b4-47fb-8009-7a2f7c1baf3d@q5g2000hsa.googlegroups.com... >I have a macro that pastes the clipboard string (I think it's the > "clipboard"), Selection.Paste, into the body of a letter. I would > like to, within vb...

How to update a specific field from a Find Duplicates querie
I have to update 1 table that has multiple duplications. If i search for duplicate on Field1, Field2, and Field3 all matching, i receive many duplicates where Field4 and Field 5 are different and/or Null. What do i need to do to the query so that Field4 and Field5 will be automatically updated when the duplicate is found? Current: Field1 Field2 Field3 Field4 Field5 Mike Smith 1234 KEEP Mike Smith 1234 DELETE 9012 Desire result: Field1 Field2 Field3 Field4 Field5 Mike Smith 1234 KEEP 9012 (...

Form Query
I am trying to run a query for a form, I am using the following criteria [form]![frmSchoolsQry]![txtschool] . The form name and the text box from the form. When I click the command button the launch the query a parameter dialog pop up. The dialog ask for the text that is in the text box. I use forms to query dates, they work great, why don’t this work? Any suggestions? You're missing an s in Form: [Forms]![frmSchoolsQry]![txtschool] -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "rap43" <rap43@discussions.microsoft.com>...

Generate selective query results
Who can give me advice? In a table I am storing the information of apartments and their total surface area in square meters within a building. When I select an apartment within a building in a form, I would like to print a report of maximum 10 equivalent apartments of approximately the same size (surface area ± 2 m2). Of these apartments I would like to see the apartment number and the size of the surface area. I tried the Top 10 option but that does not give me the correct result. Key of the table is the building number (CPX_NUMBER). The fieldname of the apartment is VHE_NUMBER ...

Conditional Formating for a cell based on another cell's value
Hi all: I was wondering if there was a way to put a conditional formating on a cell based on the values of another cell. What I mean is that, if Cell A is >5 , make cell b Blue, not just cell A. Likewise, if Cell A is <5, make both cell a and b red. On the same topic, is it possible to put more than 3 conditions on a conditional formating? Thanks, Steve o This might point you in the right direction. Sometimes you need a row formatted according to the item in A1. Use a formula in the conditional formatting in type in something like this... =$A1<5 This will apply the cond...

modifying field values in an Access 2003 query
Someone please help. Is there a SQL or Visual Basic statement that can be used to set/change the values in a field in an Access 2003 query to a default value? I'm more familiar with the concept of a table's field (or a control on a form) having a "default" value. How is it that you expect a query's field to have a default value? What are the circumstances? If, for example, you want to display something like "n/a" when an underlying field in a table contains a Null, you could use something like: YourQueryField: IIF(IsNull([YourUnderlyingField]),...

Joining two queries
Is there a way to join two queries together? In query 1, i have Invoice ID, Description, and Total In query 2, I have the same fields. I would like to see all the records from both queries. Please advise. "Tia" <Tia@discussions.microsoft.com> wrote in message news:D39EB571-0D64-4A8E-9107-351B10E1FE02@microsoft.com... > Is there a way to join two queries together? > In query 1, i have Invoice ID, Description, and Total > In query 2, I have the same fields. > > I would like to see all the records from both queries. Please advise. I believe what you're...

Fill Down to Last Value
I have 2 spreadsheets (Sheet 1 and Sheet 2). Sheet 1 has one header row and sheet 2 has 2 header rows. I am using the following to count the number of rows in Sheet 1 and copy a formula to every row in Sheet 2 based on the count from Sheet 1. With Worksheets("Sheet1") lStop = .Cells(.Rows.Count, "A").End(xlUp).Row End With Worksheets("Sheet2").Range("3:" & lStop + 1).FillDown The formula is perfect except when I only have one entry in Sheet 1. In this case the formula copies the second header row in Sheet 2 instead...