Results 1 to 11 of 11
  1. #1
    notoriusjt2 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2020
    Posts
    6

    Should I use two tables here?


    I have a query that spits out an excel sheet of data every morning. This data is appended to the prior day, so if I have 20 lines of data each day I will have 100 at the end of the week. I want to review these lines of data one-by-one and move them from the excel sheet to a new table. What is the most efficient way to accomplish this?

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I don't understand what you want to accomplish. Why copy data to another table? Why is Excel involved?
    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.

  3. #3
    notoriusjt2 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2020
    Posts
    6
    the query that runs every morning gives me the spreadsheet. I have this setup as a linked table in Access. I would like to be able to edit the data without screwing up the existing excel sheet

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Why would editing records in Access impact data already exported to Excel? Unless Excel is linked to Access table and pulling table in full.

    If you have link to Excel, can run an INSERT SELECT action to copy records from the Excel link to another table. Or simply copy/paste.

    Still not clear what you are trying to accomplish.
    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
    notoriusjt2 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2020
    Posts
    6
    here is what I am trying to accomplish. I have an existing spreadsheet with data that gets appended (automtically at 5am) to the bottom every day. Rather than copy/paste every day, I would like this spread sheet to load into access automatically when I open it. As I view each one of these records, I would like to be able to make changes and add notes and then save it to a new table while deleting the original record from the existing spreadsheet. This way, once reviewed, I will never accidentally review the same record twice. Maybe there is a better way to accomplish this?

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    "I have a query that spits out an excel sheet of data every morning" - spits out from where?

    By 'load into Access' do you mean just link or actually import records to table? If the latter, then just import to destination table and edit these records for additional data. I don't understand why you want to use two tables.

    Deleting row(s) from Excel would be difficult part. Would require Excel automation code and quite possibly the linked table would have to be closed.
    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
    notoriusjt2 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2020
    Posts
    6
    the excel sheet is generated from an automated SQL query.

    allright, lets say I just used one table. how can I import the data from the existing excel sheet while ensuring that the data never gets imported again. Keep in mind this excel sheet will have the same data in it every day while adding some new data at the end.

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    "automated SQL query" doesn't tell me what data source is. Is it SQLServer database?

    Why don't you have Access link to the data source instead of intermediate Excel?

    Don't import same data again by applying filter criteria. What determines 'new' data - a date?
    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
    notoriusjt2 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2020
    Posts
    6
    this is way above my knowledge level but a program automatically runs at 5am, cross references a bunch of different databases, and compiles a report for me to review each day. the output is an excel sheet.

    I would like this data to be available immediately on open access (linked table seemed to be working). I really don't want to do any filtering, copy/pasting, etc. The records will be reviewed using a user form with previous and next buttons. A save button will be included to save the updated record with my added notes. Where should I be saving this record?

  10. #10
    notoriusjt2 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2020
    Posts
    6
    also it might be important to add that I would like to add columns to the table that are not currently existing in the excel sheet

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I just confirmed it is possible to input info into UNBOUND controls on a form bound to linked Excel. So can have a form to review each record, enter additional info, then save data to new record in local table. Again, only 1 local table is needed.
    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 04-18-2018, 06:56 AM
  2. Replies: 17
    Last Post: 04-09-2018, 04:39 PM
  3. Replies: 2
    Last Post: 07-15-2014, 10:39 AM
  4. Replies: 1
    Last Post: 12-11-2013, 01:18 PM
  5. Replies: 4
    Last Post: 11-22-2013, 11:20 AM

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