Calculating percentage from two different queries

[Dinky]

Hi guys, I'm new to this forum, and in some serious need of help with a newbie assignment.

I have a database of dogs in a kennel, and I'm asked to create a query that lists how many dogs there are of each gender in the kennel. I think I've got an okay solution for this, the SQL-code is as follows:



SELECT Count(tblDog.DogNr) AS CountOfDogNr, tblDog.Gender
FROM tblDog
GROUP BY tblDog.Gender

I'm then asked to give a selection that shows how many dogs weigh above 10 kg, which I've also done, though I'm not sure if this is the best way to do it:

SELECT Count(tblDog.DogNr) AS CountOfDogNr, tblDog.Gender
FROM tblDog
WHERE (((tblDog.Weigth)>=10))
GROUP BY tblDog.Gender;


But I'm stuck on the next task, which is to provide a percentage of the number of dogs that weighs more than 10 kg. I guess I have to make a new query which calculates the percentage by dividing the count of dogs above 10 kg with the total count of dogs, sorted by gender (and then formatting the field to show percentage). But I'm not sure how to do this, and I can't get it right. I'm wondering if I'm doing something wrong in the two first queries.

Any help would be greatly appreciated!

I'm using Access 2007, by the way.

[ConneXionLost]

First, add titles to your existing queries.

queryA: qryCountDogsByGender; and,
queryB: qryCountLargeDogsByGender

Second, make a small adjustment to your queryB:

SELECT Count(tblDog.DogNr) AS CountOfLargeDogNr, tblDog.Gender
FROM tblDog
WHERE (((tblDog.Weight)>=10))
GROUP BY tblDog.Gender;

Third, build a new query that uses the first two to answer your question:

SELECT [CountOfLargeDogNr]/[CountOfDogNr] AS LargeDogPercent, qryCountDogsByGender.Gender
FROM qryCountDogsByGender INNER JOIN qryCountLargeDogsByGender ON qryCountDogsByGender.Gender = qryCountLargeDogsByGender.Gender;

Finally, format LargeDogPercent as percent.

Cheers,

[TheShabz]

See if this works for you.

SELECT Gender, Format(DLookUp("DogNr","tblDog","weight >=10")/Count([DogNr), "Percent") AS Percent
FROM tblDog
GROUP BY Gender;

[Dinky]

You have my greatest thanks ConneXion, that solved it for me.

Thank you both for your time.