By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,864 Members | 1,281 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,864 IT Pros & Developers. It's quick & easy.

Count Distinct Access 2000

bhcob1
P: 19
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
Feb 5 '07 #1
Share this Question
Share on Google+
5 Replies


NeoPa
Expert Mod 15k+
P: 31,758
DISTINCT & DISTINCTROW are predicates of the SELECT clause in SQL that work fine in Access 2000 and all subsequent versions.
Feb 5 '07 #2

NeoPa
Expert Mod 15k+
P: 31,758
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 :
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_OnCurrent()
  2.   lblCount = DCount("[CSOC Number]", _
  3.                     "[tblCSOC]", _
  4.                     "[CSOC Number]=" & Me![CSOC Number])
  5. End Sub
Feb 5 '07 #3

ADezii
Expert 5K+
P: 8,692
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("*","qryCSOCNumber") 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.
Feb 5 '07 #4

bhcob1
P: 19
Sorry for the misunderstanding, 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

Expand|Select|Wrap|Line Numbers
  1. CSOC Number    Issue
  2. 10303                  1
  3. 10303                  2
  4. 10303                  3
  5. 11321                  1
  6. 11321                     2
  7. 12101                  1
  8. 12300                  1
  9. 12301                  1
  10. 12301                     2
Thanks NeoPa
Feb 5 '07 #5

NeoPa
Expert Mod 15k+
P: 31,758
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 :
Expand|Select|Wrap|Line Numbers
  1. SELECT Count([CSOC Number])
  2. FROM (SELECT DISTINCT [CSOC Number]
  3.       FROM [tblCSOC])
Feb 5 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.