Calculate total and mode reponse to a text field

[Pharma Down]

Hi

I want to create a report that shows how many times a field has received a particular response in a certiain time period
Eg

The form collects info -
- field 1: person A, B or C (drop down list)
- field 2: date
- field 3: option x, y, or z (drop down list)

I want to show

Each month, for each person
- field 3 option x: how many times
- field 3 option y: how many times
- field 3 option z: how many times

The report wizard seems to create this sort of summary info for number fields, but nothing else.

Is it possible? If so, how?

Andy

 

[pr2-eugin]

Not sure if this is what you are looking for.. but in the Query Design, add the required fields, the hit Totals,

then use this a new column,

OptionXTotal : Count(IIf([tablename].[field3] = 'option x',1,Null))

Make sure the Totals is set to Expression..

 

[Pharma Down]

Would I have to make a new column in the query for each option, ie:

Column 1
OptionXTotal : Count(IIf([tablename].[field3] = 'option x',1,Null))

Column 2
OptionXTotal : Count(IIf([tablename].[field3] = 'option y',1,Null))

Column 3
OptionXTotal : Count(IIf([tablename].[field3] = 'option z',1,Null))

I ask this, because field 3 has 25 options, field 4 has 24 options, field 5 has 5 options' and fields 6, 7 and 8 have options that are designed to be able to change - be easily updated... and if the options are set in separate code you obvioulsy can't update them without adding new code.

 

[pr2-eugin]

Wow that is a lot of options.. Have you tried a simple GroupBy clause?

 

[Pharma Down]

You say that as though you think I know what one is!

 

[pr2-eugin]

LOL.. Okay to learn about the Groupby clause look into the following links..
First basic on how Group by actually works..
http://www.w3schools.com/sql/sql_groupby.asp
This link will have in details how to perform (more MS Access oriented)
http://fontstuff.com/access/acctut04.htm

 

[Pharma Down]

Hmmm... this looks like it could be useful. Obviously, I haven't a clue what it means, but it looks as though it might help if I can get my head around it.

The second link doesn't work for me!

Andy

 

[Pharma Down]

If I understand this correctly, in the example at: http://www.w3schools.com/sql/sql_groupby.asp
If the 'customer' was replaced by the options for my field (from a drop down list) and the 'order price' was simply yes or no, could that then tell me how many times per month each 'customer' was listed, ie the total number of yesses rather than the total value of all orders combined per customer?

The context is:
In my database I can record healthcare 'interventions' - these will occur on a date, relate to a location (drop down list), relate to a person (drop down list), be a particular type of intervention (ie therapeutic, legal, financial, etc) (drop down list), be of a grade of severity (drop down list)

I want to be able to say how many interventions occurred in
- x Month
- per location
- per person
- type 1 # (% of total)
- type 2 # (% of total)
- mode type
- grade A # (% of total)
- grade B # (% of total)
- mode grade
(yes, more complex than that, based on number of types etc, but you get the idea)