Table Name in Query

[dssrun]

Is there a way to get the name of the table in the query. E.g. I am using Table 1 and Table 2 in Query 1. I have a field called Field1 in Query 1 and it's Expression is this

IIF(Table1.Field2=X, Table1.Field1, Table2.Field1)

Now in query 1 I want to create a field to see all the records where Table 1 was used and Table 2.

Thanks.

[TheShabz]

I dont understand the question. Can you provide an example?

[dssrun]

Let me give you a simpler example: At the end of the month I have VB that imports 31 worksheets with the Day number as their name. These worksheets are similar in structure with different data. I have multiple append query with all the data to summarize it, but I want to be able to take the date number on the table and put it in a field where it associates with its data. So in the master table I have 31 days worth of facilities and amounts, and now I need which table each record is coming from.

Table01: Facility| Amount
Table02: Facility| Amount
....

Query01: Facility | Amount | TableName
Table01
Table02
......
Table31

hope this helps

[TheShabz]

google "crosstab query"

[dssrun]

But how do I get the name of the Table in the query field result or am I missing something? I know how to do a crosstab in access

[TheShabz]

Oh. My mistake. I looked at your example as if it was already in a table that contained table names.

I think the issue here isn't THAT you cant get the table names, but WHY you want the table names to begin with. It seems like you have the same fields in all of your table... so why arent they in one table to begin with? Seems like you have normalization issues with your database. If you are treating your tables like spreadsheets, you're going about it all wrong.

Outside of that, I dont know of a way to get the table name pulled via SQL. I doubt it's even possible.

[dssrun]

Well what i am doing is linking data others are entering in 31 excel worksheets. So what I have is an external datasource and all I need from these sheets is the Facility, Amount and date, and the date is the title of the worksheet (which is the Table name in access). SO i can write append queries to get Facility, Amount, but do not know how to get date to the master sheet. At the end of the month the linked excel sheet is cleared but I need to retain that data in an access table so that is why i write the append query to one table.

[TheShabz]

Import them all into a table, adding a field to designate which sheet it came from. Your query will be simple, then.

[dssrun]

That sounds like a good plan, but how do you designate the field to the worksheet name?

[dssrun]

I am guessing in my iteration statement in VB I would have to use the Worksheet.Name property and somehow designate that as a column for each import?

[TheShabz]

On the first import, create a blank column. In your iterations, run an update query on that field to fill in the name. so it would be

strSQL = "UPDATE tablename SET fldWorksheet = " & Worksheet.Name & " WHERE fldWorksheet Is Null;"

[dssrun]

I will try this out tomorrow. I guess I was looking for a way to do this in the design builder or through SQL without VBA. But i guess it can't be done.

I really appreciate the help on this subject

[TheShabz]

It doesn't have to be done in VBA. you can manually add a column in each of your excel sheets that holds the name of the sheet. then, on import, they'll all import into the same table. I went with the VBA route because you mentioned you had it already set up.

[dssrun]

No the VB route is preferred over manual work on Excel I thought maybe there was a function or property in Access that can give me the name of the table used that I wasn't aware of. Thanks again