Union query drawing from another query

[Guest]

I can't get Union Queries based on other queries to work.

I'm working with Census Bureau (CB) data about population race, sex, and
age. Census Bureau tables you can download are VERY unnormalized. Ex - one
table has a record for each geographic unit (lets say "Census Blocks") with
multiple columns for each race-sex-age combination. So - a row is (say) for
Block 1, and there are hundreds of columns (fields) - such as "white-male-5
yrs old", "black-
female-71 yrs old", "AmerInd-female-34 yrs old" - etc.

I want to build a normalized table, but with age groups that are not
reported by the CB. Fields would be:

*Census Year
*LOGRECNO (an autoincrement primary key used by CB to tie a table of
geographic definitions to multiple data tables)
*STFID (combination key of Codes for State-Census Tract-Census Block -
defines a unique geographic unit - but different levels are mixed into each
table. Ex-"state-level" data is mixed in with thousands of "Census
Block-level" data.)
*SUMLEV (A different CB Code for the geographic unit level - ex. "101" is
for "Census Blocks". Lets you separate State level from Block level, etc.)
*RaceCode
*SexCode
*AgeCode, (ranges I create such as "A = 1-4", "B = 5-9", etc.)
*Value (Number of People)

I first created several queries that created the age groups. I have one
query for each race. They work fine. They join 2 tables - one that has the
geographic unit description (info about the Blocks), the other has the
sex-age columns. Each query produces rows for each unique STFID, and the
columns are the different age-sex combinations. I have formulas in columns
that sum the number of people in each range I want (21-34 Males, 35-49
Females, etc.).

HERE'S THE PROBLEM

I now want to have a Union Query create the simple normalized table from
these race-age/sex tables. I've worked it and worked it - but I get "the
SELECT statement includes a reserved word or an argument name that is
misspelled or missing, or the punctuation is incorrect". Here's the Union SQL
for Whites (drawing for the query "QX_WhiteAgeSex" (I'd have another for each
race - and they would all wind up in the one normalized table.)


SELECT "2000" AS CenYr, LOGRECNO, STFID, SUMLEV, "A" AS RaceCode, "T" AS
SexCode, "T" AS AgeCode, [P012A001] AS Value FROM QZ_WhiteSexAge;
UNION ALLSELECT "2000" AS CenYr, LOGRECNO, STFID, SUMLEV, "A" AS RaceCode,
"M " AS SexCode, "T" AS AgeCode, [P012A002] AS Value FROM QZ_WhiteSexAge;
UNION ALLSELECT "2000" AS CenYr, LOGRECNO, STFID, SUMLEV, "A" AS RaceCode,
"M " AS SexCode, "A" AS AgeCode, [P012A003] AS Value FROM QZ_WhiteSexAge;
UNION ALLSELECT "2000" AS CenYr, LOGRECNO, STFID, SUMLEV, "A" AS RaceCode,
"M " AS SexCode, "B" AS AgeCode, [P012A004] AS Value FROM QZ_WhiteSexAge;
(etc - one line for each race/age range/sex record)

(NOTE: "A" is the Census Bureau code for "White" - not my code : ) )

Anyone see what's wrong?

Could it be that a Union query has to "look at" tables - not queries
(doesn't seem likely)?

TIA

John D

 

[Douglas J. Steele]

Try using a different name than Value: that's a reserved word.

For a good discussion on names to avoid, see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I can't get Union Queries based on other queries to work.

I'm working with Census Bureau (CB) data about population race, sex, and
age. Census Bureau tables you can download are VERY unnormalized. Ex - one
table has a record for each geographic unit (lets say "Census Blocks")
with
multiple columns for each race-sex-age combination. So - a row is (say)
for
Block 1, and there are hundreds of columns (fields) - such as
"white-male-5
yrs old", "black-
female-71 yrs old", "AmerInd-female-34 yrs old" - etc.

I want to build a normalized table, but with age groups that are not
reported by the CB. Fields would be:

*Census Year
*LOGRECNO (an autoincrement primary key used by CB to tie a table of
geographic definitions to multiple data tables)
*STFID (combination key of Codes for State-Census Tract-Census Block -
defines a unique geographic unit - but different levels are mixed into
each
table. Ex-"state-level" data is mixed in with thousands of "Census
Block-level" data.)
*SUMLEV (A different CB Code for the geographic unit level - ex. "101" is
for "Census Blocks". Lets you separate State level from Block level, etc.)
*RaceCode
*SexCode
*AgeCode, (ranges I create such as "A = 1-4", "B = 5-9", etc.)
*Value (Number of People)

I first created several queries that created the age groups. I have one
query for each race. They work fine. They join 2 tables - one that has the
geographic unit description (info about the Blocks), the other has the
sex-age columns. Each query produces rows for each unique STFID, and the
columns are the different age-sex combinations. I have formulas in columns
that sum the number of people in each range I want (21-34 Males, 35-49
Females, etc.).

HERE'S THE PROBLEM

I now want to have a Union Query create the simple normalized table from
these race-age/sex tables. I've worked it and worked it - but I get "the
SELECT statement includes a reserved word or an argument name that is
misspelled or missing, or the punctuation is incorrect". Here's the Union
SQL
for Whites (drawing for the query "QX_WhiteAgeSex" (I'd have another for
each
race - and they would all wind up in the one normalized table.)


SELECT "2000" AS CenYr, LOGRECNO, STFID, SUMLEV, "A" AS RaceCode, "T" AS
SexCode, "T" AS AgeCode, [P012A001] AS Value FROM QZ_WhiteSexAge;
UNION ALLSELECT "2000" AS CenYr, LOGRECNO, STFID, SUMLEV, "A" AS
RaceCode,
"M " AS SexCode, "T" AS AgeCode, [P012A002] AS Value FROM QZ_WhiteSexAge;
UNION ALLSELECT "2000" AS CenYr, LOGRECNO, STFID, SUMLEV, "A" AS
RaceCode,
"M " AS SexCode, "A" AS AgeCode, [P012A003] AS Value FROM QZ_WhiteSexAge;
UNION ALLSELECT "2000" AS CenYr, LOGRECNO, STFID, SUMLEV, "A" AS
RaceCode,
"M " AS SexCode, "B" AS AgeCode, [P012A004] AS Value FROM QZ_WhiteSexAge;
(etc - one line for each race/age range/sex record)

(NOTE: "A" is the Census Bureau code for "White" - not my code : ) )

Anyone see what's wrong?

Could it be that a Union query has to "look at" tables - not queries
(doesn't seem likely)?

TIA

John D

 

[Guest]

Try using a different name than Value: that's a reserved word.

For a good discussion on names to avoid, see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html

--

Oh Doug - thank you thank you thank you .... did I say - THANK YOU.

Hmm? Why couldn't the error message have isolated the reserved word and told
me specifically what the problem was? Doesn't seem that hard. If Access trips
over a reserved word - then it has to know what word it tripped on, doesn't
it?

 

[Douglas J. Steele]

Oh Doug - thank you thank you thank you .... did I say - THANK YOU.

Hmm? Why couldn't the error message have isolated the reserved word and
told
me specifically what the problem was? Doesn't seem that hard. If Access
trips
over a reserved word - then it has to know what word it tripped on,
doesn't
it?

Not necessarily. For example, if you used a reserved SQL word in your query,
Jet wouldn't complain about that word per se. It would complain that the
entire SQL statement didn't make sense, but it wouldn't know why.