Trying to merge multiple tables with access

[pierrickbe]

Hi everyone. It's been tree days that i'm tryig to merge 7 forecasts
for my compagny. They are linked on my sharepoint server and I open
them with acces directly. They both have the same form and i want to
merge them into a new table where i can make statistics. Is that
possible ?

Thank you very much for your help

 

[Allen Browne]

1. Create a new table with the fields you want.

2. Create a query using the first table.
Change it to an Append query (Append on Query menu.)
Map the fields from the Sharepoint table to the table you just created.
Run the query.

3. Repeat step 2 for the other tables.

If this is something you regularly, you can save the 7 append queries, and
create a macro to fire them all (the RunSQL action.) Otherwise you don't
need to save the queries.

 

[Jehuty26]

Hi
Thank you very much for you help this is exactly what I was looking
for. Now could you tell me how to create macro ? is there something I
can read on it ??

 

[Allen Browne]

To create a macro:

- In Access 2007,click the Create tab of the ribbon, then Macro (right-most
icon.) When the macro window opens,click the 'Show All Actions' button on
the ribbon, or you won't see RunSQL.

- In previous versions, click the Macros tab of the Database window, and
click New.

 

[Ron2006]

Now you need to ask yourself if the next time you do this do you want
to merge the NEW tables with the newly created table or do you want to
have a fresh set of data.

If the second then you will also need to create a "Delete" query and
run it the first thing in your macro in order to empty the table for
the appending of the newer information.

Ron