Query to return table name as a value?

[crazy_loud]

Hello all,

Is there a SQL function in Access that will allow it to return (as a field value) the name of the table where a particular record was found?

The reason that I want to do this is because I have a single table (Table A) with building address records counted and broken down by state and by month - but some of the records belong to smaller groups (e.g. Zone 1, Zone 2, etc.) that aren't states, but that I want to count in the crosstab as if they were. There are building addresses on Table A that belong to each of these four groups, so I need to somehow match the records on Table A with the appropriate Zone, and place the name of that Zone in the "State" column of the query results instead of the actual state so that I can then count the results in the same crosstab.

I had the intention of resolving this by creating three tables (with the listings of address information for each Zone #), then matching records in Table A to each Zone table in a query by the address field to get each record's appropriate Zone no.(if available), then using the results of this query in my crosstab - but I can't figure out how to get Access to create a calculated field that lists from which table a resulting query record came from, so I can't continue.

Any suggestions, if able, would be appreciated!

 

[gemma-the-husky]

rather than have three separate tables, why not just have one table with some indicator/dropdown etc to indicate the zone or state etc - that would be the normal way to do it

 

[The_Doc_Man]

I'm with Gemma on this one. Sounds like the table structure is in your way rather than helping you. This is not good.

 

[crazy_loud]

Thanks gemma,

I couldn't put them into one table because there is no field on any of these imports (they are being taken from Excel spreadsheet lists) that says which zone the records belong to.

The names of the worksheets in Excel are the names of the zones, each contains the exact same fieldnames of records - but with the addresses that happen to belong to that particular group... so if I import them all to one table I no longer have anyway to identify them. And the people using this database won't want to alter the data being imported at all. Just get in into Access and report off of it.

 

[gemma-the-husky]

if you only need the tables once, thats fine, but if you need to refer to them often, import the table originally, then copy it from the import table to the master database - at that point you could add a source description to identify the zone

it really would make it easier - at the moment you are probably having to design everything 3 times - what if you get a fourth import file for instance

--------
having said all that, for your purposes, you can do a union query to unite the 3 zone tables

take any three similar queries, and add a column identifying the tablesource! look at them in sql view - you have to do this with sql, rather than design -
paste all the sql descriptions together with the addition of the word union

you will get

select blah blah blash table 1

UNION

select blah blah blash table 2

UNION

select blah blah blash table 3

-------------
this gives you a single (non-updateable) query which may help

 

[crazy_loud]

Thanks gemma, that does simplify things alot. Lesson learned. I was tired of changing three tables all the time whenever I was trying out stuff!

But assuming that I'm copying these 3 zone tables to the master zone table to make things easier, I still don't know what expression to use in the "source description" column to identify the tablesource - which brings me back to where I was before.

Is there a "= srctblname" type deal in SQL somewhere, maybe?