Counting

  • Thread starter Thread starter Jacob Frankham
  • Start date Start date
J

Jacob Frankham

Hi

I have 2 fields in my query

eg studentID and modules (these are ficticious names for simplicity !)

studentID is unique, but there may be several instances of the same module
for each studentID

I have a report which is grouping by studentID

I wish to have a textbox which counts all of the UNIQUE module names for
each studentID

I have been using Count([studentID]) but this returns the total number of
records for that studentID - I wish to see only the total number of
DIFFERENT values of modules

eg

l914458 Chemistry
Chemistry
Chemistry
Physics
Physics

I wish to see '2', not '5'

Many thanks

J
 
Hi,

Insert a "module name" header and footer inside the
Student ID Header in your report. Place the count function
in the module name footer. Set the "running Sum" property
for the count field to "No".
This will give you a count for each module name.

regards,
Andy.
 
whoops, misread your initial mail a bit. In the "module
name" footer use the AVG function."=AVG(module_name)" This
will return for example "1" for chemistry, and "1" for
physics.
Then use the SUM function to add up the two different
values and this will return "2". "=SUM(the above textbox
name)"

Hope that helps you more.
-----Original Message-----

Hi,

Insert a "module name" header and footer inside the
Student ID Header in your report. Place the count function
in the module name footer. Set the "running Sum" property
for the count field to "No".
This will give you a count for each module name.

regards,
Andy.
-----Original Message-----
Hi

I have 2 fields in my query

eg studentID and modules (these are ficticious names for simplicity !)

studentID is unique, but there may be several instances of the same module
for each studentID

I have a report which is grouping by studentID

I wish to have a textbox which counts all of the UNIQUE module names for
each studentID

I have been using Count([studentID]) but this returns
Click to expand...
the
total number of
records for that studentID - I wish to see only the
Click to expand...
total
number of
DIFFERENT values of modules

eg

l914458 Chemistry
Chemistry
Chemistry
Physics
Physics

I wish to see '2', not '5'

Many thanks

J


.
Click to expand...
.
Click to expand...
 
Hi there

Thanks for that

BUT......

It states a data type mismatch - does AVG not apply just to numbers?

Cheers

J
whoops, misread your initial mail a bit. In the "module
name" footer use the AVG function."=AVG(module_name)" This
will return for example "1" for chemistry, and "1" for
physics.
Then use the SUM function to add up the two different
values and this will return "2". "=SUM(the above textbox
name)"

Hope that helps you more.
-----Original Message-----

Hi,

Insert a "module name" header and footer inside the
Student ID Header in your report. Place the count function
in the module name footer. Set the "running Sum" property
for the count field to "No".
This will give you a count for each module name.

regards,
Andy.
-----Original Message-----
Hi

I have 2 fields in my query

eg studentID and modules (these are ficticious names for simplicity !)

studentID is unique, but there may be several instances of the same module
for each studentID

I have a report which is grouping by studentID

I wish to have a textbox which counts all of the UNIQUE module names for
each studentID

I have been using Count([studentID]) but this returns
Click to expand...
the
total number of
records for that studentID - I wish to see only the
Click to expand...
total
number of
DIFFERENT values of modules

eg

l914458 Chemistry
Chemistry
Chemistry
Physics
Physics

I wish to see '2', not '5'

Many thanks

J


.
Click to expand...
.
Click to expand...
Click to expand...
 
Back
Top