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?
Advertisement
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,