Count and percentage of specific records

[plaguna]

This is an example of what I have so far in my Access Report.

Internet Source Salesperson... …Appointment Kept
AutoTrader … yes
AutoTrader … No
AutoTrader … No
AutoUSA … Yes
AutoUSA … Yes
Ebay … No
mbUSA.com … yes
Others … No
Others … No
Code =Count(*) =Count(IIf([Appointment Kept]="Yes",0))
Count 9 4

The Idea is the following:
How can I count how many [Appointment Kept]=“Yes” (1 ) Only for [Internet Source]=”AutoTrader”,( 3) in order to get a percentage between them (1/3). In other words, the percentage of all “yes” of only AutoTrader
Thank you for any assistance

plaguna

[ADezii]

Assuming your Table Name is tblAuto, and the [Appointment Kept] Field is a Yes/No Field, Copy and Paste the following Expression into the Control Source Property of a Text Box on your Report, preferably in the Report Footer:

=Format$(DCount("*","tblAuto","[Appointment Kept] = True And [Internet Source Salesperson] = 'AutoTrader'")/DCount("*","tblAuto","[Appointment Kept] = True"),"Percent")

P.S. - Substitute your Table Name for tblAuto.

[plaguna]

The code you gave me gives me an #error output

=Format$(DCount ("*","BDC Information","[Appointment Kept] = 'YES' And [Internet Source] = 'AutoTrader'")/DCount("*","BDC Information","[Appointment Kept] = 'YES'"),"Percen t")

The table that I sent before didn't show clearly. This is a good one:

[Internet Source]......…[Appointment Kept]
AutoTrader.………… ………YES
AutoTrader.………… ………YES
AutoTrader.………… ………NO
AutoUSA.………………… ..YES
AutoUSA.………………… ..YES
Ebay…………………….…. .NO
mbUSA.com.…………… …YEs

Code for Count:
=Count(IIf([Appointment Kept]="YES",0))
Total Yes = 5

=Count(IIf([Internet Source]="AutoTrader",0 ))
Total AutoTrader = 3

I have no problem so far.

Now, how can I count the [Appointment Kept] “Yes” of “AutoTrader”. I want to count the two “Yes” out of the three “AutoTrader” (once I figure this out first, I can get the percentage later)

I tried this code, and the ouput is -1 if there is any “YES” in the field, and an output of 0 if all are “NOs”
Code:
=Count(IIf([Appointment Kept]="YES",0)) And Count(IIf([Internet Source]="AutoTrader",0 ))

Also I tried part of your code and It didn’t work
Code:
=Format$(DCount ("*","BDC Information","[Appointment Kept] = 'YES' And [Internet Source] = 'AutoTrader'"))

Any help will be greatly appriciated

plaguna

[ADezii]

The code you gave me gives me an #error output

=Format$(DCount ("*","BDC Information","[Appointment Kept] = 'YES' And [Internet Source] = 'AutoTrader'")/DCount("*","BDC Information","[Appointment Kept] = 'YES'"),"Percen t")

The table that I sent before didn't show clearly. This is a good one:

[Internet Source]......…[Appointment Kept]
AutoTrader.………… ………YES
AutoTrader.………… ………YES
AutoTrader.………… ………NO
AutoUSA.………………… ..YES
AutoUSA.………………… ..YES
Ebay…………………….…. .NO
mbUSA.com.…………… …YEs

Code for Count:
=Count(IIf([Appointment Kept]="YES",0))
Total Yes = 5

=Count(IIf([Internet Source]="AutoTrader",0 ))
Total AutoTrader = 3

I have no problem so far.

Now, how can I count the [Appointment Kept] “Yes” of “AutoTrader”. I want to count the two “Yes” out of the three “AutoTrader” (once I figure this out first, I can get the percentage later)

I tried this code, and the ouput is -1 if there is any “YES” in the field, and an output of 0 if all are “NOs”
Code:
=Count(IIf([Appointment Kept]="YES",0)) And Count(IIf([Internet Source]="AutoTrader",0 ))

Also I tried part of your code and It didn’t work
Code:
=Format$(DCount ("*","BDC Information","[Appointment Kept] = 'YES' And [Internet Source] = 'AutoTrader'"))

Any help will be greatly appriciated

plaguna

The code you gave me gives me an #error output

=Format$(DCount ("*","BDC Information","[Appointment Kept] = 'YES' And [Internet Source] = 'AutoTrader'")/DCount("*","BDC Information","[Appointment Kept] = 'YES'"),"Percen t")

That is not the code I gave you. The code I gave you, plus the revision on the Table Name because of the Space, is:

=Format$(DCount("*", "[BDC Information]", "[Appointment Kept] = True And [Internet Source Salesperson] = 'AutoTrader'") / DCount("*", "[BDC Information]", "[Appointment Kept] = True"), "Percent")

P.S. - This Expression goes into the Control Source of a Text Box. The [Appointment Kept] Field is a Yes/No Field.

[plaguna]

Your expression makes sense but for some reason It doesn’t work. I’m still having an #error output. Is there another way to do it?

[ADezii]

Your expression makes sense but for some reason It doesn’t work. I’m still having an #error output. Is there another way to do it?

1. Is your [Appointment Kept] Field a Yes/No Field?
2. Copy and Paste your Expression as it currently exists in the Control Source Property of the Text Box.

[plaguna]

Yes, [Appointment Kept] Field is a Yes/No Field. And, I did copy and paste it into the Control Source Property of the Text Box. Also, I tried replacing other fields with different data types, modifying your expression, and I get the same result: #Error.

plaguna

[Stewart Ross]

Hi Plaguna. ADezii has asked more than once if your [Appointment Kept] field is a Yes/No field - one in which there can only be two values, True and False, or Yes and No (representing True and False). You have affirmed that it is - but your posts suggest to me that it is a Text field containing the text values 'Yes' or 'No'.

These are NOT the same as the logical values Yes and No, which in Access are constants - the constant Yes equating to -1 and the constant No equating to 0. Two possibilities: your [Appointment Kept] field really is a Yes/No field, in which case using [Appointment Kept] = 'Yes' in the Dcount is an error, or it is a text field, in which case trying to use [Appointment Kept] = True in the DCount is an error.

It will help us greatly if you could please confirm what type the field really is. ADezii's suggested code should work fine if your Yes/No fields really are boolean Yes/No fields, and not text.

I attach a screenshot of a single Yes/No field as you would see it in table design view so you can be certain whether or not it really is a yes/no field.

-Stewart

[plaguna]

Thank you guys for helping,
Yes, [Appointment Kept] field is a Yes/No Field. Probably, I was not clear that I changed it. It Is not a Text Data Type any more. Even though, I still have the #Error output

plaguna

[Stewart Ross]

Ok, so it was text (as I thought) but now it isn't. Yet you were referring in what you posted most recently to a text 'Yes' value, NOT the boolean constant Yes.

Forgetting the percentage for a moment, please advise what happens when you try just one DCount from ADezii's post 4 as the rowsource for your textbox:

=DCount("*", "[BDC Information]", "[Appointment Kept] = True And [Internet Source Salesperson] = 'AutoTrader'")

Please copy and paste this exactly as shown - DO NOT PUT 'Yes' IN PLACE OF True as you appear to have done at other times. This should simply return the count of the number of Autotrader listings where appointment kept is true.

-Stewart

[plaguna]

I just copied and pasted as you said. I checked every single character, and I still have the #Error output

[ADezii]

I just copied and pasted as you said. I checked every single character, and I still have the #Error output

plaguna, there seems only 1 Option left at this point. If I provide my personal E-Mail Address in a Private Message, would you be willing to send me the Database, or a subset of it, as an E-Mail Attachment?

@Stewart, thanks for the assist in this Thread.

[plaguna]

plaguna, there seems only 1 Option left at this point. If I provide my personal E-Mail Address in a Private Message, would you be willing to send me the Database, or a subset of it, as an E-Mail Attachment?

@Stewart, thanks for the assist in this Thread.

Sure, that will be great. But, do you thing if I get your email address, I can sent it to you tomorrow night? My company is closing in a few minutes and I won’t be able to do it until then. If you don’t mind. That will be wonderful, because I’m trying to do this for too long.

I really appreciate it

plaguna

[ADezii]

Sure, that will be great. But, do you thing if I get your email address, I can sent it to you tomorrow night? My company is closing in a few minutes and I won’t be able to do it until then. If you don’t mind. That will be wonderful, because I’m trying to do this for too long.

I really appreciate it

plaguna

I'll send it to you in a Private Message in 5 minutes (6:45 P.M.).

[plaguna]

I'll send it to you in a Private Message in 5 minutes (6:45 P.M.).

I sent you two files: The file that I'm working on and a test file. I'm sorry for the delay. thank you in advance

plaguna