Page 1 of 4 1234 LastLast
Results 1 to 15 of 50
  1. #1
    Bob M is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Mar 2013
    Posts
    43

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

    Hello

    I have 2 tables:
    1) has 200 records
    2) currently has only 1 record being the first record in Table 1

    Each record has 10 fields, each field being a single integer from 0 thru 9

    Total Diffs = sum of abs(Table(1) Field n - (Table(2)) Field n, n= 1 to 10
    Big Diffs = as above BUT only when the diff > z

    I wish to write a program that will do the following:-
    a) read next record in table 1 [starting with record 2]
    b) For all records in table 2
    calculate Total Differences and Big Differences
    if TotalDiff < x or BigDiff < y
    go to (a)
    else if we are at the last record in table 2
    write the current record (table(1)) to table (2)
    goto (a)

    Any help in how to correctly write the Access code would be appreciated

    Bob M

    I have not used ms Access for several years and am very rusty

    p.s. can I adapt the following ?

    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM Table 1")

    'Check to see if the recordset actually contains rows
    If Not (rs.EOF And rs.BOF) Then
    rs.MoveFirst 'Unnecessary in this case, but still a good habit
    Do Until rs.EOF = True

    'Perform calculations (as above)

    If new record is required in Table 2 (i.e. both calcs above return positive)
    INSERT INTO Table 2 (ID, PIC1, PIC2...PIC10)
    VALUES (ID, PIC1, PIC2.....PIC10) 'Move to the next record. Don't ever forget to do this.
    rs.MoveNext
    Loop
    Else
    MsgBox "There are no records in the recordset."


    End If

    MsgBox "Finished looping through records."
    Last edited by Bob M; 01-09-2018 at 07:33 PM. Reason: suggested coding

  2. #2
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    It *looks* you could adapt the code, but it is hard to follow what you want when you don't use real names and you are not specific.

    For instance, you have
    I have 2 tables:
    1) has 200 records
    2) currently has only 1 record being the first record in Table 1
    Does Table1 have 1 record or do you mean to start with Table1, record1?
    What is "z"?
    Where did it come from?

    Examples of the record/field values for the calculations would help tremendously.
    Or you could post the DB with just a few example records.......


    Each record has 10 fields, each field being a single integer from 0 thru 9
    Do you mean that in Table1, the field names are "1", "2", "3", etc???? If yes, those "names"will cause you problems when the Access gnomes wake up. They get very persnickety when field names begin with a number.....

  3. #3
    Bob M is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Mar 2013
    Posts
    43
    Table 1 has 200 records and I mean to start with record 2 in Table 1
    x, y and z are integers

    the calcs are as follows:-
    0 1 0 1 1 3 2 8 7 9 Record 1
    2 2 0 1 0 1 1 3 3 9 Record 2
    2 1 0 0 1 2 1 5 4 0

    16 = sum of abs differences
    0 0 0 0 0 0 0 5 4 0


    9= sum of absolute diffs > 4 say

    so if 16 > x and 9 > y -> append record from table 1 to table 2

    Basically, I wish to compare each table1 record with each and every table 2 record
    If calcs are positive for all comparisions, then add the table 1 record to table 2

    Table 2 starts off with 1 record but builds up as records are added from Table 1

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Total Diffs = sum of abs(Table(1) Field n - (Table(2)) Field n, n= 1 to 10
    Big Diffs = as above BUT only when the diff > z
    Total Diffs
    0 1 0 1 1 3 2 8 7 9 Table2...Record 1
    2 2 0 1 0 1 1 3 3 9 Table1 ...Record 2
    -------------------------------------------------
    2 1 0 0 1 2 1 5 4 0 16 = sum of abs differences (got this)

    ================================================== ==============

    (diff > z)
    z = 4
    (absolute diffs > 4 say)


    Big Diffs
    0 1 0 1 1 3 2 8 7 9 Table2...Record 1
    2 2 0 1 0 1 1 3 3 9 Table1 ...Record 2
    -------------------------------------------------
    0 0 0 0 0 0 0 5 4 0 9= sum of absolute diffs > 4 say

    0 0 0 0 0 0 0 5 0 0 5= sum of absolute diffs > 4 say
    If [Big Diffs] has to be > z, then wouldn't the sum of absolute diffs be 5 instead of 9, since 4 is not greater than z(which is 4)?




    so if 16 > x and 9 > y -> append record from table 1 to table 2 ( Not sure what/were the values of x and y are/come from)






    Am I getting closer??

  5. #5
    Bob M is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Mar 2013
    Posts
    43
    Quote Originally Posted by ssanfu View Post
    Total Diffs
    0 1 0 1 1 3 2 8 7 9 Table2...Record 1
    2 2 0 1 0 1 1 3 3 9 Table1 ...Record 2
    -------------------------------------------------
    2 1 0 0 1 2 1 5 4 0 16 = sum of abs differences (got this)

    ================================================== ==============

    (diff > z)
    z = 4
    (absolute diffs > 4 say)


    Big Diffs
    0 1 0 1 1 3 2 8 7 9 Table2...Record 1
    2 2 0 1 0 1 1 3 3 9 Table1 ...Record 2
    -------------------------------------------------
    0 0 0 0 0 0 0 5 4 0 9= sum of absolute diffs > 4 say

    0 0 0 0 0 0 0 5 0 0 5= sum of absolute diffs > 4 say
    If [Big Diffs] has to be > z, then wouldn't the sum of absolute diffs be 5 instead of 9, since 4 is not greater than z(which is 4)?




    so if 16 > x and 9 > y -> append record from table 1 to table 2 ( Not sure what/were the values of x and y are/come from)

    Am I getting closer??
    x and y are chosen by me

    Total Diffs = 16 and Big Diffs = 9 say

    Let x = 20 and y = 8 say

    Then if Total Diffs > 20 and Big Diffs > 8, after comparing all records in Table 2 with the latest record in Table 1, append Table1 record to Table 2

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by Bob M View Post
    I have 2 tables:
    1) has 200 records
    2) currently has only 1 record being the first record in Table 1

    I wish to write a program that will do the following:-
    a) read next record in table 1 [starting with record 2]
    b) For all records in table 2
    So for EACH record in Table1 (except the first record), do the calculations/appends for EACH record in Table2?

    If "Table 1" has 11 records and "Table 2" had 5 records, you want to compare EACH of the 10 records in "Table 1" to EACH of 5 records in "Table 2"?

  7. #7
    Bob M is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Mar 2013
    Posts
    43
    Quote Originally Posted by ssanfu View Post
    So for EACH record in Table1 (except the first record), do the calculations/appends for EACH record in Table2?

    If "Table 1" has 11 records and "Table 2" had 5 records, you want to compare EACH of the 10 records in "Table 1" to EACH of 5 records in "Table 2"?
    Look on the exercise as a pruning of the 200 records in Table 1
    Table 2 will end up with say 20 records where each record in Table 1 can be pigeon-holed into being 'similar' to one record in Table 2
    The similarity is defined by the two calculations
    i..e Total Differences and Big Differences

    Table 1 has say 200 records
    Start by copying the first record of Table 1 to an empty Table 2
    Then consider record 2 in Table 1
    It is either 'similar' to the single record in Table 2 (and we move on) or it is different - if so , append it to Table 2
    ----------------------
    Consider record 3 in Table 1
    It is either 'similar' to one of the 2 records in Table 2 (and we move on) or it is different to both records in Table 2 - and so we append it to Table 2
    -----------
    etc.
    etc.

  8. #8
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Does Table2 always start out with 0 records?

    Steps:
    Delete all records from Table2
    Copy Table1 record 1 to Table2
    Compare Table1 record 2 to the record 1 in Table2
    if records similar** move on ELSE copy Table1 record to table2.
    loop Through Table 2 records
    Now move to Table1 next record and start over until reach end of records in Table 1


    **Similar means (TotalDiffs> X) AND (BigDiffs>Y) Where X, Y and Z are values from controls on a form..

  9. #9
    Bob M is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Mar 2013
    Posts
    43
    Quote Originally Posted by ssanfu View Post
    Does Table2 always start out with 0 records?

    Steps:
    Delete all records from Table2
    Copy Table1 record 1 to Table2
    Compare Table1 record 2 to the record 1 in Table2
    if records similar** move on ELSE copy Table1 record to table2.
    loop Through Table 2 records
    Now move to Table1 next record and start over until reach end of records in Table 1


    **Similar means (TotalDiffs> X) AND (BigDiffs>Y) Where X, Y and Z are values from controls on a form..
    Table 2 only starts of being empty the first time I run the program
    Once I have done the initial setup of Table 2, the process then reverts to having a new record added to Table 1 periodically - then - a comparison with each record in Table 2 until you decide that the new record is similar to one in Table 2 (in which case we do nothing) or you reach the end of Table 2 - find no similarity and so append the latest record in Table 1 to Table 2

    Similar means Total Differences <= threshold x or Big Differences <= Threshold y

    Different means TDiff > x AND BigDiffs > y

  10. #10
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Not totally sure I understand... but try this

    One problem I see is that tables have no order. So Table1 record 1 could be different each time the dB is opened and the code executed.Is there a date field for when a record is added to Table1?


    Add a few (or a lot) of records to Table1. The first run will copy Table1 record 1 to Table2.
    Attached Files Attached Files

  11. #11
    Bob M is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Mar 2013
    Posts
    43
    Hello Steve

    Thank you for your patience

    I have changed the form numbers to 19, 7, and 3

    At this stage, I click on the button and
    I get a screen saying
    Loop 10
    Total Diffs = 25
    Big Diffs = 12

    I press the 'OK' button and
    I get a message saying run-time error 3134. Syntax error in Insert Into statement

    CurrentDb.Execute sSQL, dbFailOnError is the highlighted line

    I have looked at your code and it appears on first glance to be spot-on

    Any ideas ?
    Bob M

    p.s. currently using Access 2016
    Last edited by Bob M; 01-11-2018 at 06:19 PM. Reason: update

  12. #12
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    It was/is a PEBKAC error. I had code that basically said "If there are no records in Table2, abort.


    Then I found a couple of other errors and a couple of major logic errors. I guess I was in a hurry last night and, well, stuff happens.


    Try this. There are no records in either table........
    Attached Files Attached Files

  13. #13
    Bob M is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Mar 2013
    Posts
    43
    Hi Steve

    Records are certainly being added now

    I need time to digest what is happening

    Thanks again for you time and patience

    Bob M
    Dunedin
    New Zealand
    (aged 70)

  14. #14
    Bob M is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Mar 2013
    Posts
    43
    Hi Steve

    I have run the program with a couple of dozen records in Table 1

    Looking at the code - bit confused

    A new record is only to be written to Table 2 if the 2 set of differences are above the threshold limits for all comparisons with Table 2 records - not just the first Table 2 record

    It seems to me that the code writes a new record to Table 2 if the differences exceed thresholds when comparing the first Table 2 record - not ALL table 2 records

    Do I understand correctly?

    Bob M

    I guess what I am trying to say is:-

    Table 1 has several records - Table 2 has one record (being Table 1 record)

    Compare 2nd Table1 record with 1st Table 2 record - if 'different' write it to Table 2, if similar move on to next Table 1 record

    Compare 3rd Table1 record with 1st Table 2 record - if 'similar' do not copy but if different then
    compare 3rd Table1 record with 2nd Table 2 record(if there is one) - if similar do not copy and goto 4th Table 1 record, if different write it to Table 2

    In other words, each Table 1 record MUST be different to ALL existing Table 2 records before it is copied across

  15. #15
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    So we are on the same page and using the same data/records, would you post your dB with the records you are using in Table1? Table2 should be empty?


    In other words, each Table 1 record MUST be different to ALL existing Table 2 records before it is copied across
    When you say ALL Table2 records, that means Table1 records need to be compared to the newly added records in Table2 also?

    Table2 starts out with 1 record, at some point another record gets written to table 2 (now there are 2 record in Tabel2).
    The next Table 1 record gets compared to BOTH records in Table2?

    At some point Table2 has 5 records in it (still in the same 'run'), so the next record in Table1 gets compared to all 5 records in Table2 (not just the original 2 records)?

    (my head hurts )

    Still would like your dB with the records you are using in Table1..

Page 1 of 4 1234 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 03-07-2017, 05:02 PM
  2. Replies: 0
    Last Post: 03-12-2013, 02:51 PM
  3. writing data from recordset to table
    By akrylik in forum Access
    Replies: 5
    Last Post: 05-23-2012, 05:48 PM
  4. Replies: 3
    Last Post: 07-13-2011, 08:01 AM
  5. Writing data from textbox to table
    By mll in forum Forms
    Replies: 4
    Last Post: 03-10-2010, 05: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
  •  
Other Forums: Microsoft Office Forums