How Can I Count The Number Of Rows By Category In A Table

Question

0.00/5 (No votes)

See more:

I'm using MS Access 2007.

On table1
1 ID
2 Project
3 Block
4 Lot

On table2
1 ID
2 Project
3 Count

It will become like this

table 1
ID PROJECT BLOCK LOT
1 FOREST 1 1
2 HOUSE 1 5
3 FOREST 5 8
4 FOREST 4 6
5 HOUSE 4 8

table 2
ID PROJECT COUNT
1 FOREST 3
2 HOUSE 2

i want to know how many per project on table1 then it will show on table2 row3

this is my code:

VB

Try
            Dim cmd As OleDbCommand = New OleDbCommand("SELECT (Select COUNT(PROJECT) FROM table1 WHERE PROJECT=FOREST as Project12Count)", con)
            opendatabase()
            Dim myDA As OleDbDataAdapter = New OleDbDataAdapter(cmd)
            Dim myDataSet As DataSet = New DataSet()
            myDA.Fill(myDataSet, "MyTable95")
            DataGridView1.DataSource = myDataSet.Tables("MyTable95").DefaultView
            closedatabase()
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        Finally
            closedatabase()
        End Try

Ive tried searching many codes that can help me but i cant find anything that can solve my problem.

Thanks to anyone who can help me.

Posted 28-Apr-14 22:31pm

Member 9663328

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2014-04-28T22:42:00

Solution 1

Try this:

SQL

SELECT Project, COUNT(Project) FROM Table1
GROUP BY Project

That should give you the numbers you want - but I probably wouldn't store it in a separate table as it's far, far too easy for that to get out of synch which the "real" numbers.

Posted 28-Apr-14 22:42pm

OriginalGriff

Comments

Member 9663328 29-Apr-14 4:52am

Thank you I already tried it. It worked but it doesnt show how many

OriginalGriff 29-Apr-14 5:03am

Um...yes it does, that is what the "COUNT(Project)" part is there for...

Member 9663328 29-Apr-14 21:14pm

is there something wrong in my code?

Try
Dim cmd As OleDbCommand = New OleDbCommand("SELECT Project, COUNT(Project) FROM Table1 GROUP BY Project", con)
opendatabase()
Dim myDA As OleDbDataAdapter = New OleDbDataAdapter(cmd)
Dim myDataSet As DataSet = New DataSet()
myDA.Fill(myDataSet, "MyTable95")
DataGridView1.DataSource = myDataSet.Tables("MyTable95").DefaultView
closedatabase()
Catch ex As Exception
MessageBox.Show(ex.ToString)
Finally
closedatabase()
End Try

OriginalGriff 30-Apr-14 5:03am

What, apart from you closing the DB twice? (Just close it in the Finally block, you don;t need to do it elsewhere as well)

Can't tell - does it do what you expect? I just tried in in one of my apps using teh Northwind sample DB and with did what I expected:
OleDbCommand cmd = new OleDbCommand("SELECT [Product ID], COUNT([Product ID]) FROM [Order Details] GROUP BY [Product ID]", con)