If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Count items in percent ranges
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! |
#2
|
|||
|
|||
Count items in percent ranges
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 "Molasses26" wrote: 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! |
#3
|
|||
|
|||
Count items in percent ranges
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? "Daryl S" wrote: 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 "Molasses26" wrote: 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! |
#4
|
|||
|
|||
Count items in percent ranges
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 "Molasses26" wrote in message ... 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! |
#5
|
|||
|
|||
Count items in percent ranges
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 "Molasses26" wrote: 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? "Daryl S" wrote: 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 "Molasses26" wrote: 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! |
#6
|
|||
|
|||
Count items in percent ranges
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!!! "vanderghast" wrote: 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 "Molasses26" wrote in message ... 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! |
Thread Tools | |
Display Modes | |
|
|