Results 1 to 4 of 4
  1. #1
    Newtrick is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    7

    using queries to merge multiple tables

    Hello. I am trying to create a process whereby a separate table would be updated for each vendor with the exact same columns and then via query create one table with the different vendors listed top to bottom. Would be the same as a copy/paste from one spreadsheet to new "records" on another spreadsheet and so on. Can anyone help with the easiest way to do this? An alternative is to import the new records into an existing table and then write the query based on the consolidated table. Would prefer for users to be able to import data into discrete vendor tables if possible. Thanks in advance for any guidance on this issue.

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    It is considered "bad database form" to have multiple tables of similar structures and data. It makes for a lot of unnecessary work. Any time you had to add a new vendor, you would need to create a new table and queries. A well-designed database would not require the addition of new objects (like tables), just because of data for a new client.

    It would be better to have one table, and to have a field for vendor name/code, which you can filter on in a query (to return the records for a particular vendor).
    You could even make it a dynamic Parameter Query, which asks for the vendor when you open it, so you can have just one query return the data for any vendor that you want.

  3. #3
    Newtrick is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    7
    Very helpful. Thanks. I am struggling with being able to upload new records from excel into an existing table, so if you could provide some guidance that would be great. Also (I haven't tried this yet) would it make sense to try to upload from Excel into a form that appends the data to the table? There are about 20 columns so it would be a big task manually.

  4. #4
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    There are a few ways you could go about this.

    1. You could try importing the Excel data directly into your Access data table.
    2. If you need to do any sort of filtering or manipulation to your data table before importing into your Access data table, then it is best to either:
    a. import the data into a temporary table first in Access, and then do an Append Query to write what you need to your Access data table
    b. link your Excel table into Access, and then do an Append Query to write what you need to your Access data table

    Here are some helpful things to do Google searchs on if you need help with particular topics. There are even YouTube video tutorials, that show you how to do this (being a visual person, I love being able to see it be done):
    access importing data into existing table
    access create append query

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

Similar Threads

  1. Replies: 5
    Last Post: 10-06-2017, 11:49 AM
  2. Making queries from multiple tables
    By amywilson95 in forum Queries
    Replies: 11
    Last Post: 12-17-2015, 05:24 AM
  3. Reports on multiple tables / queries
    By drnld in forum Access
    Replies: 5
    Last Post: 07-02-2014, 09:04 AM
  4. Replies: 3
    Last Post: 10-18-2013, 07:29 AM
  5. Replies: 1
    Last Post: 02-27-2012, 05:18 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