COUNT(DISTINCT <expr>) is a report or its equivalent.

[ThePixelMines]

This has been driving me crazy. It seems so simple.

I've got a class roster (report) that totals tuition, number of students per class and total students. I want a total of families. Right now, I have a table for families with a one-to-many relationship to a student table.

The result I'm looking for would be similar to that of

COUNT(DISTINCT [family_ID])

but that's not working in the report. I get a warning that I have an operand without an operator.

What am I missing here?



Check it, Fool!

http://www.thepixelmine.com

 

[ThePixelMines]

The headline on this was supposed to be:

COUNT(DISTINCT <expr>) in a report...

not "...is a report..."

I can't seem to find "Edit Post" on this site.

Check it, Fool!

http://www.thepixelmine.com

 

[dhookom]

There is no Count(distinct,...) in Access queries. If you want this, you may need to use a subquery or something. Since I expect your report is not sorted by family, you won't be able to use a running sum in the report.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[ThePixelMines]

I could easily sort by family. What do you mean by 'running sum in the report?'

Check it, Fool!

http://www.thepixelmine.com

 

[dhookom]

If you sort/group by Family then you can display the group header. Add a text box to the family header and set its properties:

Name: txtCountFamily
Control Source: =1
Running Sum: Over All
Visible: No

Then add a text box to the report footer:

Control Source: = txtCountFamily

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[ThePixelMines]

That did the trick. Works like a charm!

Thanks, dhookom.

Check it, Fool!

http://www.thepixelmine.com