percentage query 1

[dinster]

Hi all,

I have query which shows number of records for each day. How do i go about changing this so that it can give me the percentage of each.

This what i got so far...

SELECT Sum(IIf(Format([DateAdded],"dddd")="Monday",1,0)) AS Monday, Sum(IIf(Format([DateAdded],"dddd")="Tuesday",1,0)) AS Tuesday, Sum(IIf(Format([DateAdded],"dddd")="Wednesday",1,0)) AS Wednesday, Sum(IIf(Format([DateAdded],"dddd")="Thursday",1,0)) AS Thursday, Sum(IIf(Format([DateAdded],"dddd")="Friday",1,0)) AS Friday, Sum(IIf(Format([DateAdded],"dddd")="Saturday",1,0)) AS Saturday, Sum(IIf(Format([DateAdded],"dddd")="Sunday",1,0)) AS Sunday
FROM Sheet4;

so basically i want the tatal number of records then divide each day by that ....and times by 100. Not sure how to go bout this


many thanx

 

[dinster]

oh i sussed it out...eventually

SELECT (Sum(IIf(Format([DateAdded],"dddd")="Monday",1,0))/DCount("[ID]","sheet4"))*100 AS Monday, (Sum(IIf(Format([DateAdded],"dddd")="Tuesday",1,0))/DCount("[ID]","sheet4"))*100 AS Tuesday, (Sum(IIf(Format([DateAdded],"dddd")="Wednesday",1,0))/DCount("[ID]","sheet4"))*100 AS Wednesday, (Sum(IIf(Format([DateAdded],"dddd")="Thursday",1,0))/DCount("[ID]","sheet4"))*100 AS Thursday, (Sum(IIf(Format([DateAdded],"dddd")="Friday",1,0))/DCount("[ID]","sheet4"))*100 AS Friday, (Sum(IIf(Format([DateAdded],"dddd")="Saturday",1,0))/DCount("[ID]","sheet4"))*100 AS Saturday, (Sum(IIf(Format([DateAdded],"dddd")="Sunday",1,0))/DCount("[ID]","sheet4"))*100 AS Sunday
FROM Sheet4;

 

[PHV]

And this ?

SELECT 100*Sum(IIf(Format([DateAdded],"dddd")="Monday",1,0))/Count(*) AS Monday, ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886