Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2009
    Posts
    2

    Unanswered: Possible?: Pair Off Matching Rows But Don't Pair It Off Again With Other Matches

    Hi All,

    I'm using VB6 and Acccess.

    I want to pair off two records/rows. Once these records/rows are paired off I don't want a third, fourth, fifth, etc... record to pair off with records already paired off (see example below).

    I've been struggling with this for quite some time and I was wondering if anyone could help me:

    If I had a table [First Name], [Last Name], [City], [Pair Off]

    Scenario (Raw Data):
    Row1: Bob, Marley, New York
    Row2: Bob, Marley, New York
    Row3: Bob, Marley, California

    Which I want to turn it into (Final Data):
    Row1: Bob, Marley, California, Pairset 1
    Row2: Bob, Marley, New York, Pairset 1
    Row3: Bob, Marley, New York,

    How would I do this?

    Programmically in VB6 I could do this but I'd rather do it through queries.

    The reason why I don't want to do it programmically is a few reasons:
    1. It'll be faster if I didn't
    2. It'll be cleaner
    3. I keep getting a locked error (BUG: "Could Not Update&#59; Currently Locked" Error Message Raised by Second Edit Attempt in a Transaction) and I can't figure out how to install MSDASQL.
    4. Keeping the table open for a long time is not ideal (as I've read somewhere...)

    What I did programmically was

    open the table for all records "New York" (V1)

    open the same table for records "California" (V2)

    Update [Pair Off] where [First Name] and [Last Name] are the same in both views (V1 and V2). If Recordcount is odd, don't update the last record.

    This process takes a really long time to process because of the connection from VB6 to Access and as mentioned before, the record is locked because of the multiple views.

    One thing which I have tried is to create two queries "New York" and "California". I then created a new table

    SELECT NY.FIRST, NY.LAST, NY.CITY, NY.PairOff
    INTO BOB
    FROM NY INNER JOIN CALI ON (NY.FIRST & NY.LAST) = (CALI.FIRST & CALI.LAST)
    WHERE (((NY.CITY)<>CALI.CITY))
    GROUP BY NY.FIRST, NY.LAST, NY.CITY, NY.PairOff;

    I don't think this is exactly what I want in the pair off and it won't (of course) give me the rest of the data i.e. ROW 3 above.

    Any help would be great, many thanks.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    One solution I can think of (they certainly are others) is:

    1) Create a public function like this:
    Code:
    Public Function PairOff(Anything As Variant) As String
    
        Static lngCount As Long
        
        If Anything = "{Initialize}" Then lngCount = -1
        lngCount = lngCount + 1
        PairOff = "Pair Off " & CStr(lngCount)
       
    End Function
    2) Create the query like this:
    Code:
    INSERT INTO TableDestination ( [First Name], [Last Name], City, [Pair Off] )
    SELECT [First Name], [Last Name], City, PO
    FROM (SELECT [First Name], [Last Name], City, PairOff([City]) AS PO
    FROM (SELECT DISTINCT [First Name], [Last Name], City
    FROM TableSource))
    If you have to run the query several times, you can first re-initialize the counter in the function like this:

    Code:
    PairOff("{Initialize}")
    CurrentDb.Execute <Query>
    Have a nice day!

  3. #3
    Join Date
    Mar 2009
    Posts
    2
    Thanks for your reply. Unfortunately, I'm not getting the result I'm looking for - at least based on the way I re-did on my computer.

    The results I got was

    California Pairoff 1
    New York Pairoff 2

    I was hoping to get something like this


    California
    New York Pairoff 1
    New York Pairoff 1

    and if I had more "New Yorks" it would be
    New York Pairoff 2
    New York Pairoff 2.

    To be quite honest, I'm still trying to understand your approach as it is quite fancy and I'm not that advanced enough to follow - but I am definitely interested in what's going on and will be looking into what's happening.

    Thanks again!

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    It would be easier if you could supply the general rule that determines how the "pairoff x" are attributed. It's not evident to extrapolate from one or two examples.

    Have a nice day!

Posting Permissions

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