Counting - can anyone clarify?

[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

 

[Guest]

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.

 

[Jacob Frankham]

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 !

 

[Marshall Barton]

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'

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

 

[Jacob Frankham]

You are a STAR

Thanks

Jake !!

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'

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

 

[Fons Ponsioen]

I would suggest group by "subject" (chemistry) and place
your text boxes and labels in the group footer.
This should work
Fons