UNION Query with Join



I am creating a UNION query based on 3 other parsing queries,qry1,qry2,qry3.
qry1 is linked to another table (it contains codes, the linked table is
descriptions for the codes) the union works well untill i include the
"description" field from that query - it bombs out prompting me as if i have
spelt the field name incorrectly

Ken Snell \(MVP\)

Post the SQL statement of the UNION query with and without the description
field. The reason Jet/ACCESS asks you for that description value is because
Jet/ACCESS cannot identify it as a field within the query's source tables,
so it's either a typo or an incomplete reference (not including the query or
table in which the field resides) to the field. Or, perhaps the description
field is not in the source qry1 query at all?


I cant understand it?, it is working now but this isn't that database i was
working on, I recreated this test database that is.

Both versions are access 2003.

SELECT Name,Age, OccupationDescription FROM qryA
SELECT Name, Age, OccupationDescription FROM qryB
SELECT Name, Age, OccupationDescription FROM qryC;

(where qryC has a join from OccupationCode to Table4 OccupationCode, and has
Name, Age, OccupationDescription(Table4))

I may re-post this in the future...!???

Douglas J. Steele

Looking at your sample, you're using a reserved word (Name) as a field name.
If this is the case in your actual example, that could be an explanation of
the error.

For a comprehensive list of names you should avoid, check what Allen Browne
has at http://www.allenbrowne.com/AppIssueBadWord.html

If you cannot (or will not) rename the field, at least put square brackets
around the name, in an attempt to avoid problems:

SELECT [Name],Age, OccupationDescription FROM qryA
SELECT [Name], Age, OccupationDescription FROM qryB
SELECT [Name], Age, OccupationDescription FROM qryC;

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Union Query if Yex/No 13
run a series of queries 1
querie based on fields from table, please help 5
Join on a UNION query 2
Union Query 7
Forcing an ambiguous outer join 5
SUM in a UNION query 2