Adding Table Name as a Field

[DrSmyth]

Hi,

Just made my first Union query, and it sems to work OK, but I have a problem, I need to use the name of the table as a field. My current SQL is as follows:

SELECT [Diana Kent].*,"Diana Kent" AS [PSM Name],[Diana Kent]![Beneficiary Name]+[Diana Kent]![Estate Name (If Applicable)] AS [RefID]
FROM [Diana Kent]
WHERE ((([Diana Kent].[Beneficiary Name]) Is Not Null))
UNION SELECT [Nick Whewell].*,"Nick Whewell" AS [PSM Name],[Nick Whewell]![Beneficiary Name]+[Nick Whewell]![Estate Name (If Applicable)] AS [RefID]
FROM [Nick Whewell]
WHERE ((([Nick Whewell].[Beneficiary Name]) Is Not Null))
UNION SELECT [Nigel Finch].*,"Nigel Finch" AS [PSM Name],[Nigel Finch]![Beneficiary Name]+[Nigel Finch]![Estate Name (If Applicable)] AS [RefID]
FROM [Nigel Finch]
WHERE ((([Nigel Finch].[Beneficiary Name]) Is Not Null));

As you can see, I've specifically stated what the field [PSM Name] should be. I would like to do this by referring to the table name instead, as I think the code would be much neater and as I'm adding more tables to the union it could save me time in the long run...

Thanks

 

[r937]

why are you adding more tables to the union?

is it because each person is in a separate table?

this suggests a problem with the design



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[DrSmyth]

Each person is in a seperate table. Basically we have a bunch of field agents that have there own spreadsheets, each month they send the spreadsheet back to me. The table is a linked table that just relates to each of their spreadsheets.

There will be about twenty more agents (and therefore twenty more tables), so there's not really any other way i can put all the results in a single table (unless you got another idea??)

Dr

 

[r937]

when you import the spreadsheets, can't you import them into one table?

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[DrSmyth]

I don't import them, I've got someone who detaches them into a folder. The Access tables are then linked to this folder, so updates are achieved simply by detaching a file.

This seems like an easier option than running an import of all the tables every month... As this way, once i have set up the queries everyting will be automatic...

 

[r937]

well, sounds like you'll have to bite the bullet and either write some module/macro code (can't help you there) or hardcode the table names in your 30-table union

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)