• List distinct field values (A2K SP3)

    • This topic has 4 replies, 2 voices, and was last updated 22 years ago.
    Author
    Topic
    #384988

    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.

    Viewing 0 reply threads
    Author
    Replies
    • #662382

      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.

      • #662563

        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?

        • #662565

          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.

          • #662758

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

    Viewing 0 reply threads
    Reply To: List distinct field values (A2K SP3)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: