Page 2 of 3 FirstFirst 123 LastLast
Results 16 to 30 of 31

Taking data from 1 table and writing it to a second table provided certain conditions are met

  1. #16
    Bob M is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Mar 2013
    Posts
    29
    Hi Again

    yes - table 1 records need to be compared with each and every Table 2 record until a 'similar' situation is reached


    then we ignore that Table 1 record and get the next Table 1 record

    Only if the Table1 record is different to every Table 2 record do we write it across

    Bob M
    Attached Files Attached Files

  2. #17
    ridders52's Avatar
    ridders52 is online now Long time standing
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    306
    That is a remarkably inefficient method of working.
    Look ahead into the future where table 2 has 10000 or 15000000 records.
    It will take ages to check every record and use up lots of memory thus slowing your database to the point where it is unusable.
    Suggest a redesign now rather than later
    Colin (Mendip Data Systems)
    Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10, Website, email

  3. #18
    Bob M is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Mar 2013
    Posts
    29
    Quote Originally Posted by ridders52 View Post
    That is a remarkably inefficient method of working.
    Look ahead into the future where table 2 has 10000 or 15000000 records.
    It will take ages to check every record and use up lots of memory thus slowing your database to the point where it is unusable.
    Suggest a redesign now rather than later
    Not sure where to start with a suggested redesign......................

    Pseudo code for what is required (I think)

    Boolean variable Similar = FALSE
    Do until t1.EOF
    T1 count +
    Do until Similar = TRUE or t2.EOF
    T2 count +
    Do the 2 calcs
    If Similar = TRUE
    Write ID of record in Table 2 to Group_ID field of record in Table 1 [this is new]
    EndIf
    Loop
    If t2.EOF
    Write record in Table 1 to Table 2
    Similar = FALSE
    EndIf
    Loop

    the above incorporated with the latest code offering from Steve [BobM.Mod2]

    Bob M

  4. #19
    ssanfu is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    7,153
    Maybe I have it now. Did a LOT of re-reading of the posts.

    Ack!! Can't believe I missed so much.

    How is this one?
    Attached Files Attached Files
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  5. #20
    Bob M is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Mar 2013
    Posts
    29
    Hello Steve

    well, that is quite a different result
    I ran it on my large Table 1 and it produced 171 records in Table 2
    Time taken - a few seconds !
    I need some time now to check

    As regards, comments about inefficiency, this is a one-off exercise so that I have an initial database to begin with
    From here on in, Table 1 gets a new record every hour (mostly) and so the exercise is much smaller

    Many, many thanks for all you time and effort

    regards from a sunny Dunedin, NZ
    Bob M

  6. #21
    Bob M is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Mar 2013
    Posts
    29
    To make the checking a lot easier, when similar, could we write the ID of the record in Table 2 back to an additional field in Table 1, say GlobalID

    If Similar Then
    'if similar copy ID of record in Table 2 to field GlobalID in record in Table 1
    ---- CODE required ---
    'if similar then move to last T2 record
    t2.MoveLast
    End If

    Bob M

  7. #22
    ssanfu is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    7,153
    Don't ask for much.... do you?? Especially since I am up here in the snow and cold!


    Is this just a testing thing and it will be removed?


    I named the field in Table1 "Table2ID_FK".
    When the code is executed tomorrow, what should happen to the T1!Table2ID_FK field?
    Keep the existing numbers (ID of the Table2 record)?
    Delete all of the numbers? (ID of the Table2 record in Table1)
    Attached Files Attached Files
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  8. #23
    Bob M is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Mar 2013
    Posts
    29
    Hi again

    Not sure that the latest tweek is correct
    Given that I end up with 171 records in Table 2 (threshold figures 19, 7, and 3)
    I would expect the majority of Table 1 records to have the new ID field filled in, but this is not the case

    Does that make sense?
    Bob M

    p.s. I think you are writing 171 new IDs (matching the 171 Table 2 records)
    Actually I want the (31653-171) records in Table 1 to have a new ID and only 171 Table 1 records with no new ID (because they exist now in Table 2)

    To answer your questions:-
    In future table 1 and Table 2 stand as is now (after running your code)
    As each new Table 1 record comes along it will be compared with records in Table 2 until a 'similar' record is found at which point the ID for Table 2 record will be written into Table 1 record
    or.......no records in Table 2 match and so the Table 1 record will be added to Table 2 and have a blank ID field (ref. Table 2)

  9. #24
    Bob M is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Mar 2013
    Posts
    29
    I have just run your code against the first 50 Table 1 records
    After shifting the latest portion of code up to where we have a 'similar' situation

    Table 1 has 50 records
    Table 2 ends up with 22 records, which I think is OK
    BUT
    The ID numbering in Table 2 goes 1, 5, 6, 7, 8, 9etc
    It should go 1,2,3,4,5,6 etc. i.e. autonumber

    Now the first 3 Table 1 records are written to Table 2
    The next three are not and should have the code '3' written into the second ID field in Table 1 but they get a '6' due to the misnumbering above

    Bob M

  10. #25
    Bob M is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Mar 2013
    Posts
    29
    Hi again

    I have just run the code slightly adjusted and got what I am seeking I think

    I shifted the code to record the Table 2 ID in Table 1 up to the - if similar code
    I removed writing the Table 2 ID code as it is autonumber

    Using thresholds of 19, 7, and 3 I get 171 records in Table 2

    The only additional thing I had to do was to find the first record in Table 1 (date wise) and add '1' to the Table 2 ID field

    Table 1 records are now NOT ordered by date/time as before

    Hope you agree

    Bob M
    Attached Files Attached Files

  11. #26
    ssanfu is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    7,153
    Seems like I blew it again! I was writing the newly added record ID from Table2 back to the Table1 record that was NOT similar to any of the preexisting records.


    Table 1 records are now NOT ordered by date/time as before
    In the code, the Table1 records ARE ordered by the "LocalTime" field.
    Code:
        Set t1 = d.OpenRecordset("SELECT * FROM Table1 ORDER BY LocalTime")


    The only additional thing I had to do was to find the first record in Table 1 (date wise) and add '1' to the Table 2 ID field
    If I understand what you are saying, this cannot be done because the Table2 ID field (Table2ID_PK) is an Autonumber type field!


    ---------------------------------------------------------

    Let's forget about the PIC fields and talk about records.

    Here is a situation:
    You have set the X, Y & Z limits.
    Table1 has 300 records.

    At some point in time,
    Table2 has 15 records.
    And Table1 has looped through 199 records.

    At Table1 record #200, Table1 record 200 is being compared to every Table2 record, until a Table2 record is similar to the Table1 record.
    When Table2 is at record 5, it is determined to be similar to Table1 - record 200 (the current Table1 record).
    At that point, do you want to:
    write the FIRST similar Table2 record ID_PK to Table1 - record 200?
    Or the LAST Table2 record ID_PK to Table1 - record 200?



    ----------------------------------------------
    BTW, you DO know that a table is a "bit bucket"...right? A table HAS NO INHERENT order.
    That is why the table1 record set is opened with an ORDER BY clause.
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  12. #27
    Bob M is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Mar 2013
    Posts
    29
    Hi

    XYZ thresholds are 19, 7, and 3

    I want to write the FIRST similar Table2 record ID_PK to Table 1

    Bob M

  13. #28
    ssanfu is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    7,153
    I set the Default property of the unbound text boxes to 19, 7, and 3.


    I set "Table1.Table2ID_FK" to NULL when the "PRUNE" button is clicked. If you DON'T want the field to be set to NULL, Delete or comment out the BLUE line
    Code:
    <snip>
        'get variables from Form
        X = Me.tbX
        Y = Me.tbY
        Z = Me.tbZ
        
    
    '@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
        'Every time you click the "PRUNE" button, Table1 Field "Table2ID_FK" is updated to NULL.
        ' if this is **NOT**  correct, comment out the following line
        d.Execute "UPDATE Table1 SET Table1.Table2ID_FK = Null;", dbFailOnError
    '@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
    <snip>

    I moved the code to write Table2 PK field to Table1 FK field up a few lines:
    Code:
    <snip>
                    'Similar means Total Differences <= threshold x OR Big Differences <= Threshold y
                    bSimilar = (TotalDiffs <= X) Or (BigDiffs <= Y)
    
                    If bSimilar Then
                        'if SIMILAR,  update current Table1  record with PK from table2
                        t1.Edit
                        t1!Table2ID_FK = t2!Table2ID_PK
                        t1.Update
    
                        'if similar then move to last T2 record
                        t2.MoveLast
                    End If
                    t2.MoveNext
    
                    'reset diff variables
                    TotalDiffs = 0
                    BigDiffs = 0
                    tmp = 0
                Loop    'inner loop**
    <snip>


    If you decide to delete all records from Table2, you SHOULD do a "Compact and Repair" before clicking the "Prune" button.
    This is to reset the autonumber initial number.


    Maybe.....I hope....fingers crossed......
    Attached Files Attached Files
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  14. #29
    Bob M is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Mar 2013
    Posts
    29
    Last night I tried to calc what the first 14 records of Table 1 should have in the Table2ID field

    I calculated thus:1, 2, 3, 3, 3, 3, 4, 4, 4, 4, 5, 5, 5, 5

    Now after running your code I think I see this: after you reorder the records in Table 1 (by Date/Time)

    b, b, b, 3, 3, 3, b, 4, 4, 4, b, 5, 5, 5, 14, 15, 6, 3, 3 , 4

    The blanks tell me that the code is not writing to this field when and only when a new record is added to Table 2

    I think that it should - I would like it to

    The 14, 15 in records 15 and 16 seem out of sync and wrong

    Bob M

    p.s. we (YOU) are so close

    I have just rechecked the 8th record - the first where we differ
    My calc was incorrect - the Diffs come to 12 and 4 so it is similar to record 4 in Table 2
    It is not similar to record 3 in Table 2: Diffs are 32 and 26

    Looking at record 12 in Table 1:

    You are correct again - table2 - record 5 is similar

    Record 15 Table 1:
    gets a '6' I think and this record should be written to Table 2 BUT is not

    My head really hurts now...................

  15. #30
    ssanfu is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    7,153
    Record 15 Table 1:
    gets a '6' I think and this record should be written to Table 2 BUT is not
    OK, I don't understand ...Again!!



    I added another table (Table3) and form .
    I added a field to Table2 to know what record from Table1 was written to Table2.
    Attached Files Attached Files
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

Page 2 of 3 FirstFirst 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 03-07-2017, 04:02 PM
  2. Replies: 0
    Last Post: 03-12-2013, 01:51 PM
  3. writing data from recordset to table
    By akrylik in forum Access
    Replies: 5
    Last Post: 05-23-2012, 04:48 PM
  4. Replies: 3
    Last Post: 07-13-2011, 07:01 AM
  5. Writing data from textbox to table
    By mll in forum Forms
    Replies: 4
    Last Post: 03-10-2010, 04:10 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums