Calculate Monthly Average Multiplied by Count of Dates/Month with Multiple Records Having Same Date

G

GarbageGeek

Any help would be greatly appreciated, as this has proven to be very
complicated and I have been trying to get this for more than 2 days
now. 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 help!
 
G

Gary Walter

I might "divide-and-conquer"
with a preliminary query

SELECT
ROUTE,
COLLECTION_DATE,
SUM(HOMES_COLLECTED) As sHomesCollected,
MAX(ACTUAL_HOMES) As sActualHomes
FROM
tempReportData
GROUP BY
ROUTE,
COLLECTION_DATE;

which I think would give you

2A 9/1/2006 700 1000
2A 9/2/2006 650 1000
4B 9/1/2006 550 950
4B 9/2/2006 600 950

then use this "qryPreLim" for calculation

SELECT
ROUTE,
Month(COLLECTION_DATE) AS MNTH,
SUM(sHomesCollected) AS COLLECTED,
SUM(sActualHomes) AS ACTUAL,
COLLECTED/ACTUAL AS SETOUT_RATE
FROM
qryPreLim
GROUP BY
ROUTE,
Month(COLLECTION_DATE);


ROUTE MNTH COLLECTED ACTUAL SETOUT_RATE
2A September 1350 2000 67.5%
4B September 1150 1900 60.5%


GarbageGeek said:
Any help would be greatly appreciated, as this has proven to be very
complicated and I have been trying to get this for more than 2 days
now. 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 help!
Click to expand...
 
G

GarbageGeek

It was so simple, it was genius. My toil is over. It worked
perfectly.

Many thanks and deepest gratitude,

Gary said:
I might "divide-and-conquer"
with a preliminary query

SELECT
ROUTE,
COLLECTION_DATE,
SUM(HOMES_COLLECTED) As sHomesCollected,
MAX(ACTUAL_HOMES) As sActualHomes
FROM
tempReportData
GROUP BY
ROUTE,
COLLECTION_DATE;

which I think would give you

2A 9/1/2006 700 1000
2A 9/2/2006 650 1000
4B 9/1/2006 550 950
4B 9/2/2006 600 950

then use this "qryPreLim" for calculation

SELECT
ROUTE,
Month(COLLECTION_DATE) AS MNTH,
SUM(sHomesCollected) AS COLLECTED,
SUM(sActualHomes) AS ACTUAL,
COLLECTED/ACTUAL AS SETOUT_RATE
FROM
qryPreLim
GROUP BY
ROUTE,
Month(COLLECTION_DATE);


ROUTE MNTH COLLECTED ACTUAL SETOUT_RATE
2A September 1350 2000 67.5%
4B September 1150 1900 60.5%


GarbageGeek said:
Any help would be greatly appreciated, as this has proven to be very
complicated and I have been trying to get this for more than 2 days
now. 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 help!
Click to expand...
Click to expand...
 
G

GarbageGeek

This nested select was also provided from the kindly Antonio from
Portugal

Subject: Re: MS Access - Monthly Average Based on Unique Dates w/
Multiple Records/Date
From: antonioa-ga on 16 Sep 2006 21:48 PDT

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.

It was so simple, it was genius. My toil is over. It worked
perfectly.

Many thanks and deepest gratitude,

Gary said:
I might "divide-and-conquer"
with a preliminary query

SELECT
ROUTE,
COLLECTION_DATE,
SUM(HOMES_COLLECTED) As sHomesCollected,
MAX(ACTUAL_HOMES) As sActualHomes
FROM
tempReportData
GROUP BY
ROUTE,
COLLECTION_DATE;

which I think would give you

2A 9/1/2006 700 1000
2A 9/2/2006 650 1000
4B 9/1/2006 550 950
4B 9/2/2006 600 950

then use this "qryPreLim" for calculation

SELECT
ROUTE,
Month(COLLECTION_DATE) AS MNTH,
SUM(sHomesCollected) AS COLLECTED,
SUM(sActualHomes) AS ACTUAL,
COLLECTED/ACTUAL AS SETOUT_RATE
FROM
qryPreLim
GROUP BY
ROUTE,
Month(COLLECTION_DATE);


ROUTE MNTH COLLECTED ACTUAL SETOUT_RATE
2A September 1350 2000 67.5%
4B September 1150 1900 60.5%


GarbageGeek said:
Any help would be greatly appreciated, as this has proven to be very
complicated and I have been trying to get this for more than 2 days
now. 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 help!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Multiple expressions (Sum, Count, Average) of same value in crosstab query 3
Sum the number of days between dates by Month 16
Lots of Frustration - Lots of Arrays, Dynamic Ranges Don't Work, Help With Options 6

Top