Multiple Totals

[james28]

Hello all,




I am a bit baffled and hoping that someone can lend some guidance.


I have a table that has 3 yes/no columns. (Whether a person has a cat [yes/no], a dog [yes/no], or a fish [yes/no]. I am trying to get all 3 of the columns to be summed independently and then grouped in a single query, so that I can ultimately graph all of the data on one graph. The graph would show the count of how many people had a dog, how many had a cat, and how many had a fish.


Is there a way to do this? I can count the number of yes answers for each column, but I am struggling to combine those in a way that I can then graph them all on one graph.



Thank you in advanced!

[lfpm062010]

Use UNION to join on all 3 queries. Then use the UNION query to graph.

For more information.
http://msdn.microsoft.com/en-us/libr...ffice.15).aspx

[June7]

Your data structure is not normalized. Hence the need to resort to UNION query to manipulate data into normalized structure.

Try:

SELECT "dog" AS Pet, Count(*) AS CountPet FROM tablename WHERE dog = True
UNION SELECT "cat", Count(*) FROM tablename WHERE cat = True
UNION SELECT "fish", Count(*) FROM tablename WHERE fish = True;

[james28]

June,

Thank you for the reply. That is fantastic!

The reason that my data is structured this way is because infopath can not handle multiple criteria; hence the need to give each option its own yes/no.


If I was not bound by infopath's entry, what would be the normalized way to store the data? Make a new table that lists petname and the person? This way, they can repeat as many entries as needed for one person, for each type of pet?

[June7]

Yes, a related table for person/pets would be normalized structure.

Then a simple aggregate (Totals) GROUP BY query could be done with that table. Or build a report using Sorting & Grouping with aggregate calcs in footers to show detail records and summary calcs.

Actually, here is a query that uses your current structure, without the UNION.

SELECT Sum(IIf([dog]=True,1,0)) AS CountDog, Sum(IIf([cat]=True,1,0)) AS CountCat, Sum(IIf([fish]=True,1,0)) AS CountFish FROM Table1;

Do you see the limitations of the non-normalized data?

[james28]

June,

Thank you for the reply. Using your non Union example, I see what you are saying. The query results in many columns with only 1 row, which is the problem I was running into for graphing purposes.

Other than not being normalized, is there any reason I would want to avoid a union query?

[June7]

Union query is not an editable dataset so don't even try to use it for that.
Not all my data is fully normalized and I must use UNION to get dataset for graphing purposes.

[james28]

June,


I am running into an issue with my graphing. I am now trying to show the percentage of people that have each type of animal, instead of simply the total count. This way, I can display a bar graph with percentages instead of actual counts.

I have tried to switch it to percentages from the chart/graph side of things, but that option seems to be disabled. Is there a way to edit this

SELECT "dog" AS Pet, Count(*) AS CountPet FROM tablename WHERE dog = True
UNION SELECT "cat", Count(*) FROM tablename WHERE cat = True
UNION SELECT "fish", Count(*) FROM tablename WHERE fish = True;


so that it returns the total percentage for each option?



Thank you in advanced!

[james28]

Hello!
I have been trying to solve the above problem and I think I have a solution, I just can not seem to have the code work.
What I am thinking of doing is adding 2 new columns. The first new column would count the number of actual results and be called "Actual". The second column would divide "Pet" by "Actual" and multiple by 100 and be called "percentage".

SELECT "dog" AS Pet,  Count(*) AS Actual, Count(*) AS CountPet FROM tablename WHERE ((tablename.dog)=True), (Count([CountPet])/[Actual])*100 As Percentage
 UNION SELECT "cat",  Count(*), Count(*) FROM tablename WHERE ((tablename.cat)=True), (Count([CountPet])/[Actual])*100
 UNION SELECT "fish",  Count(*), Count(*) FROM tablename WHERE ((tablename.fish)=True), (Count([CountPet])/[Actual])*100;

This way, I can then graph the percentage column. Any ideas on what the problem is? I think it has to do with using more then one count, but I am not sure.

Thank you for any guidance that you could provide.

[June7]

Cannot have the percentage calc after the WHERE clause, belongs in SELECT clause.

The Count(*) expressions will both return the same value. Try (for some reason, forum will no longer let me type UNION in the sample query):

SELECT "dog" AS Pet, (SELECT Count(*) FROM tablename) AS Actual, Count(*) AS CountPet FROM tablename WHERE ((tablename.dog)=True)
______ SELECT "cat", (SELECT Count(*) FROM tablename), Count(*) FROM tablename WHERE ((tablename.cat)=True)
______ SELECT "fish", (SELECT Count(*) FROM tablename), Count(*) FROM tablename WHERE ((tablename.fish)=True);

Use that query as report RecordSource. Do percentage calcs in textbox.

[james28]

June,

Thank you for the reply.


That works perfectly to populate the "Actual" column.


I can do the percentage calcs in text boxes on the report, but my issue then is how do I get the percentages in the chart?


If I can get the percentages to populate in the query, I can then chart that one column.


Any ideas?


Thank you again!

[June7]

Did you save that query as Access object? Maybe instead:

SELECT "dog" AS Pet, (SELECT Count(*) FROM tablename) AS Actual, Count(*) AS CountPet, Count(*) / (SELECT Count(*) FROM tablename) * 100 AS Pct FROM tablename WHERE ((tablename.dog)=True)
______ SELECT "cat", (SELECT Count(*) FROM tablename), Count(*) , Count(*) / (SELECT Count(*) FROM tablename) * 100 FROM tablename WHERE ((tablename.cat)=True)
______ SELECT "fish", (SELECT Count(*) FROM tablename), Count(*), Count(*) / (SELECT Count(*) FROM tablename) * 100 FROM tablename WHERE ((tablename.fish)=True);

Then use that query as form RecordSource.

And graph RowSource:

SELECT Pet, Pct FROM queryname;