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?

 

[dhookom]

Assuming your "query to look like" contains an error and you really don't have "witch" agents (however Halloween was earlier this week)

SELECT AgentID, Count(Mail) as NoMails, [Date]
FROM [looks like this]
GROUP BY AgentID, [Date];

I doubt you can get any results with hours since there is no field storing times.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[diezzz]

actualy i ve just build the crosstab query, it shows me in witch ours who treated how many mails. The problem is that tomorrow, the mails will add themselves to the ones that were treated today.

 

[dhookom]

You stated "The problem is that tomorrow, the mails will add themselves to the ones that were treated today" but didn't say what you would like instead.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[diezzz]

ok, sorry for beeing incoherent.

I've built a simple crosstab query, with agent names as rows and hours (8:00, 9:00.....22:00) as columns. Whenever an agent click a particular buton that i've built, it will put "+1" in his field. For example, if i click on a buton at 8:45 then in the row diezz, for 8:00, there would show 1, if i click twice, it would give me 2 and so on for every hour. For exemple if i click once at 8:20, once at 8:40 and once at 9:02 this is how the crosstab will result :

8h 9h 10h .....
diezz 2 1 0 ......

Now let's say i did those "click" monday, and today is tuesday. I would like a different query for tuesday, or at least, a different form.

I was thinking of something like, a normal table where all the result would be saved, a group by day in it, a normal query that will have "agentname, date, mails", and the crosstab based on the query and grouped after date, then select the hours.

Finaly, my issue is that i can't renew the query everyday. My boss wants to see the reports every day, and i can't show them unless i repeat this process every day and if i'm not the first at work, sometime the report turns messy.

I hope i made myself clear on this, so...anyone has any ideeas on how to solve this problem?

 

[dhookom]

click a particular buton...it will put "+1" in his field" what field? Are the clicking a button on the form that displays the crosstab? Does clicking the button add a record to a table or update a record?

It seems to me there should be a table involved here that we should know about.

Can you explain what you mean by "renew the query everyday"? What do you mean by "renew the query"?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[diezzz]

wow, i'm really that bad at explaining stuff?

Ok, as i said > crosstab query, names as rows, hours(7h, 8h, 9h etc) as columns. This is already based on a table (name,date,mail treated). The function in the crosstab would be countofmails, however only for particular hours.

A more abstract view of the crosstab :

7h 8h 9h etc....
diezz countofmails countofmails countofmails
john countofmails countofmails countofmails and so on.

for example, the countofmails function for diezz for 7h will show the number of mails i(diezz) treated between 7 and 8, for 8h, only the mails that i ve treat between 8 and 9 and so on.

The crosstab wasn't so hard to build, neither insering data into it (a simple form did it) however my problem is next, I want the crosstab to be grouped by days.

If as columns i've put hours, using hours(date) shows me the mails trated by a certain agents within some hours, but i don't want the today results add up with the ones that have been made yesterday.

For example if I treat today 3 mails between 7 and 8, and 2 between 8 and 9 the result for my row would be :

7h 8h 9h 10h etc

diezz 3 2 0 0

If i treat tomorrow between 7 and 8 1 mail, and between 8 and 9 2 mails that this is how my result should look like :

7h 8h etc

diezz 1 2

However it add the mails to the ones i did yesterday, witch is not good. So the result is :


7h 8h etc
diez 4 4


Insering data is no problem, nor the build of the query, the results are wrong because i don't know how to group by date in a crosstab query. If i use today() the problem is that the results of yesterday are not saved and i'll need them to for my future research.

Now i really hope i made myself clear. If anyone could help me with this problem i'll be really happy.

Thanks,

diez

 

[dhookom]

If you have the results displaying in a crosstab, you should be able to add a Row Heading in the crosstab based on DateValue([YourDateField]).

Maybe you should paste your SQL view into a reply if you can't figure this out.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[lespaul]

I want the crosstab to be grouped by days

Ok, do you have the date somewhere in the table or query?

the results are wrong because i don't know how to group by date in a crosstab query

You need to include the date in the fields in the query; probably/possibly coming from the table?

If i use today() the problem is that the results of yesterday are not saved and i'll need them to for my future research.

If the date is included in the table then you only need to change the WHERE clause of the query in order to get a different date range.

So, do you have the date that all this activity occurred stored in a table? Do you have that field included in the SQL of the query? Have you added a WHERE clause to the query to try and get historical information (ie yesterday).

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[diezzz]

Yes i do use a date field

Now this is my table :

Mails(EmployeeID,Mail,Date)

And the SQL of the crosstab is

TRANSFORM Count(mails.Mail) AS CountOfMail
SELECT mails.employeeid, Count(Mails.Mail) AS [Total Of Mail]
FROM Mails
WHERE day([date])=day(date())
GROUP BY mails.employeeID
PIVOT Hour([date]);

 

[diezzz]

i don't know why, but it seem to work now!?

In my where clause there was before

Where day(date) = day(date())

that was the problem?

 

[diezzz]

yeap it works now but i still have a problem. Could i build a parameter report on the crosstab query?

If i use the clause where day([date]) = [type the day here] it tells me that microsoft jet sql can't recognize my parameter.

 

[lespaul]

What are you typing in the parameter prompt?
What does the function DAY return? (F1 for help on finding this information)
Is what you are entering match the expected value that DAY returns?

Leslie

 

[dhookom]

You must specify the data type of all parameters used in crosstab queries. Select Query-Parameters and enter
[type the day here] Date/Time

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[diezzz]

TRANSFORM Count(mails.Mail) AS CountOfMail
SELECT mails.employeeid, Count(Mails.Mail) AS [Total Of Mail]
FROM Mails
WHERE day([date])= [type your day here] date/time
GROUP BY mails.employeeID
PIVOT Hour([date]);

won't work.

When i open the crosstab query i would like to ask me for the day. for example if today i want to see the yesterday report. So as parameter there would be a date, however when i open the crosstab it shows me as error message that the microsoft jetsql can't recongnize the [type your day here] command.

 

[PHV]

PARAMETERS [type your day here] Integer;
TRANSFORM Count(mails.Mail) AS CountOfMail
SELECT mails.employeeid, Count(Mails.Mail) AS [Total Of Mail]
FROM Mails
WHERE day([date])= [type your day here]
GROUP BY mails.employeeID
PIVOT Hour([date]);


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

 

[diezzz]

That i didn't know. this is great. Thank you very much

 

[diezzz]

if i do
PARAMETERS [type the date here] datetime;
......
where date([date]) = [type your day here] it won't work.

I know i'm a pain...

 

[dhookom]

You need to make up your mind if you want to use:
Day([date])
or
date([date])

Also, you should have learned by now that "it won't work" is not an acceptable reply unless you tell us what didn't work.

Come back with your complete SQL view pasted into a reply.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[diezzz]

i have a date field

when i open the query i'll want it to ask me to introduce the exact date, for example today is 11/16/2006.

so i use Date(mails.date) = [type your date here] and the parameter is set up like PARAMETERS [type your date here] datetime, however it gaves me an error message :

wrong number of arguments used with fucntion in query expression Date(mails.date) = ...