Percentage in a query? 1

[Soulbait]

Here is my current sql statement for the query I am running.

SELECT DISTINCTROW [Card 1].Question, [Card 1].CardType, Sum([Card 1].[Strongly Agree]) AS [Strongly Agree], Sum([Card 1].Agree) AS Agree, Sum([Card 1].Disagree) AS Disagree, Sum([Card 1].[Strongly Disagree]) AS [Strongly Disagree], Sum([Card 1].[No Opinion]) AS [No Opinion], Count(*) AS Total
FROM [Card 1]
WHERE ((([Card 1].[Date Entered]) Between [Start date] And [End Date]))
GROUP BY [Card 1].Question, [Card 1].CardType
ORDER BY [Card 1].CardType;


What I'm trying to figure out is how to add another field, at the end, that would give me a percentage of the Strongly Agree field and the Agree field compared to the Total field.

I'm kinda stumped as what to do here.

If anyone could be of assistance I would appreciate it.

Thanks.

Soulbait
"Despite the cost of living, it's still quite popular."

 

[PaulBricker]

It would probably look like this
PercentageStronglyAgree:[Strongly Agree]/([Strongly Agree] + [Agree] + [Disagree] + [Strongly Disagree] + [No Opinion])

Then format the field as percentage.

Paul

 

[Soulbait]

Almost works.

When I change it to Percentage[Strongly Agree]+[Agree]/[Total] it works fine as long as there is data in the strongly agree and agree fields, but if not it will not give me the percentage.

 

[Soulbait]

Ok well if I fill in all the empty fields with 0 it seems to work, however this is not really an option to have the user be putting 0's in the empty fields. Is there anyway for it to be told if its an empty field that it should treat it as a 0

here is the new statement

SELECT DISTINCTROW [Card 1].Question, [Card 1].CardType, Sum([Card 1].[Strongly Agree]) AS [Strongly Agree], Sum([Card 1].Agree) AS Agree, Sum([Card 1].Disagree) AS Disagree, Sum([Card 1].[Strongly Disagree]) AS [Strongly Disagree], Sum([Card 1].[No Opinion]) AS [No Opinion], Count(*) AS Total, ([Strongly Agree]+[Agree])/[Total] AS Percentage
FROM [Card 1]
WHERE ((([Card 1].[Date Entered]) Between [Start date] And [End Date]))
GROUP BY [Card 1].Question, [Card 1].CardType
ORDER BY [Card 1].CardType;

 

[PaulBricker]

PercentageStronglyAgree:[Strongly Agree]/(Nz([Strongly Agree],0) + Nz([Agree],0) + Nz([Disagree],0) + Nz([Strongly Disagree],0) + Nz([No Opinion],0))

Wrap each field in the Nz function.

Paul

 

[PaulBricker]

Sorry, didn't realize you had changed the math a little. Just wrap the Numerator fields in the Nz function.

Paul

 

[Soulbait]

Thanks, that worked perfectly.