Please help with my chart

[briggsy79]

Please help me.
I have written this code in an attempt to count how many times a distinct name appears in a recordset set. I am a novice but Im sure i have declared all variables.

Set db = New Connection
db.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Microsoft Visual Studio\vb98\Disciplin Manager 2\reporter.mdb"

Set adoRecordset2 = New Recordset

adoRecordset2.Open "select distinct Name from reportertb1", db, adOpenStatic, adLockOptimistic
DistinctRecCount = adoRecordset2.RecordCount
Call NameFind

Private Sub NameFind()
blnisopen = False
Dim i As Integer
adoRecordset2.Close
adoRecordset2.Open "select * From reportertb1", db, adOpenStatic, adLockOptimistic

adoRecordset2.MoveFirst
Set adoRecordset3 = New Recordset
For i = 1 To DistinctRecCount

Do While strName = adoRecordset2!Name
adoRecordset2.MoveNext
Loop
strName = "'" & adoRecordset2!Name & "'"

If blnisopen = True Then
adoRecordset3.Requery
Else
adoRecordset3.Open "select * From reportertb1 where Name = " & strName, db, adOpenStatic, adLockOptimistic
blnisopen = True
End If
RecNo = adoRecordset3.RecordCount
Set DataGrid1.DataSource = adoRecordset3
chtSample.DataGrid.SetData 1, i, RecNo, True
chtSample.Title = frmChoiceChart.strChart

Next i

I get an error at the setting the points on the chart at the moment but im not sure if the rest of the code will work at all. Any guidance would be appreciated. Thank you

 

[StaceyG]

Using the Northwind sample database, the products table. I was able to get a count of how many times a supplier was used for different products. Now, I just added the info to a flexgrid for a quick peek, so you'll have to modify for a dbgrid and chart, but it did a nice job of saying, supplier #1 was found 3 times and so on...

Private Sub Command1_Click()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Open "provider=microsoft.jet.oledb.4.0;data source=c:\program files\microsoft visual studio\vb98\northwind.mdb"
rs.Open "SELECT Count(Products.SupplierID) AS CountOfSupplierID, Products.SupplierID " & _
"FROM Suppliers INNER JOIN Products ON Suppliers.SupplierID = Products.SupplierID " & _
"GROUP BY Products.SupplierID;", cn, adOpenStatic, adLockReadOnly
Do While Not rs.EOF
fg.AddItem rs(0) & vbTab & rs(1)
rs.MoveNext
Loop
rs.Close
Set rs = Nothing

End Sub

good luck
Stacey

 

[briggsy79]

Thank you very much. im going to make myself sound stupid now and ask you to explain what each operation means. "Inner Join" etc. And the northwind database is a bit different to mine.
but thank you.

 

[StaceyG]

inner join just tells the query to check for matching values in the join fields between the two tables. To be honest with you when I need to create a unique query like that, I go into Access and create the query then I view the SQL syntax and copy it for my VB program.