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'"),"Percent")

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'"),"Percent")

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'"),"Percent")

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

[ADezii]

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

The answer to your dilemma is quite obvious. In info_test.mdb, where the code is functional, you have an Info Table consisting of the [Source](TEXT 255) and [App Kept](Yes/No) Fields. In your Production Database (BDC_Information.mdb), this Table (Info Table), does not even exist. The Expression used for the Control Source of the Text Box relies entirely on the Fields within the Info Table (No Table ==> No Fields ==> Error!).

[plaguna]

You are right, It was a miss spelled. Instead of “BDC Info Table” It was “DBC Info Table” and of course It couldn’t fine It. Now I don’t have a #Error message anymore, but the output is not correct. It doesn’t count correctly when the report is by groups. Even if the expression is in the Report Footer.

plaguna

[plaguna]

Please let me rephrase the last part of my last post. The expression I got from ADezii and Stewart Ross Inverness does count only in the Report Footer. In fact, It works beautifully, which is great. But, when I try it in the Group Footer, It gives Grand totals. (the same output as the Report Footer) How can I get the total for each group?

Thank you
plaguna

[ADezii]

Please let me rephrase the last part of my last post. The expression I got from ADezii and Stewart Ross Inverness does count only in the Report Footer. In fact, It works beautifully, which is great. But, when I try it in the Group Footer, It gives Grand totals. (the same output as the Report Footer) How can I get the total for each group?

Thank you
plaguna

plaguna, you change the Data Type of a Field and don't tell us, the Table used in the Expression to calculate the Grand Totals contains References to a Table that doesn't even exist in the Production Database, and now you tell us it is not 'Grand Totals' that you are looking for but 'Group Totals'. To be frank, I'm almost at the ropes end on this one. I'll make one more attempt to solve this problem given the continuously changing Parameters.

[ADezii]

Please let me rephrase the last part of my last post. The expression I got from ADezii and Stewart Ross Inverness does count only in the Report Footer. In fact, It works beautifully, which is great. But, when I try it in the Group Footer, It gives Grand totals. (the same output as the Report Footer) How can I get the total for each group?

Thank you
plaguna

Here is the Final Solution which is rather far removed from what was initially envisioned. Group Percentages can now be calculated as requested. The following Expression was placed in a Text Box in the Location ID Group Footer of the BCD By Internet Source Report. I will return the Production Database back to you with all the corrections via my Private E-Mail Account. Good Luck.

=Format$(DCount("*","DBC Information Query","[Internet Source] _
= 'AutoTrader' And [Appointment Kept] = True And [Location ID] = " & _
[Combo23])/DCount("*","DBC Information Query","[Internet Source] _
= 'AutoTrader' "),"Percent")

[Stewart Ross]

Hi Plaguna. I would ask that you reflect on ADezii's comments in his most recent posts. ADezii has gone the extra mile in assisting you - despite the changes in actual against stated conditions between posts which make it very hard to assist you from afar.

Words are very important to us - all we have to go on is what you tell us, and there is a world of a difference in a Grand Total and a Group Total. Similarly, we expect that users do not present us with SQL that is stacked with errors that should not be there - such as references to non-existent tables which basic testing would have revealed straight away. To expect us to work with such flawed material is simply inappropriate, whatever the level of user experience involved.

I trust that you will review the revised database provided by ADezii and recognise that further changes or developments are now up to you, given the base of the excellent support which you have received in this thread.

Kind regards

Stewart

[plaguna]

You guys are right. I reviewed all the previous posts, and I see some inconsistencies from my part. I agree with Stewart saying “Words are very important to us - all we have to go on is what you tell us”. I’m sorry that I fixed errors and made some changes along the way after the suggestions you tell me to do, and I didn’t let you know the updates. Now I understand that this way, you can get crazy trying to figure it out what is going on. Next time, I’ll consider more of what you need to know to help us, instead of thinking only about fixing the problem.

I always appreciate your help

plaguna

[plaguna]

I recreated this database file from scratch. The first one had too many inconsistencies and, was hard to follow when errors occurred.
I read about DCount () and, found that this function is to return the number of records specified, but it is good to use it in Queries. It is not recommended to use DCount() in a Report if you want to calculate from records in your report or report section.
Instead, I got this expression which works perfectly in a Group Totals and Grand Totals:
Code:
=Sum(Abs([Appointment Kept] = True And [Internet Source] = "AutoUSA"))

plaguna

[ADezii]

I recreated this database file from scratch. The first one had too many inconsistencies and, was hard to follow when errors occurred.
I read about DCount () and, found that this function is to return the number of records specified, but it is good to use it in Queries. It is not recommended to use DCount() in a Report if you want to calculate from records in your report or report section.
Instead, I got this expression which works perfectly in a Group Totals and Grand Totals:
Code:
=Sum(Abs([Appointment Kept] = True And [Internet Source] = "AutoUSA"))

plaguna

Nice solution, thanks for sharing it with us.