Make a Field the Name of the Table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am querying and appending data from multiple tables with the same number of
fields. I did this of course, without identifying the name of the different
tables. How do I create a field in my query that contains the name of the
table I'm running the query on? I know this is simple, been doing too much
Exel lately. Thanks.

Kou
 
Not sure how you want it to look, but if you had a table named Table1, a
calculated field such as:
TableName: "tblTable1"
would place Table1 in every row for that query.
 
It appears obvious, that there must be some way to access the field list
properties of the table, and use the alias, which has the table name as the
alias? Can something this simple, not be possible in Access? A field that
equals the table name. Wow...maybe not?
 
I guess I'm not clear on what you want or what you are doing now to get what
you are getting.
 
I am querying and appending data from multiple tables with the same number of
fields. I did this of course, without identifying the name of the different
tables. How do I create a field in my query that contains the name of the
table I'm running the query on? I know this is simple, been doing too much
Exel lately. Thanks.

Kou
Click to expand...

rpurosky has the correct answer - just use a calculated field in the
Query with "Table1". There's no automated way to do so in Access,
because this situation will only arise if you're drastically violating
all the principles of data normalization! You should not HAVE multiple
identical tables differing only in their table name, period.

Why not create one master table, with an additional field for what's
now your tablename, and Append all of these into this single table?
You can index the field, and create a parameter query to extract just
the Table1 (or Table318) data - and you'll need only *ONE* query where
you now have many.

John W. Vinson[MVP]
 
Dear John,
(Finally get to write a "Dear John" letter.)

Respectfully, I think that what the OP is doing is taking data from multiple
sources (tables, linked spreadsheets, text files) and putting them all into
one table in an attempt to normalize the data.

I don't have a solution other than building the SQL statement in VBA and
executing that. Something like the UNTESTED AIRCODE below

Sub ImportData(strTableName as String)
Dim strSQL as String
Dim dbAny as DAO.Database
On Error goto Error_EXIT
Set dbAny = DBEngine(0)(0)
strSQL = "INSERT INTO Interventions ( InterventionID, ProjectCode,
SourceTable )" & _
" SELECT Methods.MethodID, Methods.Method, "" &
strTableName & """" & _
" FROM " & StrTableName

dbAny.Execute strSQL, dbFailOnError

Exit Sub
Error_EXIT:
Msgbox Err.number & ": " & Err.Description
End Sub

Sincerely,
John Spencer
 
I only wish it was that easy John. As you know, there are many times when
someone comes along and has to pick off where someone else was. Usually the
person infront didn't know what they were doing. So here I am, to come to
the rescue and clean up what was done before me. The tables I have all have
identical number of fields. The problem? They contain thousands of line of
data, as they are tables that hold hourly data, for many number of years.
These tables are so large, they are kept on an Oracle DB. The index field
should have been put in the Oracle Db in the first place, that way I wouldn't
have to do it in Access when pulling out the data. But I will do it the old
fashion way. Just figured there was another way of querying it
automatically. Guess not...that's for sure...Thanks guys!
 
Respectfully, I think that what the OP is doing is taking data from multiple
sources (tables, linked spreadsheets, text files) and putting them all into
one table in an attempt to normalize the data.
Click to expand...

Thanks John - and my apologies, Kou!

John W. Vinson[MVP]
 
Back
Top