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.