View Question
Q: MS Access - Monthly Average Based on Unique Dates w/ Multiple Records/Date ( No Answer,   1 Comment )
 Question
 Subject: MS Access - Monthly Average Based on Unique Dates w/ Multiple Records/Date Category: Computers > Programming Asked by: garbagegeek-ga List Price: \$25.00 Posted: 15 Sep 2006 14:35 PDT Expires: 17 Sep 2006 09:50 PDT Question ID: 765669
 ```This has proven to be very complicated and I have been trying to get this for more than 2 days now. Using MS Access 2003, I am trying to define the monthly average set-out rate per route for households that recycle, based on the route logs provided by the recycling collection truck drivers. The problem is that more than one driver may service the same route. A Count of the number of records can't be used, it must be a Count of the number of unique dates for the month/route multiplied by the number of households, which will then be divided from the actual number of homes collected. The table contains fields and values: ROUTE COLLECTION_DATE HOMES_COLLECTED ACTUAL_HOMES 2A 9/1/2006 700 1000 2A 9/2/2006 350 1000 2A 9/2/2006 300 1000 4B 9/1/2006 150 950 4B 9/1/2006 400 950 4B 9/2/2006 600 950 The set-out rate percentage can be calculated in the Report, but this needs to be divided by the total number of ACTUAL_HOMES counted only once for each date. The Report would have the results: ROUTE MONTH HOMES_COLLECTED ACTUAL_HOMES SETOUT_RATE 2A September 1350 2000 67.5% 4B September 1150 1900 60.5% It is the sum of the ACTUAL_HOMES that are only counted once per unique date that I need to generate in the query, in order to use it in the report to be divided by the sum of the HOMES_COLLECTED. Here is what I have so far, but I can't figure out what to put in the ????????????? SELECT DISTINCT MonthName(Month([COLLECTION_DATE])) AS RPT_MONTH, tempReportData.ROUTE AS RPT_ROUTE, Sum(IIf([HOMES_COLLECTED]>0,[HOMES_COLLECTED],0)) AS RPT_HOMES_COLLECTED, (Avg(IIf([ACTUAL_HOMES]>0,[ACTUAL_HOMES],0)))*????????????? AS RPT_ACTUAL_HOMES FROM tempReportData GROUP BY MonthName(Month([COLLECTION_DATE])), tempReportData.ROUTE ORDER BY MonthName(Month([COLLECTION_DATE])), tempReportData.ROUTE; Many, many thanks to anyone that can what to put in the ????????????? or any other way to accomplish this!```
 ```Here is one way to do it, but there are probably other ways to do it... By "Selecting" from other "Selects"... you get the result you where looking for!! SELECT RPT_ROUTE, RPT_MONTH, Sum(RPT_HOMES_COLLECTED), Sum(RPT_ACTUAL_HOMES) FROM (select route AS RPT_ROUTE, MonthName(Month([COLLECTION_DATE])) AS RPT_MONTH, 0 AS RPT_HOMES_COLLECTED, sum(actual_homes) as RPT_ACTUAL_HOMES from (select distinct route, COLLECTION_DATE, 0 AS RPT_HOMES_COLLECTED, actual_homes from tempReportData) group by MonthName(Month([COLLECTION_DATE])), route union select route AS RPT_ROUTE, MonthName(Month([COLLECTION_DATE])) AS RPT_MONTH, HOMES_COLLECTED AS RPT_HOMES_COLLECTED, 0 as RPT_ACTUAL_HOMES from tempReportData) GROUP BY RPT_ROUTE, RPT_MONTH; Good luck with your work! Greetings from Portugal! Antonio.```