How to count distinct records in Access table and display that value in Excel

Question

I have a table in Access that I’m querying from Excel. I need help with the sql statement. First, I need to filter the table based on SampleType matched criteria indicated in the Where clause. There are only 3 options: “mel”, “col” or “ lun”. The ultimate goal is to pass the number of distinct records from the SampleID column for selected SampleType(s) to a variable and output it on a UserForm. SampleType column has duplicate values and need to count only distinct values. From what I’ve found online 'COUNT(DISTINCT(' + column_name + ')) would work, but I’m not sure how to use it.

Thanks for your help in advance

Dim var6 As String
Dim var7 As String
Dim var8 As String
var6 = "mel"
var7 =  “lun”
var8 =  “col”
SQLwhere = "WHERE "

If CheckBox5 = True Then
SQLwhere = SQLwhere & "[Table1].[SampleType] LIKE '" & var6 & "%" & "' AND "
‘count a total district records in column SampleID. 
End If

If CheckBox6 = True Then
SQLwhere = SQLwhere & "[Table1].[SampleType] LIKE '" & var7 & "%" & "' AND "
End If

If CheckBox7 = True Then
SQLwhere = SQLwhere & "[Table1].[SampleType] LIKE '" & var & "%" & "' AND "
End If

StrSql = "SELECT * FROM [Table1] "

'Remove the last AND applicable
If SQLwhere = "WHERE " Then
    SQLwhere = ""
Else
    SQLwhere = Left(SQLwhere, Len(SQLwhere) - 5)
End If

StrSql = StrSql & SQLwhere

You are actually asking multiple questions in a sort of unclear manner. Please narrow down your post and highlight the key issue you are trying to resolve. Best regards, — Alexander Bell
– Alexander Bell, Commented Jun 28, 2015 at 2:35

Alexander Bell · Accepted Answer · 2015-06-28 21:47:04Z

0

Your question is rather broad, so the answer is sort of generic. Pertinent to the Syntax of MS Access DISTINCT SQL, it could be like the following:

SELECT COUNT(*) AS UniqueRecordsCount FROM (SELECT DISTINCT [ColumnName] FROM [SampleTable]) AS T1

or simplified like:

SELECT COUNT(*) AS UniqueRecordsCount FROM (SELECT DISTINCT [ColumnName] FROM [SampleTable])

Corresponding to your additional question in comments, please see the following:

GetDistinctValue = rs("UniqueRecordsCount")

Hope this may help.

edited Jun 28, 2015 at 21:47

answered Jun 28, 2015 at 2:43

Alexander Bell

7,9163 gold badges29 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3781528 Over a year ago

Thanks Alex, From what I uderstand I need to replace "SELECT * FROM [Table1] " with SELECT COUNT(*) FROM (SELECT DISTINCT [ColumnName] FROM [SampleTable]). How do I pass that value to a variable after I open a record set? Set rs = New ADODB.Recordset rs.Open sql, cnn GetDistinctValue = rs.? Thank you so much for your help

Alexander Bell Over a year ago

You are welcome. I have extended the answer pertinent to your additional question. If satisfied, please mark it accepted. Should you have more questions, please post them separately. Good luck with your project. Best regards,

Collectives™ on Stack Overflow

How to count distinct records in Access table and display that value in Excel

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related