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.

Jeff wrote:
> 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
--
Smartin

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


"Jeff" <(E-Mail Removed)> wrote in message
news:67B2DCB0-907F-41D6-B76E-(E-Mail Removed)...
> 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.

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


Jeff Boyce wrote:
> 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
>
>
> "Jeff" <(E-Mail Removed)> wrote in message
> news:67B2DCB0-907F-41D6-B76E-(E-Mail Removed)...
> > 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.

(E-Mail Removed) wrote:
> 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?

--
Smartin

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


Smartin wrote:
> (E-Mail Removed) wrote:
> > 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?
>
> --
> 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


Smartin wrote:
> (E-Mail Removed) wrote:
> > 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?
>
> --
> Smartin

(E-Mail Removed) wrote:
> 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 wrote:
>> (E-Mail Removed) wrote:
>>> 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?
>>
>> --
>> Smartin
>

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?

--
Smartin

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



Smartin wrote:
> (E-Mail Removed) wrote:
> > 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 wrote:
> >> (E-Mail Removed) wrote:
> >>> 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?
> >>
> >> --
> >> Smartin
> >
>
> 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?
>
> --
> Smartin