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..Oc t.Nov..Dec..Tot al
-----------
1. xyz......5..... .10....7.....9. ....6......12.. ....10.....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.
8  1555  NeoPa 32,584
Recognized Expert Moderator MVP
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 14,534
Recognized Expert Moderator MVP
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 32,584
Recognized Expert Moderator MVP
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 14,534
Recognized Expert Moderator MVP
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 32,584
Recognized Expert Moderator MVP
The saddest bit is - I can hear the smugness in your laugh!
MMcCarthy 14,534
Recognized Expert Moderator MVP
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 32,584
Recognized Expert Moderator MVP
The CrossTab answer is grand.
I wasn't knocking to be sure - Just wished I used them more.
MMcCarthy 14,534
Recognized Expert Moderator MVP
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.
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: Thierry Schmitt |
last post by:
Hello all,
I m quite sure that s a common problem and even that the answer to it
has been given somewhere, forgive my lack of perspicacity but here is
my problem:
In the vba code of a button on a form, leading to the preview of a
report, I ve got a variable (string) which has been filled with some
information from a SQL querry. I want to export this variable to the
report to be previewed.
The report has been built before with the...
|
by: Mr. Smith |
last post by:
Hello all,
My code can successfully open, write to, format and save several
worksheets in a workbook then save it by a given name, close and quit excel.
My problem is that if I try and do it again, Excel hangs. OR if I open
Excel again (say from a desktop icon) before I close Access, Excel
hangs. (this has happened for both 97 & 2000 for me)
I of course thought that I mustn't be unloading a variable properly.
|
by: ward |
last post by:
Greetings.
Ok, I admit it, I bit off a bit more than I can chew.
I need to complete this "Generate Report" page for my employer and I'm
a little over my head. I could use some additional assistance. I say
additional because I've already had help which is greatly appreciated.
I do try to take the time and understand the provided script in hopes
on not having to trouble others on those. But here it goes...
|
by: rdemyan |
last post by:
My app creates a building report.
My users have requested that I provide functionality to e-mail these
"building reports" to building managers once a month.
So assuming that I have the following table that lists a building manager's
name, e-mail address and their building, is there a way that I can automate
this to send these monthly e-mails from Outlook. Ideally this would be a
click one button type of action:
|
by: jimc52 |
last post by:
Hello Everyone: I am hoping one of the gurus here will give me some help.
I have designed a form with some check boxes. I put the checkboxes there on the form so later I could use them as flags on separate reports. Just to answer a few questions, yes, the subform is properly tied to the main form and they are all bound controls...been there, done that. Now, the purpose of a check box, as I see it is to have two logical conditions (checked =...
| | |
by: pic078 via AccessMonster.com |
last post by:
I need serious help - I have a frontend/backend Access database (2 MDE Files)
that remains stuck in task manager after exiting the application - you can't
reopen database after exiting as a result - I have read every post out there
and spent hours trying to figure out the problem with no success whatsoever -
I have constrained the problem to one form however, and I think it's hiding
somewhere in my code associated with this form, which is...
|
by: dbull4 |
last post by:
HI I am trying to write a count querry where we can count the ratings on a school report. IE How many students in a year level got A's, B's C's etc. I have a rating table and use combo boxes to enter ratings into a form.
|
by: Webstorm |
last post by:
Hi, I hope someone can help me sort this out a bit, Im completely lost.
Here is the page I am working on:
http://www.knzbusinessbrokers.com/default.asp
I have 3 search critera that I need to use when querying the database. Right now it is only looking for a match on one of those dropdowns and not all 3. can anyone help?
Here is the code:
<form BOTID="0" METHOD="POST" action="businessforsale_interface/Results/test3.asp">
|
by: prakashwadhwani |
last post by:
Hi !! I'm about to develop a new project for a client.
Should I go about it in Access 2003 or 2007 ? Purchasing it either
for me or for my client is not a major consideration here ... what I'd
like to know is the stability, speed & ease of use of both the
products.
I believe Access 2007 has a new file format too and that it may be
slower.
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
