MS Access - Report & Querry Building

[dilippshah10]

I have to prepare Data Analysis Report. The data is of 12 months. I want Report in a Columnar Format and comparative type i.e Column should be Jan to Dec. and in a single row, amount should be displayed under each month column. The field in Table is only one. How to achieve this things. Pl. help me.

Format of Report is as under:
Receipts from Patients
----------------------------------
Paitent...Jan..Feb...Mar..Apr..May..June..Jul..Aug ..Sept..Oct.Nov..Dec..Total
-----------
1. xyz......5......10....7.....9.....6......12......1 0.....0....5......9......10.....11.... 94


In the table, there is only one field for Receipts from Patients. I want to bifurcate Receipts on each month basis and display in single row only. Also there is not certain that everytime there will be 12 months. It may be one month, two months etc. so column heading be displayed based on no. of months in the data.

I need the help immediately. Pl. help me. I am a novice in this field. Pl. explain elaborately, if possible.

[NeoPa]

Please post the MetaData of your tables. An example follows which you can use in your reply for proper formatting.


Table Name=tblStudent

Field; Type; IndexInfo
StudentID; AutoNumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

[MMcCarthy]

This can be done using a crosstab query.

For example ...

TRANSFORM Count(Receipts) As NumReceipts
SELECT PatientName, Count(Receipts) As TotalReceipts
FROM tblPatients
GROUP BY PatientName
PIVOT Format(ReceiptDate, "mmm") 
IN ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
 

Mary

[NeoPa]

It can also be done without a CrossTab with a bunch of IIf()s, but you'd need to link it (LEFT JOIN) with a small table including all the months.
I suppose the IN() bit ensures that all months are included in the results?

[MMcCarthy]

It can also be done without a CrossTab with a bunch of IIf()s, but you'd need to link it (LEFT JOIN) with a small table including all the months.
I suppose the IN() bit ensures that all months are included in the results?

YUP!

That's 2for, I'm on a roll.

[NeoPa]

The saddest bit is - I can hear the smugness in your laugh!

[MMcCarthy]

The saddest bit is - I can hear the smugness in your laugh!

Stop knocking me. I'm feeling all powerful. Sure it won't last though, Sigh!

[NeoPa]

The CrossTab answer is grand.
I wasn't knocking to be sure - Just wished I used them more.

[MMcCarthy]

The CrossTab answer is grand.
I wasn't knocking to be sure - Just wished I used them more.

They are very powerful for this kind of thing.