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
|
|||
|
|||
Counts of dates and sum of sales by month
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 |
#2
|
|||
|
|||
Counts of dates and sum of sales by month
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 "rpw" wrote: 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 |
#3
|
|||
|
|||
Counts of dates and sum of sales by month
Hi all,
Cancel the request. I got what I wanted by building three queries and then a query on those three. -- rpw "rpw" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|