Page 3 of 4 FirstFirst 1234 LastLast
Results 31 to 45 of 50
  1. #31
    Bob M is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Mar 2013
    Posts
    43
    Not sure I comprehend what Table 3 is telling me



    Every record in Table 1 should have a Table 2 reference
    At the moment they do NOT

    Also Table 1 records MUST remain in their initial ordering while being processed
    At the moment they do NOT seem to be

    Otherwise, how can the 15th record (assuming that Table 1 is processed in Date/Time order ALWAYS) [2.1.2013:13] have a Table 2 reference of 15 when we only have 5 Table 2 records at that point ?

    Bob M

    I think (after the code run) that Table 1 records - the first 15 records - should look like this
    i.e. records in Table 1 retain their original order, all records have a Table 2 ID and the 15th record should have a '6' for Table 2 ID not '15'

    ID Table2_ID Local Time
    1 1 01.01.2013 00
    2 2 01.01.2013 01
    3 3 01.01.2013 02
    4 3 01.01.2013 03
    5 3 01.01.2013 04
    6 3 01.01.2013 05
    7 4 01.01.2013 06
    8 4 01.01.2013 07
    9 4 01.01.2013 08
    10 4 01.01.2013 09
    11 5 01.01.2013 10
    12 5 01.01.2013 11
    13 5 01.01.2013 12
    14 5 02.01.2013 11
    15 6 02.01.2013 12
    Last edited by Bob M; 01-15-2018 at 01:59 PM.

  2. #32
    Bob M is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Mar 2013
    Posts
    43
    Hi Steve
    finally figured what was wrong here...................
    the field 'localtime' is not an acceptable date time format - thus the jumping around of the Table 1 records

    All good now, except for the 178 Table 1 records which don't get a Table 2 reference each time a new Table 2 record is written

    Many thanks again for your assistance

    Bob M

  3. #33
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Glad you got it working
    Having read this thread from the sidelines, I think Steve deserves a huge reward for persevering with this thread
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  4. #34
    SierraJuliet's Avatar
    SierraJuliet is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2017
    Location
    Earth
    Posts
    211
    SELECT all, attribute, fields
    INTO NewTable IN NewDatabase
    FROM CurrentTable
    WHERE something = something OR something LIKE something
    ORDER BY LocalTime;

    Just exclude IN NewDatabase if it is the same database and exclude WHERE if there is no criteria.

    If you have anomalies then run:

    UPDATE TableName
    SET newfield1 = value
    WHERE
    oldfield1 = value;

  5. #35
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    @Bob,

    Sorry I've been MIA. My profile in Win7 went bad - managed to save most things.. Had to reformat, install Win 7, over 300 "Updates", install programs (not done yet), etc.

    Still at 50%....... but gaining ground.

    Try adding the lines (in BLUE):
    Code:
        Dim tmp As Integer    'holds sum of differences between fields of the two record sets
        Dim RecAdded As Integer
        Dim X As Integer  ' user provided from form
        Dim Y As Integer  ' user provided from form
        Dim Z As Integer  ' user provided from form
        Dim bSimilar As Boolean  'are T2 records/fields similar to T1 record/fields
    
        Dim NewPK As Long  'PK of new record added to Table2
    
        'used for debugging/tracing records
        Dim T1RC As Long
        Dim T2RC As Long
        Dim T1Count As Long
        Dim T2Count As Long
    Code:
                If Not bSimilar Then  'no records in common between T1 record and ALL T2 - add record to T2
                    With t2
                        .AddNew
                        NewPK = !Table2ID_PK
                        !PIC1 = t1!PIC1
                        !PIC2 = t1!PIC2
                        !PIC3 = t1!PIC3
                        !PIC4 = t1!PIC4
                        !PIC5 = t1!PIC5
                        !PIC6 = t1!PIC6
                        !PIC7 = t1!PIC7
                        !PIC8 = t1!PIC8
                        !PIC9 = t1!PIC9
                        !PIC10 = t1!PIC10
                        .Update
                    End With
    
                    '###############################################
                    'Now Update Table1 non-similar record field with NEW PK added to table2
                     t1.Edit
                    t1!Table2ID_FK = NewPK
                    t1.Update
                    '###############################################
    
    
                    RecAdded = RecAdded + 1
    
                    If RecAdded Mod 10 = 0 Then
                        Me.tbT2Added = RecAdded
                        Me.Repaint
                    End If
                End If
    If a record in Table1 is checked against all records in Table2 and there are NO similar records, the record in Table1 is added to Table2 and the NEW Table2 ID is added to the record in Table1


    And maybe post a dB with the changed table1.LocalTime field????



    HOW did I miss that the field "LocalTime" was a text field and not a Date/time?????

  6. #36
    Bob M is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Mar 2013
    Posts
    43
    Hi Steve
    I am sorry to hear about your computer problems
    I did wonder whether I had annoyed you somehow
    I shall try adding the extra lines (blue) and will let you know the outcome

    Others seem to think that you have done too much for me - so once again, I am extremely grateful for your help

    Kind regards

    Bob M

  7. #37
    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're on the same page, would you post your copy of the dB with the field "LocalTime" corrected?

    Others seem to think that you have done too much for me - so once again, I am extremely grateful for your help
    The programming has been fun... the hard part has been understanding what you require when I don't know anything about your project.
    Hang in there - we'll get it.

  8. #38
    Bob M is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Mar 2013
    Posts
    43
    attached is Table 1 with Date and Time fields

    in your code we could easily sort on Table 1 ID rather than Date, Time

    regards

    Bob M

    p.s. having trouble uploading revised Table 1 - will try later ??

  9. #39
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Can't upload just table1. Must upload the accdb.

  10. #40
    Bob M is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Mar 2013
    Posts
    43
    oops - keep forgetting we are in access

    now I am getting error message - upload of file failed ??

    Bob M

  11. #41
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Do a "Compact and Repair", then Zip it.
    Try to attach to a reply

  12. #42
    Bob M is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Mar 2013
    Posts
    43
    here we go - try again after compact & repair

    yeah ! success

    I didn't delete all the records from Table 3

    Please check Table 1 design in case I have altered things

    Bob M
    Attached Files Attached Files

  13. #43
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Couple of things.....
    "Date", "Time" and "Open" are reserved words in Access and shouldn't be used for object (as in field) names.

    Why did you break "LocalTime" into two fields?

    Why is the date field still a TEXT type field instead of a DATETIME field type?

    I changed "Date" and "Time" to "LocalDate" and "LocalTime". (because of the reserved words)

    For fields "Open", "High", "Low" and "Close", what do the values/fields represent? Prices? Amt? Volumes?

  14. #44
    Bob M is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Mar 2013
    Posts
    43
    OK - confession time

    I eventually will move to a java program which I have written to trade various currencies on the Dukascopy [broker - in Switzerland] platform

    This includes using a Derby database and so I am only temporarily in 'Access'

    OHLC represent open, high, low & close prices for USD/JPY during the last hour

    Date & Time fields I have used before

    Hope this explains the scenario a bit clearer

    Bob M

  15. #45
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I knew that open, high, low & close were either volumes or prices. Just not sure which one.

    Since "LocalDate" was split, I converted "LocalDate" to a DateTime type field.

    When you sort a text type field, dates sort left to right. So if you sort the dates (in a test field), you get:
    (disregard the days)
    Code:
    yyyy-m-d
    2013-1-1   Jan
    2013-10-1  Oct
    2013-2-1   Feb
    Obviously this is wrong.
    If the text dates had 2 characters for the month and date, then they would sort correctly (like dates):
    Code:
    yyyy-mm-dd
    2013-01-01
    2013-02-01
    2013-03-01
    .
    .
    2013-10-01
    2013-11-01

    Two more modifications:
    - because the LocalDate field was split, I converted the "LocalDate" field to a Date/Time type field.
    - then then SQL for "t1" needs to be changed.
    Code:
    Set t1 = d.OpenRecordset("SELECT * FROM Table1 ORDER BY LocalDate, LocalTime")

    Try this dB
    Attached Files Attached Files

Page 3 of 4 FirstFirst 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