Count Distinct Access 2000

[bhcob1]

Hi guys,

I have access 2000, I have a form frmCSOC, with a field [CSOC Number] that can have duplicates, I want a label to display how many records there are with DISTINCT [CSOC Number] values.

I have heard that the DISTINCT keyword does not work on Acc2000.

Thanks Guys

[NeoPa]

DISTINCT & DISTINCTROW are predicates of the SELECT clause in SQL that work fine in Access 2000 and all subsequent versions.

[NeoPa]

Hi guys,

I have access 2000, I have a form frmCSOC, with a field [CSOC Number] that can have duplicates, I want a label to display how many records there are with DISTINCT [CSOC Number] values.

I have heard that the DISTINCT keyword does not work on Acc2000.

Thanks Guys

I can't see how the DISTINCT predicate helps you here mind you.
The best way to get this information on your form is probably to use a DCount() call. You haven't provided information on the table (where the actual data is stored - A form is simply a way of interfacing to a table or set of tables via a query).
Assuming a table called [tblCSOC] with a matching field [CSOC Number] then you need some code in your OnCurrent event procedure :

Private Sub Form_OnCurrent()
  lblCount = DCount("[CSOC Number]", _
                    "[tblCSOC]", _
                    "[CSOC Number]=" & Me![CSOC Number])
End Sub

[ADezii]

Hi guys,

I have access 2000, I have a form frmCSOC, with a field [CSOC Number] that can have duplicates, I want a label to display how many records there are with DISTINCT [CSOC Number] values.

I have heard that the DISTINCT keyword does not work on Acc2000.

Thanks Guys

1__ SELECT DISTINCT tblTest.[CSOC Number] FROM tblTest will return all Unique CSOC Numbers

2__ DCount("*","qry CSOCNumber") where the underlying SQL for qryCSOCNumber is listed above,will return the Total Number of Records containing Unique CSOCNumber values. If I'm not mistaken, this is what you are looking for.

[bhcob1]

Sorry for the misunderstandin g, my initial post was not very clear.

What I want to do is display the total number of records with a distinct value.

Below is the table tblCSOC, from this table there are 9 records, and 5 distinct [CSOC Number], I would like the label to always display 5, so the user always knows how many CSOCs have been put into the database

CSOC Number    Issue
10303                  1
10303                  2
10303                  3
11321                  1
11321                     2
12101                  1
12300                  1
12301                  1
12301                     2

Thanks NeoPa

[NeoPa]

It looks like ADezii already answered your question for you (twice now but you can't see the deleted post ;))
You could also do it in SQL directly as :

SELECT Count([CSOC Number])
FROM (SELECT DISTINCT [CSOC Number]
      FROM [tblCSOC])