Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 163
  • Last Modified:

Count Values in table

Hi,

Help again!  I may be way off on this but here goes.  I have a Subscribers table in Access with a SubFodId column.  What I am trying to do is display the number of records for each SubFodId and what the SubFodId is.


for example:

DS = 11,034 Subcribers
SW =    456 Subscribers
S4D =   125 Subscribers

etc...

The below returns a value of 5 which I guess is the number of different SubFodId's presently in the column.

Dim dap3 As New OleDb.OleDbDataAdapter( _
                  "SELECT distinct count (Subscribers.SubFodID) FROM(Subscribers)GROUP BY Subscribers.SubFodID;", _
                  "Provider = Microsoft.Jet.OleDb.4.0;" & _
                  "Data Source=c:\Program Files\Nads\NadsDatabase.mdb")

Dim das3 As New System.Data.DataSet

 dap3.Fill(das3, "Subscribers")
        txtFodId.Text = ("FOD " & _
                das3.Tables(0).TableName & " is " & _
                das3.Tables(0).Rows.Count.ToString & ".")
        das2.Clear()

I hope I explained this okay.  If not let me know.  Thanks for your help.
0
maralans
Asked:
maralans
  • 4
  • 4
1 Solution
 
amyhxuCommented:
Suppose you have a "Subscribers_Name" column for each record:

Dim dap3 As New OleDb.OleDbDataAdapter( _
                  "SELECT count (Subscribers.Subscribers_Name) as Number_Of_Subscribers, SubFodID FROM (Subscribers) GROUP BY Subscribers.SubFodID;", _
                  "Provider = Microsoft.Jet.OleDb.4.0;" & _
                  "Data Source=c:\Program Files\Nads\NadsDatabase.mdb")
0
 
maralansAuthor Commented:
That returns the same value.  I don't think I explained this very well.  My subscribers table has 12,000 records of which 5,000 are DS, 3,000 are SW, 2,000 are S4D etc...

What I want to display is:

DS = 5,000 Subcribers
SW = 3,000 Subscribers
S4D = 2,000 Subscribers

etc....

In other words, I want to display the total number of subscribers for each SubFodId and display the SubFodId along with the number.


I hope thats better.
0
 
amyhxuCommented:
Continued from my last post:

Dim das3 As New System.Data.DataSet

Dim txtFodId As String = ""
For Each dRow as DataRow In das3.Tables("Subscribers")
    txtgFodId = txtFodId & dRow("SubFodID").toString & " = " & dRow("Number_Of_Subscribers").toString & " Subscribers" & vbCrlf
Next

Console.WriteLine(txtFodId)
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
amyhxuCommented:
How about now?

Dim dap3 As New OleDb.OleDbDataAdapter( _
                  "SELECT count (Subscribers.SubFodID) as Number_Of_Subscribers, SubFodID FROM (Subscribers) GROUP BY Subscribers.SubFodID;", _
                  "Provider = Microsoft.Jet.OleDb.4.0;" & _
                  "Data Source=c:\Program Files\Nads\NadsDatabase.mdb")

Dim das3 As New System.Data.DataSet

Dim txtFodId As String = ""
For Each dRow as DataRow In das3.Tables("Subscribers")
    txtgFodId = txtFodId & dRow("SubFodID").toString & " = " & dRow("Number_Of_Subscribers").toString & " Subscribers" & vbCrlf
Next

Console.WriteLine(txtFodId)
0
 
maralansAuthor Commented:
I get the following error on  the following line.


Expression is of type "system.data.datatable' which is not a collection type.

For Each dRow As DataRow In das3.Tables("Subscribers")

=====================================================================

This is what I have:

   Dim dap3 As New OleDb.OleDbDataAdapter( _
                  "SELECT count (Subscribers.SubFodID) as Number_Of_Subscribers, SubFodID FROM (Subscribers) GROUP BY Subscribers.SubFodID;", _
                  "Provider = Microsoft.Jet.OleDb.4.0;" & _
                  "Data Source=c:\Program Files\Nads\NadsDatabase.mdb")

        Dim das3 As New System.Data.DataSet

        Dim txtFodId As String = ""
        For Each dRow As DataRow In das3.Tables("Subscribers")
            txtgFodId = txtFodId & dRow("SubFodID").ToString & " = " & dRow("Number_Of_Subscribers").ToString & " Subscribers" & vbCrLf
        Next

        Console.WriteLine(txtFodId)
0
 
amyhxuCommented:
For Each dRow As DataRow In das3.Tables("Subscribers").Rows
0
 
maralansAuthor Commented:
Okay, we are real close.  Its displaying it correctly in the console but I don't get anything in the textbox.  Any ideas?
0
 
maralansAuthor Commented:
Aw, I got it.  Thank you very much for your help.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now