ADO question

[briggsy79]

Hi everybody
Im trying to count the numbers of different names in a table under the name field. At the moment i open the reordset like this:
Dim db As Connection
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 adoRecordset = New Recordset
adoRecordset.Open "select Name from reportertb1", db, adOpenStatic, adLockOptimistic

Then i loop while the variable strName isn't the same as the current value in the name field. if it isn't i want to send some SQL to the database to narrow the recordset to just those with the name like the variable. then perform a record count to see how many records there are and graph that. At the moment im still working on querying the recordset (i try to open it again with SQL, but of course i have already oppened it,any tips here would be very helpful) but once i work that out i think it should work. Is this a good way to do it? Or is there an easier or more correct way to do this kind of opperation?

Thank for reading so much and i will be very greatfull for any help any one can provide

 

[JJR]

Hi,

Use the distinct keyword in your SQL statement, thus sql will filter out any doubles. The recordcount of your recordset should be the number of different names in your table.

"select distinct Name from reportertb1"

Good luck!

Jordi Reineman

 

[Mavors]

Hello,

Using DISTINCTROW(or DISTINCT) should work great. If you are still having problems try using this SQL statement so that all the similar names will be grouped together. This will make it so that you can use your idea of counting only when the field values change.

"SELECT [Name Field] FROM [Table] ORDER BY [Name Field]"

Mavors
Programmer
Cincinnati, Ohio

 

[briggsy79]

Thanks that should work a treat.