Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Feb 2002
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts

    List distinct field values (A2K SP3)

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: List distinct field values (A2K SP3)

    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.

  3. #3
    2 Star Lounger
    Join Date
    Feb 2002
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List distinct field values (A2K SP3)

    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?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: List distinct field values (A2K SP3)

    Here is a code template. Of course, you must substitute the appropriate field and table names.<pre>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</pre>

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

  5. #5
    2 Star Lounger
    Join Date
    Feb 2002
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List distinct field values (A2K SP3)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •