Results 1 to 2 of 2

Thread: Update puzzle

  1. #1
    Join Date
    Jan 2003
    Location
    Guelph, Canada
    Posts
    39

    Unanswered: Update puzzle

    I have a query that gives me results for StudentID and Other.
    For example:

    StudentID Other
    2 3
    12 1
    23 2

    I need to take these results and do the following.
    Go to Detentions table and update the first Null values in the Status
    field to "Oth" for the StudentIDs mentioned.

    So in Detentions table:
    StudentID Status
    2 Oth
    2 Oth
    2 Oth
    2
    12 Oth
    12
    23 Oth
    23 Oth
    23

    I could do this in an extremely complicated way but am looking for an
    elegant solution. Would anybody have any suggetions?

    TIA Eric

  2. #2
    Join Date
    Apr 2002
    Location
    Illinois
    Posts
    133

    RE: Update puzzle

    Eric,

    This may not be elegant but it's not too complex. This is in quick, rough pseuodcode.

    OpenRecordset (query)
    For i = 1 to query.recordcount

    ID = query!StudentID

    OpenRecordset (Detention, where StudentID = ID and IsNull(Status))

    For n = 1 to query!Other

    Detention.Edit
    Detention!Status = "Oth"
    Detention.Update

    Next n
    Close Detention

    Next i
    Close query

    Determine if you need to order the Detention recordset in any way. Let me know if you need any more help.
    SteveH

Posting Permissions

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