By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,962 Members | 1,998 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,962 IT Pros & Developers. It's quick & easy.

MS Access - Report & Querry Building

P: 1
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.
Mar 9 '07 #1
Share this Question
Share on Google+
8 Replies


NeoPa
Expert Mod 15k+
P: 31,344
Please post the MetaData of your tables. An example follows which you can use in your reply for proper formatting.


Table Name=tblStudent
Expand|Select|Wrap|Line Numbers
  1. Field; Type; IndexInfo
  2. StudentID; AutoNumber; PK
  3. Family; String; FK
  4. Name; String
  5. University; String; FK
  6. Mark; Numeric
  7. LastAttendance; Date/Time
Mar 10 '07 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
This can be done using a crosstab query.

For example ...
Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Count(Receipts) As NumReceipts
  2. SELECT PatientName, Count(Receipts) As TotalReceipts
  3. FROM tblPatients
  4. GROUP BY PatientName
  5. PIVOT Format(ReceiptDate, "mmm") 
  6. IN ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
  7.  
Mary
Mar 10 '07 #3

NeoPa
Expert Mod 15k+
P: 31,344
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?
Mar 10 '07 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Mar 10 '07 #5

NeoPa
Expert Mod 15k+
P: 31,344
The saddest bit is - I can hear the smugness in your laugh!
Mar 11 '07 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
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!
Mar 11 '07 #7

NeoPa
Expert Mod 15k+
P: 31,344
The CrossTab answer is grand.
I wasn't knocking to be sure - Just wished I used them more.
Mar 11 '07 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Mar 11 '07 #9

Post your reply

Sign in to post your reply or Sign up for a free account.