counting distinct rows using a textbox

[Guest]

Hello,
Is there a way to count distinct rows using the controlSource property of a
textbox?
writing: =count([orderId]) returns all the OrderId's, and I want to count
only the distinct orderId's.
thanks

 

[Jeff Boyce]

Count DISTINCT ...?

Jeff Boyce
<Access MVP>

 

[Ron Weiner]

Jeff

I am not aware of a Count DISTINCT in Access. I have used it in Sql Server
with this syntax

SELECT COUNT(DISTINCT [Column]) FROM Table WHERE ...

This syntax does not seem to work in Access. I am under the impression that
dshemesh is going to have to write a function that opens a recordset with
whatever conditions he wants, moves to the last record and reports the
record count. Is there any other way??

Ron W

Count DISTINCT ...?

Jeff Boyce
<Access MVP>

Hello,
Is there a way to count distinct rows using the controlSource property

of

a
textbox?
writing: =count([orderId]) returns all the OrderId's, and I want to count
only the distinct orderId's.
thanks

 

[Douglas J. Steele]

That doesn't work in Access, Jeff.

dshemesh: I don't believe there's anyway to do what you want, unless you
want to have a separate query that selects the distinct rows, and does a
count on that subquery.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Count DISTINCT ...?

Jeff Boyce
<Access MVP>

Hello,
Is there a way to count distinct rows using the controlSource property

of

a
textbox?
writing: =count([orderId]) returns all the OrderId's, and I want to count
only the distinct orderId's.
thanks

 

[Ron Weiner]

Here is a Function that I think does what he wants.

Public Function CountDistinctRows(strTable As String, strColumn As String)
As Long
Dim rs As ADODB.Recordset
Dim strSql As String

strSql = "Select Distinct " & strColumn & " From " & strTable
strSql = strSql & " Where not " & strColumn & " Is Null"
Set rs = New ADODB.Recordset
rs.Open strSql, CurrentProject.Connection, adOpenStatic
rs.MoveLast
CountDistinctRows = rs.RecordCount
rs.Close
Set rs = Nothing
End Function

In the Text box Control Source = countDistinctRows ("Table","Column") to
return the count of distinct rows where the rows are Not Null.

Ron W

That doesn't work in Access, Jeff.

dshemesh: I don't believe there's anyway to do what you want, unless you
want to have a separate query that selects the distinct rows, and does a
count on that subquery.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Count DISTINCT ...?

Jeff Boyce
<Access MVP>

Hello,
Is there a way to count distinct rows using the controlSource property

of

a
textbox?
writing: =count([orderId]) returns all the OrderId's, and I want to count
only the distinct orderId's.
thanks

 

[Guest]

First of all, thank you all very much for the help.
Ron,
The function you wrote works, but it doesn't help me, since I want to count
distinct rows using a filter (and not all distinct rows). My filters are
exactly the 'Group By' I am using in the report (it began as a form, but now
it's a report...), so if there was an easy way counting distinct rows using
access, the filters I need would have automatically been there...
Come to think of it, if there was a way to ask access how many groups were
under a certain 'Group By' it would solve my problem. For example, if I am
grouping on three levels: level0, level1 ans level2, is there a way to ask
access how many groups of level1 appeared under level0?
thanks again.

Here is a Function that I think does what he wants.

Public Function CountDistinctRows(strTable As String, strColumn As String)
As Long
Dim rs As ADODB.Recordset
Dim strSql As String

strSql = "Select Distinct " & strColumn & " From " & strTable
strSql = strSql & " Where not " & strColumn & " Is Null"
Set rs = New ADODB.Recordset
rs.Open strSql, CurrentProject.Connection, adOpenStatic
rs.MoveLast
CountDistinctRows = rs.RecordCount
rs.Close
Set rs = Nothing
End Function

In the Text box Control Source = countDistinctRows ("Table","Column") to
return the count of distinct rows where the rows are Not Null.

Ron W

That doesn't work in Access, Jeff.

dshemesh: I don't believe there's anyway to do what you want, unless you
want to have a separate query that selects the distinct rows, and does a
count on that subquery.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Count DISTINCT ...?

Jeff Boyce
<Access MVP>

Hello,
Is there a way to count distinct rows using the controlSource property of
a
textbox?
writing: =count([orderId]) returns all the OrderId's, and I want to count
only the distinct orderId's.
thanks

 

[Jeff Boyce]

Thanks, Doug. Perhaps I <sent> too quickly.

What DOES work is to "chain" two queries together. The first query derives
the unique values, using the Unique Values property. The second query uses
the Sum() function to count the number of unique values in the first query.

This may not meet the original poster's needs...

--
Good luck

Jeff Boyce
<Access MVP>

That doesn't work in Access, Jeff.

dshemesh: I don't believe there's anyway to do what you want, unless you
want to have a separate query that selects the distinct rows, and does a
count on that subquery.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Count DISTINCT ...?

Jeff Boyce
<Access MVP>

Hello,
Is there a way to count distinct rows using the controlSource property

of

a
textbox?
writing: =count([orderId]) returns all the OrderId's, and I want to count
only the distinct orderId's.
thanks

 

[Guest]

Here is the answer I got in the reports newsgroup (It obviously works...):

Create a group heading for [TheColumn] field. Add a text box in the group
heading:
Name: txtCount
Control Source: =1
Running Sum: Over All
Visible: No
Add a text box to the Report Footer:
Control Source: = txtCount

First of all, thank you all very much for the help.
Ron,
The function you wrote works, but it doesn't help me, since I want to count
distinct rows using a filter (and not all distinct rows). My filters are
exactly the 'Group By' I am using in the report (it began as a form, but now
it's a report...), so if there was an easy way counting distinct rows using
access, the filters I need would have automatically been there...
Come to think of it, if there was a way to ask access how many groups were
under a certain 'Group By' it would solve my problem. For example, if I am
grouping on three levels: level0, level1 ans level2, is there a way to ask
access how many groups of level1 appeared under level0?
thanks again.

Here is a Function that I think does what he wants.

Public Function CountDistinctRows(strTable As String, strColumn As String)
As Long
Dim rs As ADODB.Recordset
Dim strSql As String

strSql = "Select Distinct " & strColumn & " From " & strTable
strSql = strSql & " Where not " & strColumn & " Is Null"
Set rs = New ADODB.Recordset
rs.Open strSql, CurrentProject.Connection, adOpenStatic
rs.MoveLast
CountDistinctRows = rs.RecordCount
rs.Close
Set rs = Nothing
End Function

In the Text box Control Source = countDistinctRows ("Table","Column") to
return the count of distinct rows where the rows are Not Null.

Ron W

That doesn't work in Access, Jeff.

dshemesh: I don't believe there's anyway to do what you want, unless you
want to have a separate query that selects the distinct rows, and does a
count on that subquery.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Count DISTINCT ...?

Jeff Boyce
<Access MVP>

Hello,
Is there a way to count distinct rows using the controlSource property
of
a
textbox?
writing: =count([orderId]) returns all the OrderId's, and I want to
count
only the distinct orderId's.
thanks

 

[Douglas J. Steele]

If you were asking for a report, why did you post to
microsoft.public.access.forms?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Here is the answer I got in the reports newsgroup (It obviously works...):

Create a group heading for [TheColumn] field. Add a text box in the group
heading:
Name: txtCount
Control Source: =1
Running Sum: Over All
Visible: No
Add a text box to the Report Footer:
Control Source: = txtCount

First of all, thank you all very much for the help.
Ron,
The function you wrote works, but it doesn't help me, since I want to count
distinct rows using a filter (and not all distinct rows). My filters are
exactly the 'Group By' I am using in the report (it began as a form, but now
it's a report...), so if there was an easy way counting distinct rows using
access, the filters I need would have automatically been there...
Come to think of it, if there was a way to ask access how many groups were
under a certain 'Group By' it would solve my problem. For example, if I am
grouping on three levels: level0, level1 ans level2, is there a way to ask
access how many groups of level1 appeared under level0?
thanks again.

Here is a Function that I think does what he wants.

Public Function CountDistinctRows(strTable As String, strColumn As String)
As Long
Dim rs As ADODB.Recordset
Dim strSql As String

strSql = "Select Distinct " & strColumn & " From " & strTable
strSql = strSql & " Where not " & strColumn & " Is Null"
Set rs = New ADODB.Recordset
rs.Open strSql, CurrentProject.Connection, adOpenStatic
rs.MoveLast
CountDistinctRows = rs.RecordCount
rs.Close
Set rs = Nothing
End Function

In the Text box Control Source = countDistinctRows ("Table","Column") to
return the count of distinct rows where the rows are Not Null.

Ron W
That doesn't work in Access, Jeff.

dshemesh: I don't believe there's anyway to do what you want, unless you
want to have a separate query that selects the distinct rows, and does a
count on that subquery.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Count DISTINCT ...?

Jeff Boyce
<Access MVP>

Hello,
Is there a way to count distinct rows using the controlSource property
of
a
textbox?
writing: =count([orderId]) returns all the OrderId's, and I want to
count
only the distinct orderId's.
thanks