Count items in percent ranges

[Molasses26]

I have the table below and the Reads field is a Yes/No field and I’m only
looking at the ones marked Yes so my data will look like this:
Meter RdDate Reads
1234 1/5/2010 -1
1234 1/6/2010 -1
1234 1/8/2010 -1
2345 1/6/2010 -1
2345 1/15/2010 -1
2345 1/26/2010 -1
2345 1/27/2010 -1
2345 1/28/2010 -1
Etc.

I would like to create a query and report that will give me the number of
meters that have Reads=-1 for <=10% of the month, 11-20% of the month 21-30%
of the month, etc.

I am completely drawing a blank on this so any help you can give me would be
greatly appreciated!

 

[Daryl S]

Molasses26 -

If every meter has one record per date, then you can do something like this
(use your table/field names):

SELECT MeterReads.Meter, -Sum([Reads])/Count([ReadDate]) AS PctReads,
Year([ReadDate]) & "_" & Month([ReadDate]) AS YearMo
FROM MeterReads
GROUP BY MeterReads.Meter, Year([ReadDate]) & "_" & Month([ReadDate]);

 

[Molasses26]

When I run this all the PctReads come back with a value of 1 for every meter
so I don't think this is quite what I need.
Every meter does not have a row for each day of the month. They only have a
row on the days we got a read, so some meters will only have one row on one
day and some might have rows for 5 different days or some might have one for
every day. They will not have more than one row on a day tho.
Any further suggestions?

Molasses26 -

If every meter has one record per date, then you can do something like this
(use your table/field names):

SELECT MeterReads.Meter, -Sum([Reads])/Count([ReadDate]) AS PctReads,
Year([ReadDate]) & "_" & Month([ReadDate]) AS YearMo
FROM MeterReads
GROUP BY MeterReads.Meter, Year([ReadDate]) & "_" & Month([ReadDate]);

--
Daryl S

I have the table below and the Reads field is a Yes/No field and I’m only
looking at the ones marked Yes so my data will look like this:
Meter RdDate Reads
1234 1/5/2010 -1
1234 1/6/2010 -1
1234 1/8/2010 -1
2345 1/6/2010 -1
2345 1/15/2010 -1
2345 1/26/2010 -1
2345 1/27/2010 -1
2345 1/28/2010 -1
Etc.

I would like to create a query and report that will give me the number of
meters that have Reads=-1 for <=10% of the month, 11-20% of the month 21-30%
of the month, etc.

I am completely drawing a blank on this so any help you can give me would be
greatly appreciated!

 

[vanderghast]

SELECT meter,
Month(RdDate) AS theMonth,
MAX( DateSerial( (Year(RdDate), 1+ Month(RdDate), 0 )) AS lastOfMonth,
-SUM(Reads) / Day( LastOfMonth ) AS percentage
FROM somewhere
GROUP BY meter, Month(RdDate)


should return, by meter, and by month, the % of days with Reads = -1 ( I
assume there is only one record by date, by meter).

SELECT PARTITION(10*percentage, 0, 100, 10), theMonth, COUNT(*)
FROM previousQuery
GROUP BY PARTITION(10*percentage, 0, 100, 10), theMonth

returns the number of meters, per month, grouped for their percentage per
10% interval. Some ranges for some months may be missing. You could also use
a crosstab, instead:

TRANSFORM NZ(COUNT(*), 0)
SELECT theMonth
FROM firstQuery
GROUP BY theMonth
PIVOT REPLACE(PARTITION(10*percentage, 0, 100, 10) , " ", "") IN ("0:9",
"10:19", "20:29", "30:39", "40:49", "50:59", "60:69", "70:79", "80:89",
"90:99", "100:100")




Vanderghast, Access MVP

 

[Daryl S]

Molasses26 -

OK, then you can do it this way. This takes two queries. The first one
gets all the basic stats:

SELECT MeterReads.Meter, Year([ReadDate]) & "_" & Month([ReadDate]) AS
YearMo, Sum(-[Reads]) AS NumReads,
(DateDiff("d",DateSerial(Year([ReadDate]),Month([ReadDate]),1),DateSerial(Year([ReadDate]),Month([ReadDate])+1,1))) AS DaysInMonth
FROM MeterReads
WHERE (((MeterReads.Reads)=-1))
GROUP BY MeterReads.Meter, Year([ReadDate]) & "_" & Month([ReadDate]),
(DateDiff("d",DateSerial(Year([ReadDate]),Month([ReadDate]),1),DateSerial(Year([ReadDate]),Month([ReadDate])+1,1)));

The second one just calculates the percents (assuming the name of the first
query is MeterReadStats (change as needed):

SELECT MeterReadStats.Meter, MeterReadStats.YearMo, MeterReadStats.NumReads,
MeterReadStats.DaysInMonth, [NumReads]/[DaysInMonth] AS PctReads
FROM MeterReadStats;

--
Daryl S

When I run this all the PctReads come back with a value of 1 for every meter
so I don't think this is quite what I need.
Every meter does not have a row for each day of the month. They only have a
row on the days we got a read, so some meters will only have one row on one
day and some might have rows for 5 different days or some might have one for
every day. They will not have more than one row on a day tho.
Any further suggestions?

Molasses26 -

If every meter has one record per date, then you can do something like this
(use your table/field names):

SELECT MeterReads.Meter, -Sum([Reads])/Count([ReadDate]) AS PctReads,
Year([ReadDate]) & "_" & Month([ReadDate]) AS YearMo
FROM MeterReads
GROUP BY MeterReads.Meter, Year([ReadDate]) & "_" & Month([ReadDate]);

--
Daryl S

I have the table below and the Reads field is a Yes/No field and I’m only
looking at the ones marked Yes so my data will look like this:
Meter RdDate Reads
1234 1/5/2010 -1
1234 1/6/2010 -1
1234 1/8/2010 -1
2345 1/6/2010 -1
2345 1/15/2010 -1
2345 1/26/2010 -1
2345 1/27/2010 -1
2345 1/28/2010 -1
Etc.

I would like to create a query and report that will give me the number of
meters that have Reads=-1 for <=10% of the month, 11-20% of the month 21-30%
of the month, etc.

I am completely drawing a blank on this so any help you can give me would be
greatly appreciated!

 

[Molasses26]

I had to change the Partition formula to "PARTITION(100*percentage,0,100,10)"
in order to get the counts to fall into the correct levels but other than
that it worked great!
THANKS!!!