Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2004

    Unanswered: create a text field with results of a query

    I have a situation where I would like to take the result from a query:
    Level1 Level2
    2 1
    2 2
    2 3
    3 1
    3 3
    3 6
    3 10
    I would like to create a temp table that would look like this:
    Level1 Level2
    2 1, 2, 3
    2 1, 3, 6, 10

    Is there is any code that I can use to accomplish this?
    Last edited by dbjim; 11-19-04 at 10:44. Reason: new to system

  2. #2
    Join Date
    Oct 2003
    A little bit more information?

  3. #3
    Join Date
    Dec 2003

    How proficient are you in VBA?

    Some of your options
    You can create a temp table for each instance of this
    Use the same temp table and purge records after use
    Create a collection and reference that information from code

    which one you choose depends on the application of the information in the form.

    Ill go over the latter two options for you they may be a little easier to work with

    first of you need either dao or ado. I am going to use DAO for this instance as it it a little easier for a n00b to understand.
    if you need help finding the dll lemme know.

    dim db as database: dim rs as recordset, rs2 as recordset
    dim Col as new collection
    dim str as string

    set db= currentdb

    'I am going to assume that your list of first values is a derivative from another table. and has values in that table

    set rs = db.openrecordset(select firstvalue from tblTable)
    do while not rs.eof
    set rs2 = db.openrecordset(select secondvalue from tblTable2 WHERE foreignkey = " & rs.fields(0).value)
    'again we assume it is going to return values
    str = rs2.fields(0).value
    do while not rs2.eof
    str = str & ", " & rs2.fields(0).value
    end while

    coll.add str, rs.fields(0).value
    set rs2 = nothing
    end while
    set rs= nothing
    set db = nothing

    You now have a collection filled with the information. The Key for each entry is the PK from the first table and the data is the information stored.

    You can use for loops to cycle through the information etc. and display it how you want.
    dim i as integer
    for i = 1 to coll.count
    debug.print coll.item(i).key & vbtab & coll.item(i)
    next i

    How you would use this in a temp table.

    simply replace the coll.add ...... with
    db.execute("INSERT INTO tblTempTable VALUES (" & rs.fields(0).value & "," & "'" & str & "'' & ")" )

    ta dahh

    if none of these options meet your needs then we can look at the first.

    Hopefully this is enough to get you headed in the right direction.



  4. #4
    Join Date
    Nov 2004

    Thumbs up Thanks for your response

    Thanks for your response.

    I used what you gave me to build the following code and it does the job.

    Dim rs As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Dim rs3 As DAO.Recordset
    Dim z As String
    Dim x As String
    Set rs = CurrentDb.OpenRecordset("Table1")

    x = rs.Fields(0).Value
    While Not rs.EOF

    If x <> rs.Fields(0).Value Then
    If z <> "" Then z = Right(z, Len(z) - 1)

    Set rs3 = CurrentDb.OpenRecordset("TempSecRep")

    rs3!LEVEL2 = x
    rs3!LEVEL3 = z

    z = ""
    End If
    x = rs.Fields(0).Value
    z = z & ", " & rs.Fields(1).Value
    If z <> "" Then z = Right(z, Len(z) - 1)

    Set rs3 = CurrentDb.OpenRecordset("TempSecRep")
    rs3!LEVEL2 = x
    rs3!LEVEL3 = z


    If there is a better way, please let me know.

    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