UNION Query with Join

[Jays]

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?

 

[Jays]

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
Union
SELECT Name, Age, OccupationDescription FROM qryB
Union
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
Union
SELECT [Name], Age, OccupationDescription FROM qryB
Union
SELECT [Name], Age, OccupationDescription FROM qryC;