Query showing categories in rows and month in columns and total new patients in values

[jonquille80@hotmail.com]

Hello,



I have a table named patients with following fields : Id, name, Creation date, referred_by

I want to create a query showing the following information

Category JAN FEB MAR APR .......
Friend 5 2 1 4
Internet 3 1 1 3
Other 6 8 2 4

The numbers are the number of new patients grouped by Category and Month

Any help please?

Thanks

[isladogs]

You need to use a crosstab query. There is a wizard to help do this.

[jonquille80@hotmail.com]

Thanks

Is it possible to have a total for the rows as well ? The system does it for the columns, but I need it for the rows

Referral Source	Total Of ID	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
	3
	587	128	91	77	78	56		2			32	52	71
friends referral	85	4	5	7	3	16					35	12	3
Online Booking	4											2	2
Walk-In	1					1

[isladogs]

Not sure exactly what you mean
The results match your original request (apart from the empty category rows which could be excluded)

Older versions of Access up to 2010 included pivot tables but these were deprecated in A2013

Suggest you post some sample data together with the required output

[jonquille80@hotmail.com]

I mean there is a total column for the year for each category of referral

Can we have a total row for the total number of patients per month (including all categories)? (see line in red please)

Referral Source	Total Of ID	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
	3
	587	128	91	77	78	56		2			32	52	71
friends referral	85	4	5	7	3	16					35	12	3
Online Booking	4											2	2
Walk-In Total	1	132	96	84	81	1 73		2			67	66

Thanks

[isladogs]

You need to create an aggregate query from your crosstab to get the overall totals per month.
Or if being used in a report, you can easily get the totals per month.
HTH