Add table name to column then combine all tables

[sw1085]

Hi

I would like to be able to combine several tables which all share the same fields. The only way to ascertain which data relates to which company is by looking at the table name as the last three characters of the table name tell you which company it is

dbo_SALFLDGASL would be for company ASL
dbo_SALFLDGASL would be for company CPL

I want to combine the two companies entirely in a SQL query but don't know how to add a column in the query that stores the last three characters of the table name.

Can anyone help? I understand it can be done in VBA.

Thanks very much.

SW

 

[sw1085]

Thanks for your reply Pat

It needs to be automated with a refresh every 60 seconds

Any ideas on what code would accomplish what I am trying to do?

Currently trawling the internet but nothing found as yet.

Thanks alot for your help.

If this is something you need to automate you'll need to create a function. If it is something you're going to run only once to convert the data, just add a calculated field to the append query.

Select ..., "tbl1", as SourceTable, ...