SQL Count Distinct - integrating the query

Question

Ok, so in my vb project I have a datagridview being populated by some data from an Access db. the code for which is as follows which includes the SQL query to generate the info:

Dim sql_3 As String = "SELECT tbl_cx_" & yr & ".Loc," & _
                                " tbl_cx" & yr & ".Product," & _
                                " tbl_cx_" & yr & ".price," & _
                                " tbl_cx_" & yr & ".comment," & _
                                " tbl_cx_" & yr & ".other" & _
                                " FROM tbl_cx_" & yr & _
                                " WHERE tbl_cx_" & yr & ".mth like '%" & mth & "%' AND" & src_sql

        Dim con As New OleDb.OleDbConnection("PROVIDER=Microsoft.Ace.OLEDB.12.0;Data Source = F:\me\Databases\dbLPM.accdb")
        Dim oleCmd As OleDb.OleDbCommand = New OleDb.OleDbCommand(sql_3, con)
        con.Open()

        Dim da_3 As New OleDb.OleDbDataAdapter(oleCmd)
        Dim ds_3 As New DataSet
        da_3.Fill(ds_3)
        Me.DataGridView1.DataSource = ds_3.Tables(0)
        con.Close()

So this is working fine.

I am interested in updating a label with the count of the distinct entries in a column from the database called "products", based on the query that is run above. i.e. one query may generate 10 results, of which 5 products may be distinct. another may generate 100 results, of which 80 are distinct.

I have the following SQL which I think does the trick, but I dont know how to integrate it to the above SQL query in order for it to run at the same time:

(select Count(*) AS Numberofproducts
from
(SELECT DISTINCT product FROM tbl_cx_2012) as T)

Can anyone help?

Cheers

Answer 1

It looks like two separate queries to me - just using the same criteria in the WHERE statement. One returns zero or more rows, while the other a count of unique items for a column in the query. Since they are logically different queries can't you just run one after the other?

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 2

cool, thanks Paul. Sometimes its the obvious that I just dont see. :-)