Total count of unique values in a query

Soundje

Registered User.
Local time
Today, 11:54
Joined
Aug 14, 2014
Messages
27
Hello everybody,

Can anybody help with this...

I have the following code:

Code:
     Dim rs As DAO.Recordset
    Dim db As Database
    Dim strSQL As String
 
    Set db = CurrentDb
    strSQL = "SELECT DISTINCT tbl_contract_lev.ContractID FROM (tbl_leverancier_gegevens INNER JOIN tbl_contract_lev ON tbl_leverancier_gegevens.Id_lev = tbl_contract_lev.LeverancierID) INNER JOIN tbl_data_leverancier ON tbl_contract_lev.ContractID = tbl_data_leverancier.ContractID"
      
    Set rs = db.OpenRecordset(strSQL)
 
    RecordCount = rs.Fields(0)
 
    rs.Close
    Set rs = Nothing
    Set db = Nothing
 strTitle = "FINISH !!!"
MsgBox RecordCount, vbOKOnly + vbInformation, strTitle
The result of the queury is a list fo unique values but by including the "count" function in SQL seems not te be working in combination with "DISTINCT"

Many thanks for all your help,

Kindly Regards,
Koen
 
I don't see where you've used the Count() function.
 
Thanks a lot Plog for this,
I am almost there but do not need to know the how many time each value is in the table.
I need to know how many entries there are in the table of the unique field so one number

this is the query that I have tried:
SELECT tbl_data_leverancier.ContractID, COUNT (tbl_data_leverancier.ContractID) AS total FROM tbl_data_leverancier GROUP BY tbl_data_leverancier.ContractID


Kindly Regards,
Koen
 
Then I'd take the query you initially had and make it a subquery in an aggregate query:

SELECT COUNT(ContractID) FROM (put your query here)
 
Thanks a lot that worked fines for me..

Again many thanks,
Koen
 

Users who are viewing this thread

Back
Top Bottom