Stats in Access - SQL

[djmixer135]

Hi there,

I've done some Googling on this but can't seem to work it out.

I have a data set containing a number of organisation records. These records display a status and a date and the person responsible for calling to arrange a meeting with these organisations.

Company	Status	Today's Date	Caller
a	Booked	01/01/2019	1
b	Call Back	01/01/2019	2
c	Call Back	01/01/2019	1
d	Declined	01/01/2019	1
e	Booked	01/01/2019	2

How can I achieve the following result with an SQL query? This should only show the results for where today's date = TODAY()

Caller	Count of 'Booked'	Count of 'Call Back' and 'Declined'	Count of 'Other'
1	1	2	0
2	1	1	0
3	0	0	0

Many thanks in advance!

[isladogs]

First create an aggregate query by clicking the totals button on the ribbon.
Add the 4 fields. Each will have Group By in the Totals row of the query design.
Change that to Where for the date field and filter that for Date()
Next change the Company field to Count
Save the query and run it to check its working

Now create a crosstab query based on the above to get the required result

[June7]

Looks like a CROSSTAB query.

TRANSFORM Count(Calls.CallDate) AS CountOfCallDate
SELECT Calls.Caller
FROM Calls
GROUP BY Calls.Caller
PIVOT IIf([Status]="Call Back" Or [Status]="Declined","CallBack_Dec",IIf([Status]="Booked","Booked","Other")) In ("Booked","CallBack_Dec","Other");

However, since there is no record for caller 3 there is record in the output. If you need to show all persons even if they did not make calls, join this query to a Persons table that has a unique record for each person.

More about CROSSTAB http://allenbrowne.com/ser-67.html