Arranging Columns in Microsoft Access

I am using Microsoft Access to generate a report, but I’m encountering difficulty in figuring out how to have the information displayed in way that will comply to my preference. Specifically: the manner in which the data in my table is currently displayed is depicted below:

Day     Provider    Patient 
1/27    Johnson     Claire
1/27    Johnson     Tim
1/28    Johnson     Craig
1/27    Jane        Michael
1/28    Jane        Lisa

However, I would like the data to display as follows:

Provider    1/27    1/28
Johnson     2        1
Jane        1        1

My understanding of SQL is admittedly rudimentary, so I have been only able to come up with the following query:

SELECT COUNT (Patient), Day, Provider
FROM (SELECT DISTINCT Patient,Day, Provider FROM Records)
GROUP BY Day, Provider;

Is what I’m looking for possible in MS Access?

Answer

Have a look at using the Query Wizard in Access to create a Crosstab Query.

• Start by opening the wizard, and selecting “Crosstab Query Wizard” from the choices given when you start the wizard;
• Select the table required – I think that this is “Records” in your case;
• Select “Provider” as row header in the next screen;
• Select “Day” as the column header in the next screen;
• Select “Date” to group by in the next screen;
• Select “Count” as the value to be shown.

Your SQL should look like:

TRANSFORM Count(Records.[Patient]) AS CountOfPatient
SELECT Records.[Provider], Count(Records.[Patient]) AS [Total Of Patient]
FROM Records
GROUP BY Records.[Provider]
PIVOT Format([Day],"Short Date");

Regards,