Query to perform percentage

[mari_hitz]

Hi everybody!

I have a little issue with a query I would like to perform: I have a table with a group of people to which I should send two types of communications, there is a Column that indicates if the person should receive the first communication or the second . I have managed to make a "Count" that returns me how many persons should receive the first communications and how many the second.
However, what I am looking for is to create a report or a table that can return me how many persons should receive the first communications and the percentage, and so on, like the table below.

First Communications%Second Communications%Business travelers343%744%Complex Cases457%956%Total7100%16100%


I have tried to obtain the percentages based on examples found in the web, however the query returns me error, and I guess it is because I do not have numbers, I have comments that should be counted. I am not looking for the exact table of above, I will be more than happy to get the percentage hehe.

Thanks in advanced!

 

[bob fitz]

I have managed to make a "Count" that returns me how many persons should receive the first communications and how many the second.

Can you show us the SQL statement of this query.

 

[mari_hitz]

Hi Bob!

The SQL is the following:

SELECT Count(Business.Comments) AS Total1BV
FROM Business;

 

[bob fitz]

Hi Bob!

The SQL is the following:

SELECT Count(Business.Comments) AS Total1BV
FROM Business;

Surely that will only give you one count, not "a "Count" that returns me how many persons should receive the first communications and how many the second."

 

[mari_hitz]

Thanks Bob!

Yes, it only returns me one count, I could not make a single query to return me the count for the two comments.

I am sorry for my ignorance but my Access knowledge is pretty basic.

Is it possible to make a single query to return me the count of both comments? Even more, is it possible to obtain a query that returns me the count and the percentage?

Thanks in advance for all your help, you are relly kind

 

[bob fitz]

Try this:
SELECT Business.Comments, Count(Business.Comments) AS CountOfComments
FROM Business
GROUP BY Business.Comments;

If this gives the count as required we can work on the %

 

[mari_hitz]

Bob! You are a genius!!!

That worked, it returned me the firgures of each communication.

How do I obtain the percentages of this figures?

Sorry for asking this, I am really thankfull for all your help!


Thanks and regards!

 

[bob fitz]

Try this:
Save the first qry as
qryBusiness

Then create a new query with:
SELECT qryBusiness.Comments, qryBusiness.CountOfComments, [CountOfComments]/DCount("Comments","Business")*100 AS PC
FROM qryBusiness;

 

[mari_hitz]

Thanks for all your help! I finally obtained it. I am really happy.

Can I ask you one more question? I am really embarrassed that I did not bring this up before, however, I would like to know if it is possible the following: this database contains all the people in my company that has travelled abroad, so we have to send them two different types of communications like I have said before to ask for information. In addition, this persons can travel for Business or Work purposes, is it possible to add this category too? I mean, I have added the category to the query and it works just fine like I would like to see it in a table where the rows are the type of travel and the columns are the count of commnets and the respective percentage?

Thanks and regards!

 

[Alansidman]

Cross Post

http://www.accessforums.net/showthread.php/23739-Query-to-perform-percentage?p=114341#post114341

 

[mari_hitz]

Thanks Alan for your post. I will close the other thread, the reason why I posted my issue in two places was because I needed an answer. Here Bob could help me properly, however I did not find a solution for my issue so I will continue to ask here and not in the other place.

Continuing on what I was mentioning before, I have managed to do the percentage to work just fine how Bob told me, however I have to perform the Count of comments but make some filters before so I have a new query with all the filters and I did a query just like Bob teached me with the Count (named it qrynoncomplex) and when I did the query for the percentage it seems not to work. It returns me values that are incorrect and not the percentage. I have 2475 figures, and for example one of the counts is 152 and the Percentage field returns me 3800 instead of the percentage 6,12.

The code is the following:

SELECT qrynoncomplex.Comments, qrynoncomplex.CountOfComments, [CountOfComments]/DCount("Comments","qrynoncomplex")*100 AS PC
FROM qrynoncomplex;

Do you have any idea why this is happening?

Thanks!

 

[Alansidman]

I would do it as an aggregate query. You need to have a group by clause. Look at this video. It will be helpful. You are close.

http://www.datapigtechnologies.com/flashfiles/percenttotal.html

Alan

 

[mari_hitz]

I would like to thank everyone here because I have managed to obtain what I was looking for!

One last question (please don't kill me). I have performed some reports, where I have put an analysis, in which I would like to include theree text boxes which each one sums different information located in other text boxes located in other two sub reports included in my main report. I tried to do this with control source, and there I can find the text boxes that are located each on the report footer of the subreports.
How can I make to point the access to sum taking into count this text boxes?