Append more then one table

[Guest]

Hi,

I dont use access much.

I have 7 Tables and I need to combine these into 1 table, and the tables are
the same format, with the same column names.

Can I make an append query that combines all 7 tables into one, in one
querry - because I was using 7 append queries but this was cumbersome.

Also can I delete the old 7 tables automatically, with a macro?

Thanks for your help.

 

[Smartin]

Hi,

I dont use access much.

I have 7 Tables and I need to combine these into 1 table, and the tables are
the same format, with the same column names.

Can I make an append query that combines all 7 tables into one, in one
querry - because I was using 7 append queries but this was cumbersome.

Also can I delete the old 7 tables automatically, with a macro?

Thanks for your help.

For the combine piece:

SELECT * INTO BIGTABLE
FROM
(SELECT * FROM TABLE1
UNION ALL
SELECT * FROM TABLE2
UNION ALL
....
);

For the delete piece, it's probably simpler to delete the tables
manually. Unless, that is, you actually have 700 tables.

HTH

 

[Jeff Boyce]

Jeff

How many times do you need to do this? If once, just use your multiple
append queries.

If you need to do this repeatedly, one approach would be to create a macro,
and in the macro, call each append (and each delete) query.

If you are importing data from some other source, there may be another
approach that simplifies your work even more... got details?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[dbahooker]

if you were using Access Data Projects; you could write a single stored
procedure to fire 7 different appends.

File that away under 'YET ANOTHER REASON ADP SLAUGHTERS MDB'


-Aaron
ADP Nationalist

 

[Smartin]

if you were using Access Data Projects; you could write a single stored
procedure to fire 7 different appends.

File that away under 'YET ANOTHER REASON ADP SLAUGHTERS MDB'


-Aaron
ADP Nationalist

But you would have to actually write "7 different appends" first, right?

 

[dbahooker]

no you would write it once; and then copy paste.. doing things in TSQL
is _ALWAYS_ faster than copying 7 different copies of similiar queries;
and deciding on a naming convention; etc.

-Aaron

 

[dbahooker]

copy and paste and then you can design different pieces within a single
sproc-- in design view.

MDB on STEROIDS. and I mean that in a positive way.. I personally love
steroids; and I think that they should be legal in all the sports
leagues.

-Aaron
ADP nationalist

 

[Smartin]

copy and paste and then you can design different pieces within a single
sproc-- in design view.

MDB on STEROIDS. and I mean that in a positive way.. I personally love
steroids; and I think that they should be legal in all the sports
leagues.

-Aaron
ADP nationalist

I don't understand where the efficiency part is. "Design different
pieces"? Isn't that a lot like copy/pasting several appends and
"tweaking" each one? Seems like it's all editing "TABLEX", "TABLEY",
"TABLEZ" stuff in the end? Or do I miss the point?

 

[aaron.kempf]

it's just easier to have this as one sproc instead of 7 queries.

one sproc -- instead of 7 queries.
one sproc -- instead of 7 queries.
one sproc -- instead of 7 queries.
one sproc -- instead of 7 queries.
one sproc -- instead of 7 queries.

doesn't it just sound soothing?

you can even bring a ton of sprocs together; name them spMySproc;1
spMySproc;2 and spMySproc;3

and then you just need to

EXEC spMysproc and it fires all 3 of them.

MDB is for babies.


-Aaron