HELP! How do I create a table column containing the Table Name

[jiblankman]

I need to merge data from several tables for analysis by a third-party program. The TPP will cause the data to be reordered, amongst other actions. After TPP analysis, the data is brought back into Access for reporting. I need to know which table the data orignated from. Is there some way to add a column in the merged table, or in each table itself before merging, that contains the name of the table.

These are well established databases with >50k records. I would prefer not to modify the existing tables with this information.

I am relatively new to Access and am not a programmer. I have had a good bit of experience writing macros and small programs in VBA but not with Access. Thanks for any help.

 

[pbaldy]

Welcome to the site. One way would be to add a field to an append query as it appended the records into your merged table:

INSERT INTO ...
SELECT ..., "TableName" AS SourceTable
FROM TableName

 

[jiblankman]

Thank you but I do not know where to enter this information.

Is there some way to write a macro that will add a column to a database and fill in the same value into each of the cells in that column? If this can be done, then it should be an easy matter to include that column information in the merged tables.

I forgot to mention that we are using Access 2000, which was probably a major oversight on my part, sorry.

 

[pbaldy]

Sure, but it's probably not necessary. How are you creating the "merged table"?

 

[jiblankman]

I use the following steps to create the merged table:

1. transfer 6 tables from 3 databases (2 tables from each DB).
- each DB uses the same naming scheme, so the imported tables are renamed

2. an append query is run on the tables from each DB (one at a time)

3. the resultant table is exported to an excel file which can be read by the third-party program we use.

4. The TPP removes many of the records, reorders some records and the output from this program is another excel file which will need to be re-imported into access for reporting. Therefore, I need to have some way of linking the re-imported data to the original records that went into making the merged table.

 

[pbaldy]

Well, since step 2 is an append query, why can't you add the data there as I first suggested? At that point you have to know the table name, since it's in the FROM clause, so it could also be used to populate the extra field.

 

[jiblankman]

Thank you for your help. I don't know why this didn't occur to me.