Report with monthly totals?

[KrisDdb]

Hello everyone!

I am working on a database that deals with cases which have unique case numbers 'casenum', with each of them having a date. And all the cases are divided into 4 types: A, B, C, and D. Now I am trying to run a report that would allow me to first select a date range (preferably in months over years) and fetch the total number of cases in that range, displayed with case type (i.e A,B,C,D) as the row fields and total cases in each month for the particular case type in column fields.



Ex: If type A = 40, B = 50, C = 35 and D = 60 in Jan 2011 and A = 25, B = 30, C = 40 and D = 50 in Feb 2011, the report should be able to display data in a tabular format with A,B,C,D in rows and in column one 40,50,35 and 60, and in column two 25,30,40, and 50 and in the last column, I plan to get the row totals.

Which would be the best way to do this? I need a few ideas to get started?

Appreciate your time and thoughts.

[Robeen]

Could you give me an example of a casenum from your table?

What is your Table design like?

[KrisDdb]

Robeen, thanks for replying.

casenum is a number datatype. ex: 225678

The casetype is a text datatype. Each casenum has a date/time datatype field 'date_opened' associated with it, which is actually the date on which the case was opened. Now what I want to achieve is, I want to group these cases month-wise for each case type. And I should be able to select from which month to which month. In simple words, I want all casenums with dates in June 2011 in one column. Then all casenums with dates in July 2011 in the next column.. and so on.
Let me know if you need more details.

[KrisDdb]

I have an idea to use a form where in I can give a start date and end date for the user to choose. Then I can do a button click on the form to generate the report. It is the report design, that I am having trouble with.

[KrisDdb]

This sounds simple.. but I am still baffled! Suggestions anyone?

[Robeen]

Hi Kris,

I'm going to try addressing this first:

In simple words, I want all casenums with dates in June 2011 in one column. Then all casenums with dates in July 2011 in the next column.. and so on.

I think what you need here is a crosstab query. You should research that because I believe it will give you what you need in one simple step.

I am not good at crosstab so . . .

Here's a possible alternative solution.

Create a Query and put all the fields you want into it.
For your Date field put something like this:
June2011: Year([StartDate]) & Month([StartDate])
In the Criteria for this field put this:
201106
Name the query QryJune2011.

When you run your query - you will get all records which have a year of 2011 and a month of 06.

Create similar queries for each month that you need.

Next:

Create a query and pull in all the monthly queries that you created.
Now add the month fields from the different queries to your new query.

If you have a field [like the A, B, C, D . . .] that is common to all your queries - join that field between all the queries before you run it.

If I knew your data better, I would be able to give you better instructions.
Still - you can play around with this and maybe get to where you need to be.

All the best!!

[KrisDdb]

Robeen

Thanks for your suggestion. It worked out pretty well The crosstab does the trick, but partially. However, that gave me an idea to design the criteria as per my need. I did not try the second idea though, cos I do not find it efficient to create so many queries. If you would be interested, here is the SQL:

TRANSFORM Count(DBA_cases.casenum) AS CountOfcasenum
SELECT DBA_cases.matcode, Count(DBA_cases.casenum) AS [Total Of casenum]
FROM DBA_cases
WHERE (((DBA_cases.matcode)="AUT" Or (DBA_cases.matcode)="PI" Or (DBA_cases.matcode)="WC" Or (DBA_cases.matcode)="S S"))
GROUP BY DBA_cases.matcode
PIVOT Format([date_opened],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

I now have an idea to make it more user friendly by creating a pop up form where in the date range can be chosen, and the report would generate the data for that.

[Robeen]

One of these days - I will master crosstab queries . . .

All the best!