PDA

View Full Version : Appending Multiple Tables into One



john
08-11-2004, 11:27 AM
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

TonyJollans
08-11-2004, 12:46 PM
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?

john
08-12-2004, 12:46 AM
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

TonyJollans
08-12-2004, 01:50 AM
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

john
08-12-2004, 02:08 AM
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.

SJ McAbney
08-12-2004, 02:55 AM
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.

john
08-12-2004, 03:10 AM
Hi Abulafia,

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

Thanks
John

SJ McAbney
08-12-2004, 03:33 AM
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.

TonyJollans
08-12-2004, 06:32 AM
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:

Sub AppendAll()

DoCmd.SetWarnings False

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

DoCmd.SetWarnings True

End Sub


,, 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.

SJ McAbney
08-12-2004, 08:53 AM
Another method could be to use a UNION query to join the tables and then turn it into a MakeTable query.

sgrant
08-23-2004, 08:36 AM
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.