Weekday() to filter results by Monday, Tuesday etc

[nick.h]

Hey guys,



I have a query that counts all the records that were added in the last week (by looking at a DateAdded field (which automatically populates with a Now()).

I would like to break down the results further by having the records broken down by Monday, Tuesday, Wednesday, Thursday, and Friday headers.

Any idea how I would write the logic to do that? I can not wrap my head around the Weekday() function at all yet

Thanks for your help,
Nick

[hertfordkc]

WD:weekday([DateAdded])
or if you want to sort on the name,
WD:weekdayname([DateAdded])

[nick.h]

It gave me an invalid procedure call???

Here is the code

SELECT "Prospecting Calls" AS VisitType, Abs(Sum(VisitDate Between Date()-6 And Date())) AS Week, Abs(Sum(VisitDate Between Date()-29 And Date())) AS [Month], Abs(Sum(VisitDate Between Date()-89 And Date())) AS Quarter, Abs(Sum(VisitDate Between Date()-359 And Date())) AS [Year], WeekdayName([DateAdded]) AS WD
FROM VisitInfo
WHERE (((VisitInfo.[VisitDate]) Between Date()-359 And Date()) AND ((VisitInfo.[AddedBy])=GetUser()) AND ((VisitInfo.[FormOfContact])=3))
GROUP BY "Prospecting Calls", WeekdayName([DateAdded]);

It is part of a larger union query, but until I get it working I'm jsut trying to fix one bit of it.

[nick.h]

Crap, I found out what caused the invalid procedure call. It was me forgetting to take out the code that sorted it by Week, Month, Quarter, and Year.

What I want to do is get a Count of all records taht were added on Monday, Tuesday, Wednesday etc.

Here is what I've changed it to so far:

SELECT "Prospecting Calls" AS VisitType, Weekday(VisitDate) AS WD
FROM VisitInfo
WHERE (((VisitInfo.[VisitDate]) Between Date()-6 And Date()) AND ((VisitInfo.[AddedBy])=GetUser()) AND ((VisitInfo.[FormOfContact])=3))
GROUP BY "Prospecting Calls";

It doesn't work and I'm horribly confused, lol

[hertfordkc]

when doing queries.
However, there are a couple of things in your SQL that don't look right.

Crap, I found out what caused the invalid procedure call. It was me forgetting to take out the code that sorted it by Week, Month, Quarter, and Year.

What I want to do is get a Count of all records taht were added on Monday, Tuesday, Wednesday etc.

Here is what I've changed it to so far:

Is "Prospecting Calls" a field? If so, I'd use [Prospecting Calls].
So...

SELECT [Prospecting Calls] AS VisitType, Weekday([VisitDate]) AS WD
FROM VisitInfo
WHERE (((VisitInfo.[VisitDate]) Between Date()-6 And Date()) AND ((VisitInfo.[AddedBy])=GetUser()) AND ((VisitInfo.[FormOfContact])=3))
GROUP BY [Prospecting Calls],[WD];

It doesn't work and I'm horribly confused, lol

HTH

[nick.h]

Actually Prospecting Calls is not a field name. Like I said earlier it is part of a larger union query which sorts different call types and the query adds a column which names the row of results. Does that make sense?

[hertfordkc]

"Prospecting Calls" do you get results?

[nick.h]

Yea, I haven't had a problem with that.

[nick.h]

SELECT "Prospecting Calls" AS VisitType, Abs(Count(VisitDate)) AS WD
FROM VisitInfo
WHERE (((VisitInfo.VisitDate) Between Date()-359 And Date()) AND ((VisitInfo.AddedBy)=GetUser()) AND ((VisitInfo.FormOfContact)=3))
GROUP BY "Prospecting Calls WD";

Here's the current code. Again the rub is I would like to have a count of all records added on last Monday, last Tuesday. Sorry if I'm repeating myself, but I babble when I'm stumped.

[hertfordkc]

SELECT "Prospecting Calls" AS VisitType, Abs(Count(VisitDate)) AS WD
FROM VisitInfo
WHERE (((VisitInfo.VisitDate) Between Date()-359 And Date()) AND ((VisitInfo.AddedBy)=GetUser()) AND ((VisitInfo.FormOfContact)=3))
GROUP BY "Prospecting Calls",WD;

Here's the current code. Again the rub is I would like to have a count of all records added on last Monday, last Tuesday. Sorry if I'm repeating myself, but I babble when I'm stumped.

However, the rest of the SQL has pieces that don't make sense and I'm afraid we are confusing each other. I suggest that you post the basic SQL query which includes all of the fields which are needed to later sort,group, and total. This query should include the weekday calculated field.