SQL, Charts, General newbieness 1

[briggsy79]

I have been asking question about graphing the number of records in a database with the same name (value in a field)for about a month or more, i have finally found a (very dodgy!)half solution, here's what i did:

adoRecordset3.Open "Select * from reportertb1 order by Name", db1, adOpenStatic, adLockOptimistic
recCount = adoRecordset3.RecordCount
For a = 1 To DistinctRecCount
If adoRecordset3.EOF Then
Exit Sub
End If
GraphData = 1
recCount = adoRecordset3.RecordCount
Do While strName = adoRecordset3!Name Or adoRecordset3.AbsolutePosition = 1
GraphData = GraphData + 1
If recCount = placeCount + 1 Then
Exit Sub
Else
adoRecordset3.MoveNext
placeCount = placeCount + 1
End If
chtSample.RowLabel = strName
Loop

chtSample.ColumnLabel = "Number of Entries"
strName = adoRecordset3!Name
i = i + 1

chtSample.DataGrid.SetData i, 1, GraphData, False

If adoRecordset3.EOF Then
chtSample.RowCount = i
Exit Sub
End If
Next a

It is very very messy code (i am still learning,hence the question) and most of it is just stuff i inveted to patch up re-occuring errors. But my problem now is how do i change the row labels so they reflect the name (value in the name field) for which the value is created, and also how do i restrict the number of columns to the number of different Names. Also do you think this code will work well...if at all? Please feel free to test it until your computer locks up with many and varied infinite loops.

Thank You all for your help, maybe one day I can answer a question, dont hold your breath though.

-David Briggs

 

[MarkSweetland]

The SQL query is where you need to start. The statement in your example returns all records in the table name and therefore requires you to parse each record to see if it contains the previous name in order to establish a count. You need to use SQL to do all the work for you:

Rather than:

Select * from reportertb1 order by Name

Use:

Select Name, Count(*) as CountofName from reportertb1 Group by Name Order by Name

This returns the Name field along with the count of how many times the name appears in the table.

I'll leave the graphing to you....

Mark

 

[briggsy79]

Thanks a lot it is working a treat.
I should be able to work out the graphing