How do I insert the name of a table into my "master" table?

[Adrienne]

So I'm doing an append query, appending data from about 300 separate
tables into one master table. I want to include the name of the
originating table name as a field for each record that I append into
the master table.

Does anyone out there have any ideas/suggestions on how I would go
about doing this, please? Responses are very much appreciated, thanks
in advance!

 

[Jeff Boyce]

Adrienne

Are you trying to set up a single append query that appends from ALL 300
tables at once? Are you confident that each of the 300 is "well-formed" and
matches up properly to the destination?

If creating one append query for one table and then modifying it 299 times
is not an option (say, if you needed to do this repeatedly), you will need
to look into creating a procedure that steps through the tables, grabbing
the tablename, then dynamically modifies a SQL statement handling the append
(INSERT) operation. Designing, developing and testing this procedure may
take you longer than 299 modifications.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Adrienne]

Jeff,

The question of how many tables I'm inserting isn't what I'm concerned
with. I'm trying to track the table they came from in the master table
by having a field in the master table for the filename of the file of
origin.

In other words, if one of my original tables is
"serverXYZ_2007_09_30", I want each record that originally came from
that table to have a field with "serverXYZ_2007_09_30" in that field.
Does that make sense?

I just need some function or expression to auto-insert the filename
into that field when I do an append query.

 

[Jeff Boyce]

Adrienne

Your conceptualization of this as a "just" may be ... insufficient. This
isn't something that falls into the "just" category.

Again, let me ask. Are you doing this one time? (in that case, build a
query and modify it for each table -- you can add in a "calculated" field in
your query into which you will enter the table-name).

Or is this something that you will need to use over and over again. In that
case, re-read my previous response -- you will need to engage in coding.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

In other words, if one of my original tables is
"serverXYZ_2007_09_30", I want each record that originally came from
that table to have a field with "serverXYZ_2007_09_30" in that field.
Does that make sense?

Just to add to Jeff's good advice: if it's a one-shot deal you can just type

TableName: "serverXYZ_2007_09_30"

in a vacant Field cell in the append query.

John W. Vinson [MVP]