Counts of dates and sum of sales by month

[rpw]

Hi all,
I don't know how to go about this and would really appreciate help on it. I
have a table with the following fields:
Territory
DateRecd
ApptDate
SaleDate
JobTotal
What I would like to do is have a query that will sum by month on the
SaleDate field, group by Territory and also count the DateRecd, ApptDate, and
SaleDate that match the summed month.
Some SaleDate records are null and it's possible some ApptDate records are
also.
Here is the SQL I have so far for summing the sales by month, I need the
help on how to get the counts of the date fields included somehow.

SELECT qryAllentown.Territory, Sum(qryAllentown.JobTotal) AS [SumOfJob
Total],
DateSerial(Year(qryAllentown![SaleDate]),Month(qryAllentown![SaleDate]),1) AS
SortMonth
FROM qryAllentown
WHERE ((Not (qryAllentown.SaleDate) Is Null))
GROUP BY qryAllentown.Territory,
DateSerial(Year(qryAllentown![SaleDate]),Month(qryAllentown![SaleDate]),1);

Thanks in advance for any assistance provided...

 

[rpw]

Hi all,

I've been able to find a few hints with further searching but now I think a
refinement of the original question and/or more clarification is needed.

Please add to the previous information: If a record has different months for
the three date fields, then I would want the count of a given date field to
be summed for that month. I can get that with a separate query for any one
date field.

I'm sorry but I need to use an example to explain (I think). Imagine one
record with a DateRecd in 01/2008, a ApptDate in 02/2008, and a SaleDate in
03/2008. I' getting results like this:

SampleOne
Territory SortMonth DateRecd ApptDate SaleDate JobTotal
One 1/01/2008 1 1 1
$3,000

But what I want is slightly different, like this:

SampleTwo
Territory SortMonth DateRecd ApptDate SaleDate JobTotal
One 1/01/2008 1
One 2/01/2008 1
One 3/01/2008 1
$3,000

Here is my SQL that gets me the equivilent of SampleOne above:
SELECT qryAllentown.Territory,
DateSerial(Year([tblAllentown]![DateRecd]),Month([tblAllentown]![DateRecd]),1)
AS SortMonth, Count(qryAllentown.DateRecd) AS CountOfDateRecd,
Count(qryAllentown.ApptDate) AS CountOfApptDate, Count(qryAllentown.SaleDate)
AS CountOfSaleDate, Sum(qryAllentown.JobTotal) AS SumOfJobTotal
FROM qryAllentown
WHERE ((Not (qryAllentown.DateRecd) Is Null))
GROUP BY qryAllentown.Territory,
DateSerial(Year([tblAllentown]![DateRecd]),Month([tblAllentown]![DateRecd]),1);


Again, any and all help is appreciated!

 

[rpw]

Hi all,
Cancel the request. I got what I wanted by building three queries and then
a query on those three.