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
542 Views

Similar Articles

[PageSpeed] 31

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:

replying to a forwarded email using another address
Hi, I get my email forwarded from my website to Outlook. When I reply from Outlook the address shows my computer address. Is there anyway I can configure Outlook so that the address shows my website address? ...

Credit Card Payments
I've been using MM for years now but just noticed something in MM 2005. If I go to reports and click on "Monthly Income and Expenses" the report doesn't list credit card payments even if I select "Include transfers..." in Customize Report. It seems that the report generator doesn't know about the special Transfer and Credit Card Payment categories. It only knows about the expense category "Credit Card Payments/Transfers". My question is does anyone actually use this expense category? It seems like it could cause some unfortunate side effect...

How can I use Outlook on both my laptop and desktop? I have cable.
and Netgear wireless version 4.0 Dear Bafflegab, have a look on this site, may it helps: http://www.slipstick.com/outlook/share.htm -- Oliver Vukovics Groupware for Outlook without Exchange: Public ShareFolder Notebook Synchronisation for Outlook: Public SyncTool New Domain: www.publicshareware.com "Bafflegab" <Bafflegab@discussions.microsoft.com> schrieb im Newsbeitrag news:59DE2C4E-ADC5-403C-AE3B-7DE1F9AF90C6@microsoft.com... > and Netgear wireless version 4.0 Thanks for the recommendation. It's a great site and one I never would have found on my own. I dow...

count between a date range
Hi, I want to be able to count between a date range and return the value yes. I will show you the formula I currently have: =IF(M2>=DateCell-365,"Yes","No") This gives a yes if the date is greater than the date in M2 minus 365. This however cincludes all the dates beyond M2 which isnt what I want. I want the dates upto M2. If that makes sense? Any help would be greatly appreciated Thanks Try.. =IF(M2>=datecell-DATE(1,0,0),"Yes","No") -- Jacob (MVP - Excel) "Dave" wrote: > Hi, I want to be able to count be...

Is it possible to use a vlookup looking up peoples names
I would like to lookup a list of names but i have to have a set o consecutive numbers before it. Is it possible to lookup names that ar not consecutiv -- Message posted from http://www.ExcelForum.com If you set the last argument of the function to be an "exact" match, the lookup column does *not* have to be in order. =Vlookup(value to find,array or list of data,column to return,exact match or not) Exact Match = False OR Zero (0) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! =======================...

how do i print an A3 poster using 2 A4 sheets
When I select A3 portrait as the page size for a poster how do I get it to print on two A4 sheets. Publisher wants to print on 4 tiled sheets When tiling, Publisher will compute space for overlapping; when selecting your Page setup select Horizontal. Also, you'll need to either crop a bit or resize your poster a bit to have it fit on two A4 sheets. I have to thank you for giving me the nudge to look for and find a chart of paper sizes. http://www.dpandi.com/paper/index.html -- Don - Publisher 2000� Vancouver, USA "TedM" <TedM@discussions.microsoft.com> wrote in...

Using Matrix Items
Someone...want to tell me how or if I can default which item is first (selected) when a matrix item is scanned? Is there a way to set this up? -- Thank You Vince :) What are the dimensions you use? Rob "Vince" <Nagarra@yahoo.com.(donotspam)> wrote in message news:E001F1F0-3002-4E1B-8E75-8E62FC6EC01C@microsoft.com... > Someone...want to tell me how or if I can default which item is first > (selected) when a matrix item is scanned? Is there a way to set this up? > -- > Thank You > Vince :) Using Lot Matrix, what I have is a six pack of coke or a sin...

How to leave a copy of messages on server using Outlook 2000 and 2002
I am using Outlook 2000 with our company exchange server, how do I setup outlook so it leaves a copy of messages on the server so I can use multiple computers to access email. I have spent an hour but can't the setting to do this. thx Rajesh By default, when you set up Outlook to connect to an Exchange mailbox, it opens the full mailbox (MAPI connection) - doesn't download mail (POP connection). So unless you are using POP mail, or a PST file for delivery, your mail should stay in the Exchange mailbox (which is the best place) and you can access it from multiple computers. Proba...

How to make SOP Invoice look good using Line Wrap?
I am using Report Writer to modify the SOP Blank Invoice Form. I used the RW_ParseString function to wrap the item description to two lines. But since we also use serial numbers for some parts, there's a gap between the lines. This looks confusing on the printout because you have Description then a gap then the Serial Number and then the next Description without a gap in between. I tried adding horizontal lines under the SerialNumber row but that still results in a pretty ugly printout. I'm looking for suggestions to make this look better. Thanks, David I have used additional...

Exchange delegation using group doesn't work?
Hi, I am running Exch 2k3 SP1 in Windows SBS 2003. I tried to delegate Exch to user. What I did: I created a group called: Junior Admins I delegate at the Exchange top level the Junior Admins group to be Exch Full Admin. I assigned user: Sam to Junior Admins I use AdminPak.msi for Sam to control Exchange from her PC. Sam can get ESE on her PC (mmc), but can't open the server folder due the rights. I've also assigned Sam in the local administrator of that workstation. Things work if I assign Sam directly in the Delegation Control from ESE. How can I delegate control via group? ...

Remove records from lookup
Not sure if this is possible but I would like to remove all records except the account records from the look up records dropdown menu of the appointment entity. The associated schema is regardingobjectid and the label is regarding. I see the other entities listed under relationships but there is no option to delete. Is there a way to create a new lookup and have it added to my appointment form? Thanks. There is no supported way to change this. The system is setup to allow you to set appointments that are related to specific records (Opportunities, Quotes, Cases, etc) and it will then ro...

Import datetime field using CRM 4.0 Data Migration
Hi Greetings all :). I am trying to migrate a datetime field using CRM data migration manager. The date time settings in CRM under settings - system settings and under personalize workplace are the same. I have also changed my data file to be dd/mm/yy hh:mm:ss:mss. I keep getting invalid format error. I am really not sure what other format I could try. I look forward to some suggestions please. Regards CRM 2008 If you read the ReadMe or help about Data migration then it specify the correct format(s) for importing date and time variable in CRM. ------ Aamir Blog = http://mscrmsupport...

anyone know if it'spossible to de-dupe a mailing list using excel
Hi I've merged 2 mailing list files and have noticed there are a few duplicate records.Is there a way of de-duping in Excel?? Hi, Look CPearson web, go to the middle of the page where it says delete duplicate rows http://www.cpearson.com/excel/deleting.htm "Jonathan6315" wrote: > Hi I've merged 2 mailing list files and have noticed there are a few > duplicate records.Is there a way of de-duping in Excel?? Check out this option. --Select data range and from menu Data>Filter>Advanced Filter>Copy to another location.. --In 'copy to&#...

making a decision tree using excel!
hello all, i`ve been trying to make a decision tree using excel, the problem i encounter is that i wish to put information in to b11 which is related to information in b3, the information in b3 is ether 0 or err and is based on a "y" or "n" being entered, does anyone know which formula to use which will print a true of false statement in b11 Hi Andrew, If you mean print "True" or "False" or your error b11: =Left(b3)="y" So you can use YES, Yes, yes, Y, y and get True everything else will show False Or to treat the error in ...

Use Data from one sheet to populate Purchase Order on another sheet -Excel 2002
Hello, Wonder if this is possible.. Sheet1 - has a 'list' of parts that need to be ordered - their description, qty, type of material, part number etc Sheet2 - I want to create a requisition from those items listed on Sheet1 based on type of material... Basically need to take the data from Sheet 1 (can be 50 - 200 items/parts) keyed in by designers and create a purchase order requisition for our vendors/suppliers based on the type of material. Is there a way to extract.. say. material is H20.. and of them 50 items keyed in.. 10 of them are H20.. so that I can fill in the qty, descr...

Counting # of events per day`
I have a file with the following format col a event name col b event date (e.g., 9/01/2006) col c event day of week (Mon, Tu, etc) col d, etc other info There are about 1600 of them. I need to get the following info: Number of days throughout the year with 0, 1, 2, 3, or 4 or more events per day Similar results for Mondays, Tuesdays, etc. Any suggestions on how to go about doing that? Thanks Hi Elliott Create a Pivot Table. Data>Pivot Table>Next>Mark the range of your data>Next>Layout Drag Event day of Week to Column Area Drag Event Date to Row area Drag Event Name to...

RE: Use this critical package for Internet Explorer
--hmrlmpxejw Content-Type: multipart/related; boundary="enmgzrgmzgzwwvlg"; type="multipart/alternative" --enmgzrgmzgzwwvlg Content-Type: multipart/alternative; boundary="hsqxuryiiedx" --hsqxuryiiedx Content-Type: text/plain Content-Transfer-Encoding: quoted-printable MS Partner this is the latest version of security update, the "October 2003, Cumulative Patch" update which eliminates all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three new vulnerabilities. Install now to help protect y...

limit create window/control max count?
Hi all. I am testing a create control. But I want create many control (more 10000) in dialog. I test dynamic create CButton. It's created about 9800. I want work. 1. have a two dialog.(A, B) 2. A dialog has 40 controls(Button, Spin, static). 3. B dialog has contain A dialog. A dialog is more than 200. 4. create A dialog dynamic.(Using A->Create(IDD, this)) and attach B dialog. A dialog is modaless. 5. But not create about 190. dialog create function return 0. thank for read. Have a nice day. You need to redesign your system, IMO. -- - Mark Randall http://zetech.swehli.com...

Counting Texts
If cell A1 contains a paragraph of texts and if I want to count just letter "W"s (Upper or lower case), How can I achieve this? Example: A1 contains "How now brown cow" the formula should return "4". Thank you. Write a macro and use the VBA functions Instr(strName, "w") together with Split(strName, "w") and count the number of times that it finds "w" or "W" Chris "Keith" <Keith@discussions.microsoft.com> wrote in message news:CC60AA13-B569-4C55-BDC7-1B6295CDAFB9@microsoft.com... > If cell A1 co...

using SetTimer
I use extensively the timers in my View mfc application but I find the documentation is not absolutely clear. I use the version with 3 paramteres - the last one is NULL which indicates using the View OnTimer routine. In the documentation it says to use KillTimer with the first parameter being used in the SetTimer function and in some other palce to use it with the return value of SetTimer. Can someone clarify that plaeas? Thanks Rick. "Rick" <rick@cortex.com> wrote in message -> I use extensively the timers in my View mfc application but I find the > documenta...

insert logo using AUTOCORRECT
Have been trying to insert my company's logo using AUTOCORRECT. It works like a charm in WORD. It doens't work in Excel. ex. when I type "lg" these characters should be replaced by my company's logo. Does anyone know how to do this in Excel? -- digicat ------------------------------------------------------------------------ digicat's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=14920 View this thread: http://www.excelforum.com/showthread.php?threadid=497273 ...

ACCESS 2000 is deleting records
Just yesterday I converted an Access 97 database to Access 2000. Have a large problem I need help with. After converting the DB I "split it" in order to use it as a backend, same way it was in Access97. After converting the database to Access2k we began to have a problem with Access2k deleting records. it deleted a total of 4 out of 62 records. the records are not together ( record #289365 then 289048 then 289128 then 289178 then this morning 289405) these record are auto-numbered incerement of one. They were entered by different people so I know its not just an operator probl...

Using Excel to design business forms
I have been using excel hit and miss to design business forms. I have been pretty successful but in many places I have found myself doing many inelegant things to get stuff to layout. Is there a good place to start looking to see how this is done properly, as I have seen many well done forms produced in excel. ...

number in system using arrivals and departures
Is there and easy way to figure out the total number of persons in a system if have access to the arrival and departure times. I have tried to use counts with if statements, but I keep getting the number 1. I know this is not correct. Is there any special macros or functions Thanks Hi you may provide some more details about your data. Could you post some example rows (plain text - no attachments please) and describe your expecte result -- Regards Frank Kabel Frankfurt, Germany megv wrote: > Is there and easy way to figure out the total number of persons in a > system if have access...

Retreiving Xml child nodes using DataSet and XmlDataDocument
I am loading an Xml file from a sql server. I have one field that contains child nodes stored as text. The problem is, when I try to imort the dataset into a XmlDataDocument, it loads the whole thing as a text element. (with all of the special characters converted) The best solution I've found so far is to do a string replace on all of the special characters. Like so: DataSet ds = new DataSet(); ds.EnforceConstraints = false; SqlDataAdapter da = new SqlDataAdapter(query, connStr); da.Fill(ds, "Keywords"); XmlDataDocument xmlDoc = new XmlDataDocument(ds); string s = xmlDoc.S...