Using Record Count in an equation

I have a "yes/no" field called Sold.  I need to track and report what percent 
of total solicitations sold.  I thought I could use the built in sum feature 
which provides a record "Count(*)" for each record in a group.  However, I 
dont know how to set up the equation as the control is called the same for 
each grouping.  

How do I either use that control  or set up a calculated field to count 
total records and count the number of records with Sold="yes".  
0
Utf
2/25/2008 12:38:01 AM
access.reports 4434 articles. 0 followers. Follow

3 Replies
587 Views

Similar Articles

[PageSpeed] 21

In the Report Footer section, place a text box with this in its Control 
Source:
    = - Sum([Sold])

This works because Access uses -1 for True, and 0 for False. Hence summing 
the yes/no field gives the negative count of the number of Yeses.

-- 
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.

"Reici" <Reici@discussions.microsoft.com> wrote in message
news:E9F94781-E1DF-4C7B-84B6-615B022323E3@microsoft.com...
>I have a "yes/no" field called Sold.  I need to track and report what 
>percent
> of total solicitations sold.  I thought I could use the built in sum 
> feature
> which provides a record "Count(*)" for each record in a group.  However, I
> dont know how to set up the equation as the control is called the same for
> each grouping.
>
> How do I either use that control  or set up a calculated field to count
> total records and count the number of records with Sold="yes". 

0
Allen
2/25/2008 12:47:39 AM
Thanks Allen, but can I press you a bit further?  I need to report the % sold 
of the total.  Would that be = Standard(Sum([Sold]/+ Sum([Sold]))   Get the 
Idea?  I am not a good equation writer, need to study.

Thanks again


"Allen Browne" wrote:

> In the Report Footer section, place a text box with this in its Control 
> Source:
>     = - Sum([Sold])
> 
> This works because Access uses -1 for True, and 0 for False. Hence summing 
> the yes/no field gives the negative count of the number of Yeses.
> 
> -- 
> 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.
> 
> "Reici" <Reici@discussions.microsoft.com> wrote in message
> news:E9F94781-E1DF-4C7B-84B6-615B022323E3@microsoft.com...
> >I have a "yes/no" field called Sold.  I need to track and report what 
> >percent
> > of total solicitations sold.  I thought I could use the built in sum 
> > feature
> > which provides a record "Count(*)" for each record in a group.  However, I
> > dont know how to set up the equation as the control is called the same for
> > each grouping.
> >
> > How do I either use that control  or set up a calculated field to count
> > total records and count the number of records with Sold="yes". 
> 
> 
0
Utf
2/25/2008 1:12:00 AM
Total count is:
    =Count("*")

You can therefore use:
    = - Sum([Sold]) / Count("*")
assuming that the count is not zero.

-- 
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.

"Reici" <Reici@discussions.microsoft.com> wrote in message
news:EFF2A859-AF86-4753-ACDF-330486341293@microsoft.com...
> Thanks Allen, but can I press you a bit further?  I need to report the % 
> sold
> of the total.  Would that be = Standard(Sum([Sold]/+ Sum([Sold]))   Get 
> the
> Idea?  I am not a good equation writer, need to study.
>
> Thanks again
>
>
> "Allen Browne" wrote:
>
>> In the Report Footer section, place a text box with this in its Control
>> Source:
>>     = - Sum([Sold])
>>
>> This works because Access uses -1 for True, and 0 for False. Hence 
>> summing
>> the yes/no field gives the negative count of the number of Yeses.
>>
>> "Reici" <Reici@discussions.microsoft.com> wrote in message
>> news:E9F94781-E1DF-4C7B-84B6-615B022323E3@microsoft.com...
>> >I have a "yes/no" field called Sold.  I need to track and report what
>> >percent
>> > of total solicitations sold.  I thought I could use the built in sum
>> > feature
>> > which provides a record "Count(*)" for each record in a group. 
>> > However, I
>> > dont know how to set up the equation as the control is called the same 
>> > for
>> > each grouping.
>> >
>> > How do I either use that control  or set up a calculated field to count
>> > total records and count the number of records with Sold="yes". 

0
Allen
2/25/2008 1:40:08 PM
Reply:

Similar Artilces:

hey ya'll how do i auto sum a cell using the letter x instaed of .
hey ya'll how do i auto sum a colum using the letter x instead of using a number To count number of "x"es =COUNTIF(A1:A100,"x") Gord Dibben Excel MVP On Mon, 25 Oct 2004 14:25:04 -0700, jrod98 <jrod98@discussions.microsoft.com> wrote: >hey ya'll how do i auto sum a colum using the letter x instead of using a >number Gord answered but I couldn't resist asking what the sum of x and x is? Sinister Rod Serling music here. -- Don Guillett SalesAid Software donaldb@281.com "jrod98" <jrod98@discussions.microsoft.com> wrote in mes...

Error: "This record is read-only in Microsoft CRM"...
We have recently implemented MS CRM 3.0 with the CRM-Outlook client (on-line mode). Whenever I have Outlook open, I am frequently annoyed by the following error message dialog: "This record is read-only in Microsoft CRM. Changes will not be synchronized to the Micrsoft CRM system." Other team members experienced this same message initially, but then it stopped for them. However, I continue to get this message. Note the following: 1. I have CRM - Administrator rights 2. Using CRM 3.0 3. Using Exchange 2003 5. Installed on-line version of CRM-Outlook 4. Have checked/un-check...

paste text into a used cell
I need to move text from one row to a single cell that has been formatted to wrap text and repeat the process for other rows. Paste is not working. Please advise. When you copy and paste, you paste the format as well as the contents If you want to copy text to a cell that is formatted differently instead of paste, slelct paste special, values -- ElsiePO ----------------------------------------------------------------------- ElsiePOA's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=390 View this thread: http://www.excelforum.com/showthread.php?threadid=26962 ...

How can I use the exchange mode in Outlook? Can I use it in my PC
Can I use it in my PC? To use it I'm supposed to have Microsoft Exchange Server 2003, but what is that? Using the exchange mode in Outlook, if Outlook makes a security copy for my emails, that means that if an email has a virus, it gets saved in my PC? If by "Exchange Mode" you mean use an Exchange server in "Add / Edit Email Accounts" you can't - unless you are connecting to an Exchange server (which is often the case in larger companies). If you had an Exchange server, I suspect you would have been informed by your tech staff - so my guess is that the answer...

COUNTIF using amount of time
I have some data with various time intervals in mm:ss.ms and I want to use the COUNTIF function to count how many of them fall between 1-2 minutes, 2-3 minutes, 3-4 minutes, etc., but I keep getting a count of all the values. Am I comparing the time intervals wrong? For example, the data (in the A column) is: 01:19.666 01:13.287 01:28.308 02:29.872 05:36.658 01:01.770 My formula for counting those between 1-2 minutes: =COUNTIF(A:A,">=00:01.000&&<00:02.000") The returned value is 6. Any help would be appreciated, Dennis Dennis, For 1 to 2 minutes, try =SUMPRODUCT(...

MS Access Copy record to another table based on a form input 10-08-07
Hello. I have 6 Tables, all with identical fields. I want to copy a record from one table into another table, which table depends on what table is selected in a form. So if the table names are One, Two, Three, Four, Five, Six, and the record that I want to copy and insert into another table has been selected by a query in a form. I want the form to direct where to insert the table. So if the form has table Two selected (from the drop down list) then it should insert the record into table Two. If the form has table Four selected then it should insert it into table Four. The table the record is...

to use workday function in excel vba code
hi all, is there a way to use wrokday function in my worksheet controls.? i have a date time picker in my worksheet and a text box and a button. on click of this button i should get next desired date. like when i select a date from the calendar and click on button ther is onclick function in my macro. this onclick should calculate next desired date(assume if i choos 10/11/2005 and add 2 to this date i should get 12/11/2005) i know how to use workday function using a cell reference but................ i don't know how to put this into a vba code. if i use workday("10/11/2005&qu...

Using a sub procedure to change a subform property
I am currently using the following code to change the AllowEdits property on any form. The form calls the Sub procedure through a button's OnClick event. Sub UnlockForm(strFormName As String) On Error GoTo Err_unlockform If Forms(strFormName).AllowEdits = False Then Forms(strFormName).AllowEdits = True MsgBox "Form is now unlocked for editing." End If Exit_unlockform: Exit Sub Err_unlockform: MsgBox Err.Description Resume Exit_unlockform End Sub I am trying to call this Sub procedure from a button on a subform. I was going to us...

Counting days between dates
I am trying to calculate working days late or early between two dates using =NETWORKDAYS(A2,B2) If I have a due date of 24 Sept and I ship on 24 Sep then I am neither late or early so I am on time i.e. 0 days - but my formula returns 1! If I have a due date of 24 Sept and I ship on 23 Sep then I am shipping 1 day early i.e. –1 – but my formula returns –2 formula due actual days early late should be 24-Sep 24-Sep 1 0 24-Sep 25-Sep 1 1 24-Sep 23-Sep -2 -1 What do I need to do...

Use of Dialog
Hi all, I'm curious about use of excel dialog. What is for? and how to use it and what other benefits or advantages in using for common financial worksheet? Thank you. :) -- markuss ------------------------------------------------------------------------ markuss's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=34966 View this thread: http://www.excelforum.com/showthread.php?threadid=553085 Dialogs can make things easier for you and your end user. They can be used to pre-validate entered information, apply business rules to it before placing it onto a w...

Use Folders for E-mail
I organize my e-mail message into folder by client or topic. When I look at a message in the "sent items" folder it is shown under the name of the person to whom I sent it. However, when I drag that same item into a client or topic folder it is now shown under my name, apparently since I was the sender. Is there a way to force the dragged item to keep the same name as it had in the sent items folder? Chuck Humphrey All you have to do is switch the view on the client or topic folder to "Sent To" -- that's the view used in the Sent Items folder by default. -- Jocelyn...

Inserting blank rows plus using Sum
I have a list of stocks sorted by date. When the stock symbol changes, I need to insert 2 blank rows and AutoSum columns 2, 6,7, and 8 of the group and display them on the first of the 2 blank rows, leaving a blank row before the next group. Symbol Qty Price Action Name US Trade Date Amount Commission Fees MSFT -210 28.95 Sell 2/13/2008 6079.43 -0.07 -0.07 MSFT -790 28.95 Sell 2/13/2008 22863.24 -7.26 -0.26 -1000 28942.67 -7.33 -0.33 C -700 22.16 Sell 3/4/2008 15511.82 -0.18 -0.18 C -300 22.15 Sell 3/4/2008 6637.92 -7.08 -0.08 -1000 22149.74 -7.26 -0.26 MSFT -500...

Not able to send mails using Ms Outlook 2000
Whenever i try to send mails using Ms Outlook 2000 i get this error SMTP-MAIL: died on signal 11', Port: 25, Secure(SSL): No, Server But i am able to send the same mails with same id configured with Outlook Express 6 and 5 both What will be the solutions to this Regards Shardul What you try to do is to export your mailaccount setting from Outlook exprees into Outook 2000 >-----Original Message----- >Whenever i try to send mails using Ms Outlook 2000 i get >this error >SMTP-MAIL: died on signal 11', Port: 25, Secure(SSL): No, >Server > > > >But i a...

De-Duping Customer Records
Hi, I'm new to the system but searched in the Help as well as this newsgroup. We have several customers that have called in and the clerk did not check for a previous record, created a new one. Now I have many many customers with duplicate records but not the purchase history. Is there a way to de-dupe the records and keep the history? -- Thanks,Jan There are no tools for this built into the system. This can be done, but it may be painful. If you are doing On-Account Tenders, it will be especially painful, as you will need to merge both the purchase history and account activ...

Choosing unlocked new record
I am trying in a multi-user environment to have each user able to add data to the next available, unlocked record. I am thinking this is a query, but not sure. I am set to share the database with record-level locking. I have a query that finds the next available record, but when another user runs the query, it chooses the same record even though another user is entering data. Obviously it creates an error later, but I want to avoid the error altogether by having each sequential user that runs that query to enter data on the next, unlocked (unused) record. Can anyone help? I am no...

Ignore table formatting when importing data using OLEDB
I am using Excel 2010. I want to write a macro in a 'consolidate' XLSM file that will query another selected XLSM file and import data from one of the range names in that file, into the 'consolidate' file. When the import occurs, it formats the imported table using the default Excel 2010 table style (see screenshot), however i don't want to format the cells - i just want to import raw data and leave the cells formatted as they are in the 'consolidate' XLSM file. The PreserveFormatting property has no impact, as the damage is already done once the query is added. ...

Restrict user access to their own records and associated entities
Hi all, I have a user - and they can correctly 'see' only their 'Contacts' But I have created other entities - lets say a 'product' that all users can see. And as each 'Contact' can be interested in more than one 'product' and each 'product' can have more than one 'Contact' interested I've created another entity called 'ContactProducts' to link the two. It basically has only two fields, a referential 'lookup field' to each of 'product' and 'Contact. The entities ('product' and 'ContactProduc...

Using Delete Dialog
Hi NG, I try to create a Custom Web Page, which will insert to the CRM by NavBar. This is working fine. Now the should be a seperate Button, for deleting. I found a way to call the standard delete dialog with: window.showModalDialog("/grid/cmds/dlg_delete.aspx?iObjType=112&iTotal=1&sIds=") My Problem now: How can I set the id for the Case which should deleted? When I set the argumten like: window.showModalDialog("/grid/cmds/dlg_delete.aspx?iObjType=112&iTotal=1&sIds=", "{C1B55815-104E-DB11-9C89-0003FFA2910B}") I get the error: "XML Parse...

Picture viewing in outlook 2000 using XP
Using outlook 2000 standard (no updates). XP Home with all SP's and updates. I view picture attachments using the default picture and fax viewer. When the last picture is viewed. I click next. Instead of restarting the same file, another file appears. It is always the same picture file that appears regardless of which email I am viewing. I have removed the intruder picture file from the C drive(MY Pictures) and from outlook (sent items). This intruder still happens. Any ideas ??? Metro Pete <Metro Pete@discussions.microsoft.com> wrote: > Using outlook 2000 standard (no u...

Code 17 Dup Record on file FA_Retirement_Mstr
V. 7.00g using the FA module only (not interfacing with GL) Retired 600+ assets. Un-retired to correct proceeds. Re-retired and the first 390 went fine, now every asset we try to re-retire gives us this error. What can we do? Thank you, Robert ...

what pdf software do you use to redact text?
There are a lot of software packages that will converted back and forth between Word and PDF documents - however, I need to know if anyone uses a good converter that will allow true redactions that cannot be seen in the PDF version as well as remove metadata from the output document. Thanks!! CH Do your redaction in Word. Output the document to a graphics format such as TIFF using SnagIt's 'printer' driver, then print the results to PDF using Acrobat to combine multiple pages. That should stay redacted :) -- <>>< ><<> ><<> ...

how can I use a picture to fill text?
I am trying to use a big balloon font, and have a picture as the fill color. How do I do this? Create the phrase with WordArt. Fill, Fill effects, Picture tab. -- Mary Sauer http://msauer.mvps.org/ "lee254" <lee254@discussions.microsoft.com> wrote in message news:0EA083FF-61A9-4DF7-9D65-6CAE1A3B02E5@microsoft.com... >I am trying to use a big balloon font, and have a picture as the fill color. > How do I do this? ...

Mysterious question mark (?) when using OWA
Mysterious question mark (?) when using OWA It seems that my users whom use OWA to send email always seem to send along an "?" question mark at the beginning of their email messages. I am trying to find out why that is happening and how to go about eliminating it. If clients are XP SP2, check kba 883543 and apply the hotfix. Pe-XP SP2 clients: reinstall the S/MIME control from OWA - options. -- Bharat Suneja MCSE, MCT www.zenprise.com blog: www.suneja.com/blog ----------------------------------------- "Mr. Backup" <backup@yahoo.com> wrote in message new...

Ploynomial Trendline Equation Changes After Copy Paste
In Excel 2007 if you create a XY Scatter Plot and then add a polynomial trendline to the plot, display the equation, then try and copy/paste the chart to a Word 2007 document the trendline equation in the chart drops one of the orders. If you right click and select Format Trendline, it still lists the correct order equation, but it does not show. If you delete the trendline, and then add it again in the pasted copy of the chart the problem is fixed. I have reproduced this using 2,3,4,5, and 6th order polynomial equations. I was unable to reproduce this behaviour. Can you give more ...

Calculate change from record to record
Dear All, in my database, I am recording projects in different categories. I have now created a crosstab query that sums up all project per category and year, including a total number of projects per year. How can I also show, how the total number is changing compared to the previous year? Basically, I am looking for something like (No of projects of previous record's year-No of projects of current record's year)/(No of projects of current record's year) at the end of each record. A B C Total Change Y1 3 2 5 10 0 % Y2 1 7 6 ...