I revised your query slightly, moving Month, MonthName, and Order to another
query and is named CCripe below.
CCripe_1 uses a table named CountNumber and field CountNUM with numbers from
0 (zero) through your maximum spread of weeks.
CCripe_2 left joins CCripe_1 to CCripe on WeekEnding and formulates Month,
MonthName, and Order using WeekEnding from CCripe_1 so as to have data for
all weeks.
CCripe
SELECT tblActivities.ActivityID, tblActivities.ContactID,
tblActivities.ProspectID, tblActivities.Date, tblActivities.Employee,
tblActivities.ActivityType, tblProducers.Initials,
DateAdd("d",7-DatePart("w",[Date],2),[Date]) AS WeekEnding,
Switch([Month]='12','1',[Month]='1','2',[Month]='2','3',[Month]='3','4',[Month]='4','5',[Month]='5','6',[Month]='6','7',[Month]='7','8',[Month]='8','9',[Month]='9','10',[Month]='10','11',[Month]='11','12') AS [Order]
FROM tblActivities INNER JOIN tblProducers ON tblActivities.Employee =
tblProducers.Initials
WHERE (((tblActivities.Date) Between
[Forms]![frmProducerTouchesChoice].[Start Date] And
[Forms]![frmProducerTouchesChoice].[End Date]) AND
((tblActivities.ActivityType)<>"Imaged Material" And
(tblActivities.ActivityType)<>"Info Updated" And
(tblActivities.ActivityType)<>"Set Up AMS" And
(tblActivities.ActivityType)<>"Red Flag" And
(tblActivities.ActivityType)<>"Scheduling") AND
((tblProducers.Initials)<>"119") AND ((tblProducers.CurrentEmployee)=Yes))
ORDER BY tblActivities.ActivityType;
CCripe_1
SELECT
DateAdd("d",7-DatePart("w",DateAdd("ww",[CountNUM],[Forms]![frmProducerTouchesChoice].[Start
Date]),2),DateAdd("ww",[CountNUM],[Forms]![frmProducerTouchesChoice].[Start
Date])) AS WeekEnding
FROM CountNumber
WHERE (((DateAdd("ww",[CountNUM],[Forms]![frmProducerTouchesChoice].[Start
Date]))<=CVDate([Forms]![frmProducerTouchesChoice].[End Date])))
ORDER BY DateAdd("ww",[CountNUM],[Forms]![frmProducerTouchesChoice].[Start
Date]);
CCripe_2
SELECT CCripe_1.WeekEnding, CCripe.ActivityID, CCripe.ContactID,
CCripe.ProspectID, CCripe.Date, CCripe.Employee, CCripe.ActivityType,
CCripe.Initials, DatePart("m",[CCripe_1].[WeekEnding]) AS [Month],
Switch([Month]='1','January',[Month]='2','February',[Month]='3','March',[Month]='4','April',[Month]='5','May',[Month]='6','June',[Month]='7','July',[Month]='8','August',[Month]='9','September',[Month]='10','October',[Month]='11','November',[Month]='12','December')
AS MonthName,
Switch([Month]='12','1',[Month]='1','2',[Month]='2','3',[Month]='3','4',[Month]='4','5',[Month]='5','6',[Month]='6','7',[Month]='7','8',[Month]='8','9',[Month]='9','10',[Month]='10','11',[Month]='11','12') AS [Order]
FROM CCripe_1 LEFT JOIN CCripe ON CCripe_1.WeekEnding = CCripe.WeekEnding;
--
KARL DEWEY
Build a little - Test a little
CCripe said:
Karl, not sure if this is what you are looking for or not. This is all the
SQL from the first query, and I suspect it's more than you wanted.
SELECT tblActivities.ActivityID, tblActivities.ContactID,
tblActivities.ProspectID, tblActivities.Date, tblActivities.Employee,
tblActivities.ActivityType, tblProducers.Initials,
DateAdd("d",7-DatePart("w",[Date],2),[Date]) AS WeekEnding,
DatePart("m",[WeekEnding]) AS [Month],
Switch([Month]='1','January',[Month]='2','February',[Month]='3','March',[Month]='4','April',[Month]='5','May',[Month]='6','June',[Month]='7','July',[Month]='8','August',[Month]='9','September',[Month]='10','October',[Month]='11','November',[Month]='12','December')
AS MonthName,
Switch([Month]='12','1',[Month]='1','2',[Month]='2','3',[Month]='3','4',[Month]='4','5',[Month]='5','6',[Month]='6','7',[Month]='7','8',[Month]='8','9',[Month]='9','10',[Month]='10','11',[Month]='11','12') AS [Order]
FROM tblActivities INNER JOIN tblProducers ON tblActivities.Employee =
tblProducers.Initials
WHERE (((tblActivities.Date) Between
[Forms]![frmProducerTouchesChoice].[Start Date] And
[Forms]![frmProducerTouchesChoice].[End Date]) AND
((tblActivities.ActivityType)<>"Imaged Material" And
(tblActivities.ActivityType)<>"Info Updated" And
(tblActivities.ActivityType)<>"Set Up AMS" And
(tblActivities.ActivityType)<>"Red Flag" And
(tblActivities.ActivityType)<>"Scheduling") AND
((tblProducers.Initials)<>"119") AND ((tblProducers.CurrentEmployee)=Yes))
ORDER BY tblActivities.ActivityType;
:
Post your SQL that gives the results below and I will edit it.
--
KARL DEWEY
Build a little - Test a little
:
I have a sales database report that sums and groups by weekending date. It
looks at activity dates and sums the number of activities for a given week
ending date.
Sample Data:
Dec Week Ending Activity Count
12/07/08 5
12/21/08 4
12/28/08 3
Total Monthly Activity 12
The problem:
In the above example, I need it to show a week ending date for 12/14/08 with
an Activity Count of zero so we know that the week wasn't just missed.
We also average how many activities per week. In the above example, the
report will calculate an average of 4 activities per week for December (12
total activities divided by the three weeks listed). This does not take into
account the missing week. I want the report to divide the total number of
activities by the total number of week ending dates for a given month.
Any help is greatly appreciated.
