Totals on groups of records 1

[eadiem]

First of all I have a table called Varieties. It has 4 fields: ID, Variety, Acres, Year of Planting.
From this I want to make a pie chart of the total acreage of each variety, with 6 categories: 4 slices of the 4 most popular varieties, 1 for other, and 1 for unknown.

Here is my query so far:

SELECT Varieties.Variety, Sum(Varieties.Acres) AS SumOfAcres
FROM Varieties
WHERE (((Varieties.ID)>2500 And (Varieties.ID)<3000))
GROUP BY Varieties.Variety
HAVING (((Varieties.Variety) In ("Bluecrop","Duke","Elliott","Reka")));

It totals the acreage of the 4 most popular varieties just fine.

I need some help in creating the other 2 slices: Unknown and Other.

The problems I have are:

Unknown varieties are not labelled as such, the variety is just left blank.

Grouping several varieties into the "other" category.

Thanks for any suggestions.

Eadie

 

[CosmoKramer]

It looks like a Union query might do the trick. Try something like the following, of course substituting your "Other" values. (The new code is in blue):

SELECT Varieties.Variety, Sum(Varieties.Acres) AS SumOfAcres
FROM Varieties
WHERE (((Varieties.ID)>2500 And (Varieties.ID)<3000))
GROUP BY Varieties.Variety
HAVING (((Varieties.Variety) In ("Bluecrop","Duke","Elliott","Reka")))[COLOR=blue]
UNION
SELECT "Other" AS OtherLabel, Sum(Varieties.Acres) AS SumOfAcres
FROM Varieties
WHERE (((Varieties.ID)>2500 And (Varieties.ID)<3000))
HAVING (((Varieties.Variety) In ("Put","Other","Varieties","Here")))
UNION
SELECT "Unknown" AS UnkLabel, Sum(Varieties.Acres) AS SumOfAcres
FROM Varieties
WHERE (((Varieties.ID)>2500 And (Varieties.ID)<3000))
HAVING ((Varieties.Variety) = Null));[/color]

Notice that there are no Group By clauses in the second two select statements....


Hoc nomen meum verum non est.

 

[eadiem]

Thanks for your response.

That is a good idea to do the union queries, I didn't think of that.

However I am getting an error message "Syntax error in SQL query" ... my query is as follows:

SELECT Varieties.Variety, Sum(Varieties.Acres) AS SumOfAcres
FROM Varieties
WHERE (((Varieties.ID)>2500 And (Varieties.ID)<3000))
GROUP BY Varieties.Variety
HAVING (((Varieties.Variety) In ("Bluecrop","Duke","Elliott","Reka")))
UNION
SELECT "Other" AS OtherLabel, Sum(Varieties.Acres) AS SumOfAcres
FROM Varieties
WHERE (((Varieties.ID)>2500 And (Varieties.ID)<3000))
HAVING (((Varieties.Variety) In ("Jersey","June","Bluejay")))
UNION
SELECT "Unknown" AS UnkLabel, Sum(Varieties.Acres) AS SumOfAcres
FROM Varieties
WHERE (((Varieties.ID)>2500 And (Varieties.ID)<3000))
HAVING ((Varieties.Variety) = Null));

I have never seen the SELECT syntax you use, which does not specify a field name...could that be a problem?

Also, I would like to find a way to get "other" varieties without listing them all - there are dozens of them, and depending on the area (differs by ID) there may be different ones.

Thanks for your help.

Eadie

 

[eadiem]

Found the syntax error - extra closing parenthesis in last line. Will try messing around with this some more.

 

[eadiem]

And also - last line should be:

HAVING ((Varieties.Variety) is null);

Thanks for your help, now works perfectly!

The only thing that would make it optimal is having the query detect "Other" automatically, rather than me typing in all the possible values.

Eadie

 

[CosmoKramer]

Try this for the "Other"s....
Instead of

HAVING (((Varieties.Variety) In ("Jersey","June","Bluejay")))

in the "Other" select clause try

HAVING (((Varieties.Variety) Not In ("Bluecrop","Duke","Elliott","Reka")))

This will ignore nulls and count all other varieties...

Hoc nomen meum verum non est.

 

[eadiem]

Problem solved! Thank you!!

 

[CosmoKramer]

You're welcome, glad I could help!!

Hoc nomen meum verum non est.