CODEWITHSUNDEEP
c#sqlms-access-2010
Aravind Sirsikar
Aravind Sirsikar

Reputation: 19

How to find the count of distinct records in a MS Access database using C#?

I have an MS Access database as below

Filing_Year     |  Name
------------------------
02/01/2008      | AAA             
02/01/2008      | AAA             
02/03/2008      | AAA             
03/01/2008      | BBB

I need a query in C# which will get me an output as below and display it in a gridview

Filing_Year     | file_count
----------------------------
02/01/2008      | 2  
02/03/2008      | 1  
03/01/2008      | 1

This is the code I tried:

dataGridView1.Columns.Add("Column", "FileCount");    // file count is not an element in database

OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;DataSource=|DataDirectory|\RMS_Database.mdb");

con.Open();

OleDbDataAdapter sda = new OleDbDataAdapter("select Filing_Year,count(*) from Checklist_Data group by Filing_Year ", con);

DataTable dt = new DataTable();
sda.Fill(dt);

BindingSource bSource = new BindingSource();
bSource.DataSource = dt;

dataGridView1.DataSource = bSource;
sda.Update(dt);

With this I am seeing only unique filing_year, but not the count.

Thanks in advance. Help is really appreciated.

Upvotes: 1

Views: 216

Answers (1)

Daniel
Daniel

Reputation: 9829

You need to modify your SQL Query like:

SELECT Filing_Year, COUNT(Name) as file_count
FROM Checklist_Data 
GROUP BY Filing_Year, Name

Upvotes: 3

Related Questions