Numerical Report based on number of CaseTypes / Dated

[Cyberdyne]

Hi guys I am working on access 2007 database that deals with entering client information and then outputting reports.

The reports I am trying to setup is how many cases of a certain kind has a branch produced from this date to this date.

I have two fields in the database for this, one called Branch, it contains drop down menue with locked values NW, NE, SW and another field called CaseType that contains drop down menue with values Hardest, Hard, Normal, Easy.

Should ooks like this:

Report Period: Month, Day, Year to Month, Day, Year

Branch | Hardest | Hard | Normal | Easy | Total
NW 4 35 14 23 76
NE 23 42 3 76 144
SW 20 12 34 13 79

How do I calculate the numbers, in other words when I create the report what should the formula/code look like for those fields? Also how will the Date option be implemented? Do I need to create a separate report of some sort?

thanks for your help, I am new to databeses but have been on the scripts for quite some time =)

-=Cyberdyne=-

[Delerna]

Hi Cyberdyne
What do the tables look like?
By the term fields I guess you mean drop down boxes on a form
that are selected and then the report is called up by a button on the form?

[Cyberdyne]

Hi Cyberdyne
What do the tables look like?
By the term fields I guess you mean drop down boxes on a form
that are selected and then the report is called up by a button on the form?

yea the fields are drop down boxes on the form. Branch and CaseType each containing different options. The reports are called outside the form from a switchboard "Reports"

[Delerna]

and what do the tables look like?
In particular tha data in the tables from which the report is to be built.

[Cyberdyne]

and what do the tables look like?
In particular tha data in the tables from which the report is to be built.

the data is text so....

table Branch has NE, NW, SW, etc as options
table CaseType has Hardest, Hard, Normal, Easy etc
There is also a Date Field for the day the info was entered

so for the report I need to see the numbers so for example from Dec 1 to Dec 31 in the Branch NE - an x number of Hard, Easy and Normal cases came in, and then the total number of these....

ther report will be tabular

Branch>>|Hard| >Easy|Normal|To tal>|
NE>>>> |>2>>|>45>|>>41 >>|>88>|
NW>>> |>23>|>6>>|>>32 >>|>61>|
SW>>>>|>14>|>12 >|>>7>>>|>43> |

[Cyberdyne]

I know how to create a report and Add the Field Branch to it but after that how do I add the Easy, Hard, Normal in separate boxes and make it show the number of cases that came in for the particular Branch that month?

I imagine it would be a formula or code of some sort

First there would be a condition to the report to only show data from X to X dates

then it would show fields with formula inside... for this branch there were an x number of CaseType (Easy, Hard, or Normal) and then the total of all of the CaseTypes for that month.

[Delerna]

Sorry Cyberdyne, I think we might be on different rails of the same track.
I understand that you have a form with two combo boxes on it.
one is linked to a branch table with NE,WW etc as the data in it.
one is linked to a Case type table with hardest, hard etc as the data in it.
Actually, I'm not entirely sure how these combo boxes will affect the report
because the report you mention shows all branches and case types.

Anyway,
When I asked what does the table look like I was referring to the table where the numerical info is going to come from. Ie the 2 45 41 88 etc from below.

Branch>>|Hard| >Easy|Normal|To tal>|
NE>>>> |>2>>|>45>|>>41 >>|>88>|
NW>>> |>23>|>6>>|>>32 >>|>61>|
SW>>>>|>14>|>12 >|>>7>>>|>43> |




From your posts so far I guess that there is a table that has the date and the number of each type of case that was produced in each branch on that date.

What I need to know is what does that table look like. What are the fields and what does the data look like. I guess the formula you talk about will be an aggregate query on that table that the report will use but I need to see what the table and some of its data looks like in order to see how the query should be constructed, or even if I am on the same track as you.
Bear with me and we will sort it out.

[NeoPa]

Assuming you have a table ([tblA]) with these two fields ([Branch] & [CaseType]) then the query you need to run your report from would look something like :

SELECT [Branch],
       Sum(IIf([CaseType]='Hardest',1,0)) AS [Hardest],
       Sum(IIf([CaseType]='Hard',1,0)) AS [Hard],
       Sum(IIf([CaseType]='Normal',1,0)) AS [Normal],
       Sum(IIf([CaseType]='Easy',1,0)) AS [Easy],
       Count(*) AS [Total]
FROM [tblA]
GROUP BY [Branch]
ORDER BY [Branch]

PS. Feel free to ask for explanations if there's anything not clear here Cyberdyne. As such a long-standing moderator we'll pull out the stops for you :)

[Cyberdyne]

Assuming you have a table ([tblA]) with these two fields ([Branch] & [CaseType]) then the query you need to run your report from would look something like :

SELECT [Branch],
       Sum(IIf([CaseType]='Hardest',1,0)) AS [Hardest],
       Sum(IIf([CaseType]='Hard',1,0)) AS [Hard],
       Sum(IIf([CaseType]='Normal',1,0)) AS [Normal],
       Sum(IIf([CaseType]='Easy',1,0)) AS [Easy],
       Count(*) AS [Total]
FROM [tblA]
GROUP BY [Branch]
ORDER BY [Branch]

PS. Feel free to ask for explanations if there's anything not clear here Cyberdyne. As such a long-standing moderator we'll pull out the stops for you :)

Thanks so much NeoPa, working on it =)