Consulting

Results 1 to 11 of 11

Thread: Appending Multiple Tables into One

  1. #1
    VBAX Regular
    Joined
    Jul 2004
    Posts
    13
    Location

    Appending Multiple Tables into One

    Hi everybody,

    I am a new user of Access. I am having 3 tables in a database file(as attached). I like to append all the files into one table.
    Could any one help me how to append the 3 tables into one table?

    Thanks in advance
    John

  2. #2
    VBAX Master TonyJollans's Avatar
    Joined
    May 2004
    Location
    Norfolk, England
    Posts
    2,291
    Location
    Take a look at Append Queries.

    We need a bit more detail to give a full answer. Are you looking for an automated solution? The tables do not all have the same layout; they also contain autonumber fields, do you want to maintain the values after merging or have new ones assigned?
    Enjoy,
    Tony

    ---------------------------------------------------------------
    Give a man a fish and he'll eat for a day.
    Teach him how to fish and he'll sit in a boat and drink beer all day.

    I'm (slowly) building my own site: www.WordArticles.com

  3. #3
    VBAX Regular
    Joined
    Jul 2004
    Posts
    13
    Location
    Hi,

    I am sorry, I started working Access recently. Could you give me a clear visibility of Append queries?
    I have attached the file with additional details. In the attached files, Main, Sheet1 and Sheet2 are the input files and All is the output. Here i just copy the three tables records and created a single file (All) manually. But I am dealing with some millions of records in each file where i have to append into a single file. Highly appreciated if there is an automated solution available.

    Thanks and regards,
    John

  4. #4
    VBAX Master TonyJollans's Avatar
    Joined
    May 2004
    Location
    Norfolk, England
    Posts
    2,291
    Location
    Hi John,

    Got to be quick at the moment - will check back later - here's how to start

    From the main Access Window, click on Queries at the left
    In the body of the window double click on Create Query in Design View
    In the Show Table window Select one of your tables, say, Main and press Add
    Then Press Close
    In the little window showing the Main Table, double click on the asterisk
    Right click in the grey space beside the Main window
    From the popup, hover over Query Type and then select Append Query
    In the dialog box which appears enter All under Table name
    Close the Query designer
    Reply Yes to Do you want to save
    Give it a name, or stick with the default Query1
    Now double click on the new query in the main access window to run it
    Reply Yes twice

    Main has now been added to All
    Enjoy,
    Tony

    ---------------------------------------------------------------
    Give a man a fish and he'll eat for a day.
    Teach him how to fish and he'll sit in a boat and drink beer all day.

    I'm (slowly) building my own site: www.WordArticles.com

  5. #5
    VBAX Regular
    Joined
    Jul 2004
    Posts
    13
    Location
    Hi Tony,

    Marvellous. I got it. Thanks a lot for your kind patience. I append the first table to 'all' then I second table to 'all'. Could it be done automatically because if i have 10 files I have to create 10 queries and each I have to run it?

    Thanks
    John.

  6. #6
    VBAX Tutor SJ McAbney's Avatar
    Joined
    May 2004
    Location
    Glasgow
    Posts
    243
    Location
    Based on that sample database I can only advise you to normalise your data. You should not have a repeating group (i.e. Title1, Title2, Title3, etc.) as this violates the First Normal Form of database design.

  7. #7
    VBAX Regular
    Joined
    Jul 2004
    Posts
    13
    Location
    Hi Abulafia,

    I have updated my sample database in my second attachment(work_sort.zip).
    Could you please check that attachment?

    Thanks
    John

  8. #8
    VBAX Tutor SJ McAbney's Avatar
    Joined
    May 2004
    Location
    Glasgow
    Posts
    243
    Location
    There's not much I can comment upon until I see appropriately named fields and relevant example data as, at the moment, it's just a mess of randomly typed characters.

    The Fields a, b, c, d, e are suspicious.

  9. #9
    VBAX Master TonyJollans's Avatar
    Joined
    May 2004
    Location
    Norfolk, England
    Posts
    2,291
    Location
    Hi John,

    There are various ways to attack this. If you have created 10 Append Queries, you can easily run them from a VBA procedure, something like this:

    [VBA] Sub AppendAll()

    DoCmd.SetWarnings False

    DoCmd.OpenQuery "QueryMain"
    DoCmd.OpenQuery "QuerySheet1"
    DoCmd.OpenQuery "QuerySheet2"

    DoCmd.SetWarnings True

    End Sub
    [/VBA]

    ,, where QueryMain, etc are the names of your queries.

    You may want to look at stopping it being run more than once, and I don't know what else. Your sample is so obviously trivial that I can't really say more.
    Enjoy,
    Tony

    ---------------------------------------------------------------
    Give a man a fish and he'll eat for a day.
    Teach him how to fish and he'll sit in a boat and drink beer all day.

    I'm (slowly) building my own site: www.WordArticles.com

  10. #10
    VBAX Tutor SJ McAbney's Avatar
    Joined
    May 2004
    Location
    Glasgow
    Posts
    243
    Location
    Another method could be to use a UNION query to join the tables and then turn it into a MakeTable query.

  11. #11
    VBAX Regular
    Joined
    Aug 2004
    Location
    Mesquite, Texas
    Posts
    13
    Location
    If you plan on re-running this process each week/month/quarter you may want to make the first Append Query a Make-Table query. This will automatically delete and re-create the combined table each time you run it. To disable the do you want to do this messages add application.setwarnings = false before your code and application.setwarnings = true after your code. I am not sure "setwarnings" is exactly correct, but I will look it up if you need me to.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •