defect of access query affect the report

[maswien]

Access doesn't support function count(distinct ...), for some report this function is very useful, how the access report programmer work around this issue?

 

[maswien]

Without this function, I doubt that access is a good report tool.

 

[PCLewis]

I wrote a query in Oracle SQL that did the same in Oracle as a select count(distinct .. and then used the alternate form Oracle in Access.

SELECT Count(*) AS Expr1
FROM (select distinct subinfo3 from query3) AS Q1;

Which worked.

This is what the query builder in Access translated my SQL to:

SELECT Count(*) AS Expr1
FROM [select distinct subinfo3 from query3]. AS Q1

Learn something new every day I guess.

 

[dhookom]

If you create a report with a grouping on the field you would count distinct...
Add a text box to the field's group header:
Name: txtCountDistinct
Control Source: =1
Running Sum: Over All
Add a text box to the Report Footer section:
Control Source: =txtCountDistinct

Why would you blame this issue on the report rather than JET SQL syntax?

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]