counting distinct records

[Todd P]

I have a report which groups LocID by month and lists the details for each
record. Some of these months will extend for pages so I want a summary text
box to show the total count of distinct locations and total records for that
month. Example: June- (3 Locations, 32 records) The total records of that
month is easy with =Count([LocID]) in the control source but I can't seem to
find a way to count distinct LocID's. Any thoughts? Thank you!

 

[Douglas J. Steele]

Create a query that only returns the distinct locations ("SELECT DISTINCT
LocID FROM MyTable") and use it.

If you want a count of locations by month, try "SELECT Month(MyDate),
Count(LocID) FROM MyTable GROUP BY Month(MyDate)"

 

[Todd P]

Thanks Douglas, I know how to make a Distinct query, just not how to 'use
it' in this report. The report already has a very lengthy/complex record
source SQL statement in the Report_Open sub. Are you suggesting running
another query in the module and filling the text box with the result? If
so, could you elaborate?

Create a query that only returns the distinct locations ("SELECT DISTINCT
LocID FROM MyTable") and use it.

If you want a count of locations by month, try "SELECT Month(MyDate),
Count(LocID) FROM MyTable GROUP BY Month(MyDate)"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a report which groups LocID by month and lists the details for each
record. Some of these months will extend for pages so I want a summary
text box to show the total count of distinct locations and total records
for that month. Example: June- (3 Locations, 32 records) The total records
of that month is easy with =Count([LocID]) in the control source but I
can't seem to find a way to count distinct LocID's. Any thoughts? Thank
you!

 

[Douglas J. Steele]

You can make the control source of the text box be something like

=DCount("*", "qryMyDistinctQuery")

Don't forget the equal sign if you go that route.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks Douglas, I know how to make a Distinct query, just not how to 'use
it' in this report. The report already has a very lengthy/complex record
source SQL statement in the Report_Open sub. Are you suggesting running
another query in the module and filling the text box with the result? If
so, could you elaborate?

Create a query that only returns the distinct locations ("SELECT DISTINCT
LocID FROM MyTable") and use it.

If you want a count of locations by month, try "SELECT Month(MyDate),
Count(LocID) FROM MyTable GROUP BY Month(MyDate)"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a report which groups LocID by month and lists the details for
each record. Some of these months will extend for pages so I want a
summary text box to show the total count of distinct locations and total
records for that month. Example: June- (3 Locations, 32 records) The
total records of that month is easy with =Count([LocID]) in the control
source but I can't seem to find a way to count distinct LocID's. Any
thoughts? Thank you!

 

[Todd P]

Doug, The problem with that is the textbox recordset is not sync'ed to the
report recordset and the report recordset is dynamic SQL. The LocID's are
already there under the month group; just need a way to count the distinct
ID's in that month. Sorry if I'm not making it clearer.

You can make the control source of the text box be something like

=DCount("*", "qryMyDistinctQuery")

Don't forget the equal sign if you go that route.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks Douglas, I know how to make a Distinct query, just not how to 'use
it' in this report. The report already has a very lengthy/complex record
source SQL statement in the Report_Open sub. Are you suggesting running
another query in the module and filling the text box with the result? If
so, could you elaborate?

Create a query that only returns the distinct locations ("SELECT
DISTINCT LocID FROM MyTable") and use it.

If you want a count of locations by month, try "SELECT Month(MyDate),
Count(LocID) FROM MyTable GROUP BY Month(MyDate)"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I have a report which groups LocID by month and lists the details for
each record. Some of these months will extend for pages so I want a
summary text box to show the total count of distinct locations and total
records for that month. Example: June- (3 Locations, 32 records) The
total records of that month is easy with =Count([LocID]) in the control
source but I can't seem to find a way to count distinct LocID's. Any
thoughts? Thank you!