List distinct field values (A2K SP3)

Topic

New Reply

I have a database which contains personal information of about 1000 students and their classes. I want to transfer the student information on a class by class basis to dBase III+ files (to be used by an ancient DOS problem), each dBase III+ file contains only student info for each class. If I have 30 classes, then I should have 30 dBase III+ files. Transferring the info to dBase III+ files is no problem. What I am not sure is how can I loop through all the 30 classes so that I can produce 30 dBase III+ files? The class field is not indexed. My approach is to store all the distinct names of 30 classes into an array and then ask the Access program to loop through the array element by element, but so far without success. The failure is due to my inability to get the 30 distinct class names. Can anyone please help.

Reply | Quote

Replies

Open a recordset (DAO or ADO) based on the SQL statement “SELECT DISTINCT Class_Name FROM tblClass_Info”, of course with the appropriate names substituted. Loop through the records of this recordset.

Reply | Quote

Thanks for your response, Hans.

What I want is to make the process automatic. Say I have classes named 2A, 2B, 2C, and 2D, which I may not know beforehand. I want by just supplying a class name “2”, and the program will get the distinct class names 2A, 2B, 2C and 2D, and then for each distinct class, save the info into files 2A.dbf, 2B.dbf, 2C.dbf and 2D.dbf.

I am really dumb. I don’t see how the SQL statement “SELECT DISTINCT Class_Name FROM tblClass_Info” will help. Apparently I need to know the Class_Name, which I may not know, as I only know their names start with “2”. My post must have been unclear. Can you elaborate it a little, please?

Reply | Quote

Here is a code template. Of course, you must substitute the appropriate field and table names.

Dim strChar As String
Dim strClass As String
Dim strFile As String
Dim dbs As DAO.Database
Dim rstClasses As DAO.Recordset

' Ask user for first character
strChar = InputBox("Enter first character, e.g. 2")
If strChar = "" Then Exit Sub

' Get class names
Set dbs = CurrentDb
Set rstClasses = dbs.OpenRecordset( _
	"SELECT DISTINCT Class_Name FROM tblClass_Info WHERE Class_Name Like '" & _
	strChar & "*'")

' Loop through recordset
Do While Not rstClasses.EOF
	strClass = rstClasses!Class_Name
	strFile = strClass & ".dbf"
	' Now, you have the name of the class (e.g. 2A) in strClass
	' and the file name (2A.dbf) in strFile
	' Insert code to export data to strFile here
	...
	' Move to next class
	rstClasses.MoveNext
Loop

' Clean up
rstClasses.Close
Set rstClasses = Nothing
Set dbs = Nothing

This code will not work in this form, you will have to adapt it to your situation. Post back if you need more help.

Reply | Quote

Thanks, Hans. This was the fourth time you helped me out. Your code template worked perfectly for my purpose. Thanks again.

Reply | Quote