Query Weekly Totals

[biggcc]

On the attached jpg(s) you will see the query I'm working with. It's fairly simple except for a couple of things and those are the items I'm stuck on.

What I'm trying to do is get a representation of the amount of traffic that has come through our salescenters in the last week. Some centers had no traffic and others had multiple prospects. So when I look at the query for week 33 (this week) I would like to see all the centers even if they had zero traffic - the query should show zero.

If you look at Traffic_Query.jpg you'll see how the table is laid out. With the query I'm trying to show all of our centers for the current week even if no traffic was entered.

I may not be explaining it clearly so if you have any questions let me know.

Thanks,
Chester Campbell
Joseph Freed and Associates

 

[Jon K]

Assuming you have these two tables:
Traffic - recording sComm_Name, dtTracking_Date, iTracking_Count
Comm_Name - containing a unique list of sComm_Name

you can do it with a series of two queries.

qryOne:-
SELECT sComm_Name,
DatePart("ww",dtTracking_Date) AS iTracking_Week,
Sum(Traffic.iTracking_Count) AS iTracking_Count
FROM Traffic
GROUP BY sComm_Name, DatePart("ww",dtTracking_Date)
HAVING DatePart("ww",dtTracking_Date)=DatePart("ww",Date());

qryTwo:-
SELECT Comm_Name.sComm_Name,
Nz(qryOne.iTracking_Week,DatePart("ww",Date()))+0 AS iTracking_Week,
Nz(qryOne.iTracking_Count)+0 AS iTracking_Count
FROM Comm_Name LEFT JOIN qryOne ON Comm_Name.sComm_Name=qryOne.sComm_Name;

Run the second query.
.

 

[biggcc]

Weekly Totals

Thanks Jon K - you gave me enough information so I could get what I need except for one small detail. I would like to display the last date for the week close and I can't figure that one out. Here's what I have currently that gives me the weekly totals I was looking for.

SELECT DISTINCTROW [Traffic_Count Query].sComm_Name, [Traffic_Count Query].iTracking_Week, [Traffic_Count Query].iTracking_Year, Sum([Traffic_Count Query].iTraffic_Count) AS SumOfiTraffic_Count
FROM [Traffic_Count Query]
WHERE ((([Traffic_Count Query].iTracking_Week)=DatePart("ww",Date())))
GROUP BY [Traffic_Count Query].sComm_Name, [Traffic_Count Query].iTracking_Week, [Traffic_Count Query].iTracking_Year
HAVING ((([Traffic_Count Query].iTracking_Year)=Year(Now())))
ORDER BY [Traffic_Count Query].sComm_Name;

But how do I show the week ending date or possibly the week date range?

Thanks again for all you help.

 

[Jon K]

You can add:

Date()-WeekDay(Date())+1 AS StartOfWeek,
Date()-WeekDay(Date())+7 AS EndOfWeek

which should return the Sunday and Saturday of the current week.

But you may have a problem when a year ends and a new year starts because most years do not start on a Sunday and end on a Saturday.
.

 

[Rabbie]

most years do not start on a Sunday and end on a Saturday.
.

Pedants Corner: If 1/1/yyyy is a sunday then 31/12/yyyy will also be a sunday unless its a leap year when it will be a monday.

Sorry Jon. I can resist everything except temptation.

 

[biggcc]

Weekly Totals

THANKS JonK.

After some slight tweaking it does exactly what I need it to do. I have 4 different queries that show prospect traffic for the current and prior 3 weeks. Once I adjusted the number after the Date()) entry to the corresponding weeks it worked GREAT!

Thanks again,
Chester Campbell