Counting - can anyone clarify?

  • 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
 
i don't know it this helps.. but a quick idea come to me..
maybe basing the record source in a query with the unique
values set to true.
 
I cant because I need to have the duplicates in order to use Count on
another field.

Basically, all I want to be able to do is add up how many values exist in
[myField] IGNORING any duplicates, and I need to do this on my report (not
in my query)

Agghhh!!!!!

J !
 
Jacob said:
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'
Click to expand...

Add another level of grouping on the Module field with an
invisible Group Header or Footer. Add a text box named
txtModCnt to the header/footer with control source
expression =1 and RunningSum set to Over Group.

Then a text box in the student footer can display the unique
modules by using the expression =txtModCnt
 
You are a STAR

Thanks

Jake !!
Marshall Barton said:
Jacob said:
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'
Click to expand...

Add another level of grouping on the Module field with an
invisible Group Header or Footer. Add a text box named
txtModCnt to the header/footer with control source
expression =1 and RunningSum set to Over Group.

Then a text box in the student footer can display the unique
modules by using the expression =txtModCnt
Click to expand...
 
I would suggest group by "subject" (chemistry) and place
your text boxes and labels in the group footer.
This should work
Fons
 
Back
Top