Results 1 to 15 of 15
  1. #1
    b_rye_chan is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2015
    Posts
    18

    Access Beginner! Want to automatically append new external data to a "master" table


    So I'm completely new to Access so pardon any easy/dumb questions. I'm creating a master Contact List with set field values. I have many small contact lists I'm trying to import into this Contact list. I need these small lists accessible separate from the master Contact List but I'd like to have it so that whenever a new list is imported, it is automatically appended to the master Contact list/table. Is there an easy way to go about this or would I be looking at coding macro's to do the job? And if anyone can point me towards some good beginner tutorials that would be a bonus as well . Thanks in advance!

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Will probably need VBA code, not macros.

    What do you mean by 'small lists accessible separate from the master Contact List'? What are you importing from?
    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
    b_rye_chan is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2015
    Posts
    18
    So here's the scenario. I work in an office where each employee handles contacts from different companies. These "small lists" are each individual employees contact list. I want all these lists to feed into a master company list.
    All data is being imported through excel.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    So each employee has their own spreadsheet they maintain? This is nuts. You are probably on the path to loony farm.
    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
    b_rye_chan is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2015
    Posts
    18
    haha i know, directions from the boss man though. any suggestions on making it more efficient?

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Are the spreadsheets simple and uniform in structure? Can you set links to them? If you can, they can be used in a UNION query to merge the records into single dataset. Use the UNION query as your 'master' contact list. No importing and no VBA. Otherwise, review http://www.accessmvp.com/KDSnell/EXCEL_Import.htm
    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
    b_rye_chan is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2015
    Posts
    18
    Yes, all the spreadsheets will have the same fields so I think the UNION query should do the job. Now would the UNION take into account changes made to "child" spreadsheets, in other words, if someone made a change to one of the smaller tables, would it be reflected in the "master" contact list? That seems like something I would probably need VBA code for I'm guessing.

  8. #8
    b_rye_chan is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2015
    Posts
    18
    And giving that site a read over right now (not gonna lie, most of it's going right over my head haha)
    Also wanted to note I'm just using the Contacts Manager template that comes with Access 2016 as a starting point if that simplifies things.

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    The links and the UNION will be dynamic and automatically reflect data edits in the spreadsheets - no VBA needed. Unlike importing.

    Data cannot be edited through the spreadsheet links and data is never editable through UNION query.
    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.

  10. #10
    b_rye_chan is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2015
    Posts
    18
    Got it. Last problem (hopefully), I'm a little confused as to how to link the tables to the master contact list table, if that's how it works. I've imported the tables using the "Link to the data source by creating a linked table" option, is that the right import method or should I be using the Append method? Or was I supposed to import the data source into a new table in the database and manually link them?

    Sorry for the newbie-ness

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    That is setting a dynamic link to the spreadsheets. That is what I suggested - not import.

    Now build a UNION query that references the links. There is no designer/wizard for UNION, must be typed into SQL View of query builder. http://www.w3schools.com/sql/sql_union.asp

    There is a limit of 50 SELECT lines.

    The 'master' table is not involved in this approach. The UNION query would the 'master' list.

    Getting close to that loony farm yet?
    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.

  12. #12
    b_rye_chan is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2015
    Posts
    18
    O ya I think I'm deep in the loony farm at this point lol. Think I might be getting the idea now though, I'll post my progress (if any hehe). Thanks so much for the help so far! Really appreciate it

  13. #13
    b_rye_chan is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2015
    Posts
    18
    I'm running into some issues with UNION. I'm just trying a very simple UNION command
    Code:
    SELECT * FROM "Business Card Contacts"
    UNION ALL
    SELECT * FROM Contacts
    ORDER BY Company;
    It keeps telling me it's an incomplete query clause? From all the examples I've seen, it seems like it should work?

  14. #14
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Don't use the quote marks, use [] to delimit object names.

    Advise not to use spaces and special characters/punctuation (underscore is exception) in naming convention and [] will not be needed.

    Tables will need the same number of fields and in the same order in their design if you use * wildcard. The first SELECT sets the field names and types. Also, suggest constructing a field with calculation to identify data source:

    SELECT *, "Business" AS Source FROM [Business Card Contacts]
    UNION ALL
    SELECT *, "Contacts" FROM Contacts;
    Last edited by June7; 12-17-2015 at 06:08 PM.
    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
    b_rye_chan is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2015
    Posts
    18
    you good sir, are a wizard. slightly less in loony farm now haha

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

Similar Threads

  1. Replies: 1
    Last Post: 09-07-2015, 08:00 AM
  2. Replies: 2
    Last Post: 08-28-2015, 01:45 AM
  3. Replies: 11
    Last Post: 07-20-2015, 06:07 PM
  4. Replies: 3
    Last Post: 02-16-2015, 01:04 PM
  5. Replies: 5
    Last Post: 02-05-2013, 10:57 PM

Tags for this Thread

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