Query solution 2

[diezzz]

Hello,

My Table looks like this :

AgentID
Mail
Date

I'm trying to build a query that would show me the number of mails treated by every agent / day

For example if the table looks like this :

AgentID Mail Date
1 bla 1/1/2006
1 blabla 1/1/2006
2 bl 1/1/2006
2 bli 2/1/2006

I would like my query to look like :


AgentID NoMails Date
1 2 1/1/2006
2 1 1/1/2006
2 2 2/1/2006


I'm not sure if this is logical, but next i would like a transform query that has for its columns the hours of the day, and as rows AgentID, so i can see how many mails were treated by witch agent and when.

Is this possible?

 

[PHV]

You may try to use mails.date = [type your date here]

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

 

[diezzz]

PARAMETERS [Type the day here] DateTime;
TRANSFORM Count(mails.ingempid) AS CountOfMail
SELECT mailuri.Name, Count(mail.ingempid) AS [Mail totals]
FROM mails
WHERE Date(mails.date)=[Type the day here]
GROUP BY mails.Nume
PIVOT Hour([date]);

 

[diezzz]

it would work only that the field date returns not only the date, but also the hour.

 

[PHV]

And this ?
WHERE DateValue(mails.date)=[Type the day here]

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

 

[diezzz]

it works now fine, thank you very much

 

[lespaul]

then you should show your appreciation with stars!

Have one on me!

les