SQL - How to extract data grouped by WEEK 1

[DomFino]

Hi everyone,
I have an application where I link to Outlook from Access. The purpose of the database is to count how many Sent and Received Emails each person receives per week.

The following code results in the output in the first table below and I need to have the output show as it is in table 2 below. I think the problem is that the date/time is in the Sent and Received fields. I changed the format of the table that contains the data to Short Date but if I hold the cursor over the date it shows date and time (10/10/2006 2:18:28 PM). Is there a way to modify the SQL below to only select the date rather than the data and time?

SELECT DF_Sent.To, Count(DF_Sent.[Sender Name]) AS [CountOfSender Name], DF_Sent.Sent
FROM DF_Sent
GROUP BY DF_Sent.To, DF_Sent.Sent
HAVING ((([DF_Sent.Sent]) Between Date()-Weekday(Date())+1 And Date()-Weekday(Date())+7))
ORDER BY DF_Sent.To;

10/10/2006 2:18:28 PM

Table 1 - I get this:

DF_EmailSent Query

To CountOfSender Name Sent
Bennett 1 10/10/2006
Bennett 1 10/10/2006
Bennett 1 10/11/2006
Bennett 1 10/11/2006
Heess 1 10/9/2006
adelphia 1 10/8/2006
adelphia 1 10/10/2006

Table 2 - I am trying to get this:

DF_EmailSent Query

To CountOfSender Name Sent
Bennett 4 10/10/2006
Heess 1 10/9/2006
adelphia 2 10/8/2006

 

[PHV]

You may try this:
SELECT DF_Sent.To, Count(DF_Sent.[Sender Name]) AS [CountOfSender Name], [!]DateValue([/!]DF_Sent.Sent[!])[/!]
FROM DF_Sent
WHERE [!]DateValue([/!]DF_Sent.Sent[!])[/!] Between Date()-Weekday(Date())+1 And Date()-Weekday(Date())+7
GROUP BY DF_Sent.To, [!]DateValue([/!]DF_Sent.Sent[!])[/!]
ORDER BY DF_Sent.To

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DomFino]

PVH,
Thanks for the quick reply. I used your suggestion and it is closer but still missing the mark.

Here is the new result using your suggested code:

To CountOfSender Name Expr1
Bennett 2 10/10/2006
Bennett 3 10/11/2006
Heess 1 10/11/2006
Heess 1 10/9/2006
adelphia 1 10/8/2006
adelphia 1 10/10/2006

In other words it seperates on the DAY part rather the WEEK.

 

[PHV]

And this ?
SELECT DF_Sent.To, Count(DF_Sent.[Sender Name]) AS [CountOfSender Name], Min(DateValue(DF_Sent.Sent))
FROM DF_Sent
WHERE DateValue(DF_Sent.Sent) Between Date()-Weekday(Date())+1 And Date()-Weekday(Date())+7
GROUP BY DF_Sent.To
ORDER BY DF_Sent.To

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DomFino]

PHV,
You are fantastic. I have been playing with this for over 2 hours and you solved it in 2 minutes.

Your suggested code works perfectly.
Thanks so much.