Difficult query

[Pietro]

Hi,
I have a table called WT,contains the fields "Type of call","DateW" and
"ID", this table is used to by users to add rows that determine type of calls
received in a call center,I want to create a query with the following
criteria:
1- To view number of calls received in each type per day.
2- To show the field "Type of call" in this query,even the type that wa not
used,and to view number 0 in the count field.
3-Prcentage of each type of call .

 

[Smartin]

Hi,
I have a table called WT,contains the fields "Type of call","DateW" and
"ID", this table is used to by users to add rows that determine type of calls
received in a call center,I want to create a query with the following
criteria:
1- To view number of calls received in each type per day.
2- To show the field "Type of call" in this query,even the type that wa not
used,and to view number 0 in the count field.
3-Prcentage of each type of call .

1- Simple aggregate ("totals") query. Group by date, count on type.

2- (a) Build a second table Types with one column that contains each
possible type. Create a query that joins both tables on type and
change the join to show all records from Types and corresponding
records from WT. Bring in DateW, and type /from Types/. Again, group
by date and count on type. Or,

2- (b) Create a helper query Types that has WT.Type. Group by this
field. Construct a new query as in solution A using the helper query
instead of a new table. Note this will only return types that are
actually present in WT.

3- I stumbled here, thinking that DCOUNT should work, but I could not
quickly make it work. Assuming you want percentage of type by day,
this subquery approach gets the right answers:

SELECT W1.DateW, W1.[Type of call],
(SELECT COUNT(*) FROM WT AS W2
WHERE W2.[Type of call] = W1.[Type of call] AND W2.DateW =
W1.DateW) /
(SELECT COUNT(*) FROM WT AS W3
WHERE W3.DateW = W1.DateW) AS PERCENTAGE
FROM WT AS W1
GROUP BY W1.DateW, W1.[Type of call];