CODEWITHSUNDEEP
sqlms-access
YoungTommy
YoungTommy

Reputation: 3

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?

Upvotes: 0

Views: 30

Answers (1)

Applecore
Applecore

Reputation: 4099

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,

Upvotes: 1

Related Questions