Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    tazzzz is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2015
    Location
    USA
    Posts
    37

    Import(copy) data from another table

    Hello all,
    My project has 2 tables: FullData and FromExcelData
    As the names suggested table "FromExcelData" is linked to an excel table that receives every day new data and FullData is a table that has all the columns from FromExcelData and a some more.
    What should I do to have all data from FromExcelTable to FullData(since FromExcelData is linked to an excel table and data is updated in real time) and be able to add data to FullData.



    FromExcelData has this collumns: Jobnumber, PieceMark,Qty, Weight,ProdDate
    FullData has this collumns:PieceID, Jobnumber, PieceMark,Qty, Weight,ProdDate,Cost,Shipped, Note.
    Columns 2-5 need to be filled from FromExcelData and the other will be filled at a later time by a designated person.

    Thank you all in advance for your help

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Since you have the 2 tables, make an append query to add the records.
    make a macro with this query.
    then just save the excel file, run the macro.

  3. #3
    tazzzz is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2015
    Location
    USA
    Posts
    37
    ranman256 thank you for your reply.
    I am not very advanced in acces and if you like and have the necessary time to guide and help me with this task it will be great.
    Thank you.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Is the Excel file cumulative (edits and new rows) or is the file replaced and has only new records?

    Why is Excel involved? Why not data entry/edit in Access?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    tazzzz is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2015
    Location
    USA
    Posts
    37
    Helllo June7,
    Excel is a cumulative data. Every day a number of rows are added. That means the only different thing from the previous day is the date (all the other fields may contain the same data as in the previous day.) Excel is involved because other departments use the same data in different ways using excel instead of acces.
    I was able to append table FullData with data from FromExcelData but at this time I am stuck because I have no idea how to update the table FullData with new records from FromExcel.
    Thank you for the help

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I am confused. You say you were able to append data then say you have no idea how to update with new records. Appending is adding new records.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    tazzzz is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2015
    Location
    USA
    Posts
    37
    I was able to append all data from one table to another. When new data was inserted in excel, I run the query again and instead of having only new data inserted, all data was brought again(old+new).
    So what I need, is to bring only new data inserted in excel.

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Apply filter criteria. How can new records be determined? A date or a unique ID?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    tazzzz is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2015
    Location
    USA
    Posts
    37
    The only different thing from the previous day is the date (all the other fields may contain the same data as in the previous day.)
    Could you also give me an example how to filter the query by new date?(I am a beginner in acces and any piece of information I get is well appreciated)

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    An example of a simple SELECT query:

    SELECT * FROM table WHERE fieldname>=[enter date here];

    So an APPEND query could be like:

    INSERT INTO table(field1, field2, field3) SELECT field1, field2, field3 FROM source WHERE fieldname>=[enter date here];
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    tazzzz is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2015
    Location
    USA
    Posts
    37
    Is there a way to have acces changing the date automatic inside the query by selecting the last date added from the table?

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Maybe with DMax()

    INSERT INTO table(field1, field2, field3) SELECT field1, field2, field3 FROM source WHERE datefield>DMax("datefield", "table");

    or nested subquery

    INSERT INTO table(field1, field2, field3) SELECT field1, field2, field3 FROM source WHERE datefield>(SELECT Max(datefield) AS MaxDate FROM table);
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    tazzzz is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2015
    Location
    USA
    Posts
    37
    I tried your first suggestion(code bellow) and I keep getting a message of error:syntax error INSERT INTO statement.
    Also this are the fields for each table:
    FullData:ID;JOB NUMBER,PCS#,PCS;SQ FT; PRODUCTION DATE; COST; BILLED; NOTE.
    FromExcelData:JOB NUMBER; PCS#;PCS;SQ FT;PRODUCTION DATE
    As for the second suggestion I need to learn more about nested subquery.
    Could you please take a look and see what is wrong with the code?
    Thank you for your time and patience !

    Code:
    INSERT INTO FullData( JOB NUMBER, PCS#, PCS, SQ FT, PRODUCTION DATE) 
    SELECT JOB NUMBER, PCS#, PCS, SQ FT, PRODUCTION DATE  FROM FromExcelData 
    WHERE PRODUCTION DATE>DMax("PRODUCTION DATE", "FullData");

  14. #14
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Names with spaces and special characters/punctuation (underscore is exception) must be enclosed in []. Advise to avoid those in naming convention. So: [JOB NUMBER], [PCS#], [SQ FT], [PRODUCTION DATE]

    Better would be PCSnum or PCS_Num, JobNumber or Job_Num.

    Also, all upper case is harder to read.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  15. #15
    tazzzz is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2015
    Location
    USA
    Posts
    37
    I followed your advice (see code bellow). I don't get any error message, query is running but it brings all the data instead of only new recorded data.
    Code:
    INSERT INTO FullData ( [JOB NUMBER], [PCS#], PCS, [SQ FT], [PRODUCTION DATE] )
    SELECT [JOB NUMBER], [PCS#], PCS, [SQ FT], [PRODUCTION DATE]
    FROM FromExcelData
    WHERE [PRODUCTION DATE]>DMax([PRODUCTION DATE], "FullData");
    Also , in WHERE statement should be
    Code:
    WHERE datefield>DMax([PRODUCTION DATE], "FullData");
    or as I did it in the above code?
    Thank you

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

Similar Threads

  1. How to copy data within the same table?
    By price12 in forum Access
    Replies: 1
    Last Post: 04-23-2014, 08:34 PM
  2. Replies: 13
    Last Post: 11-14-2013, 04:13 PM
  3. Replies: 6
    Last Post: 05-23-2013, 08:14 AM
  4. Replies: 3
    Last Post: 03-05-2013, 05:14 AM
  5. copy data from one table to another
    By Sureshbabu in forum Access
    Replies: 1
    Last Post: 01-08-2012, 01:27 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