Make vs Delete+Append

[Geoff]

In another post someone says to use delete/append instead of make when
repeatedly importing data.

On a daily basis, I will be importing data, (14 ISAM files), rebuilding 7
intermediate tables, (to somewhat normalize some of the data), then running
another make query to extract "yesterday's" data that will then be uploaded
to a SQL server.

Leaving all other questions aside, is it better to delete/append rather than
to make?

Thanks,
Geoff

 

[Jose Ruben Gonzalez-Baird]

I like using make queries if i'm relying on them for static data and won't
necessarily being refreshing them on a regular basis.

one example is for archiving or "snapshotting" data. i also use make
queries for record sources for table record counts and link the record count
values to Word documents using mail merge.

delete/append queries should be used for tables that are used for
intermiedate purposes. so in your case, it seems more appropriate to use
append/delete queries for the rebuilding of the 7 intermediate tables, and
using make queries for the data that will be extracted.

i tend to prefer make queries more than a lot of other users though, and i'm
sure you'll get differing opinions. ruben

 

[John Spencer]

It depends.

A problem with make queries is that you don't have a lot of control over
the resulting table. No indexes, fields can be the wrong type, etc.

With a predefined table, you can apply indexes (one time) and ensure the
data types of the fields.

In either case, you will need to compact your database on some regular
basis to get rid of the bloat.

I prefer to stay away from make table queries, but if they work for you
then go for it. In the situation you describe I see little advantage to
either method.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Geoff]

I guess my preference, being an Access noob, is to use Makes, (one query
instead of two,) but John brings up a very good point. Make queries use
default field sizes/types. So, I have 7 tables containing lots of text
fields with 255 length. What a waste of space, and I have to "Trim"
everything. So, I'll be converting these to Delete/Appends.

Thanks for your input guys!

Geoff