Click here to Skip to main content
14,698,806 members
Please Sign up or sign in to vote.
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:

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

1 solution

Try this:
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.
   
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)

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900