Select Table name as a Calculated field

[crabback]

Hi I have a union query that pulls data from two tables, I want to also create a calculated field that will insert the name of the table each record is coming from. I tried using...

SELECT DISTINCT MSysObjects.Name
FROM MSysObjects

When I ran this as a standalone query it returns one column of values, so I put it in my sql which is:

SELECT SpecialAcceptances.CedantBalloonID, Clients.CedantName,(SELECT DISTINCT MSysObjects.Name
FROM MSysObjects)
FROM Clients INNER JOIN SpecialAcceptances ON Clients.BalloonID = SpecialAcceptances.CedantBalloonID;
UNION SELECT Faculative.CedantBalloonID, Clients.CedantName, (SELECT DISTINCT MSysObjects.Name
FROM MSysObjects)
FROM Clients INNER JOIN Faculative ON Clients.BalloonID = Faculative.CedantBalloonID;

When I try to run the query I get an error: at most one column can be returned by this subQuery. Ok so I just realised that is because I have two tables in the select statement... which means my subquery would return 2 vals per record. How do I get it to give me the table name that contains a field 'CedantBalloonID'????

I can't find any links for help on this... but possibly I'm phrasing it all wrong. I really appreciate anyones help on this!!

Crabback

 

[dhookom]

Try

SELECT "SpecialAcceptances" as TableName, SpecialAcceptances.CedantBalloonID,
Clients.CedantName
FROM Clients INNER JOIN SpecialAcceptances ON Clients.BalloonID = SpecialAcceptances.CedantBalloonID;
UNION SELECT "Faculative", Faculative.CedantBalloonID, Clients.CedantName
FROM Clients INNER JOIN Faculative ON Clients.BalloonID = Faculative.CedantBalloonID;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[crabback]

Thanks again Duane!

Crabback