Union Query and Field Alias
in [Queries]
|
Prev: Queries
Next: Average time with a single entry
From: SSi308 on 1 Jun 2010 16:30 I have created a union query and now want to create a field alias to specify which records come from which query. The union query works as such: SELECT * FROM [qryTodBefore10] UNION SELECT * FROM [qryTod10To12] UNION SELECT * FROM [qryTod12To2] UNION SELECT * FROM [qryTodAfter2]; But when I try to add the field alias I get the error: Syntax Error in From clause. The error is not any more specific than that. I have tried so many different statements I thought it may be better to just ask: What is the correct syntax to add a field alias called [TimeOfDay]? Thanks. Lori
From: Krzysztof Naworyta on 1 Jun 2010 16:45 SELECT "Before10" as TimeOfDay, * FROM [qryTodBefore10] UNION ALL SELECT "10To12", * FROM [qryTod10To12] UNION ALL SELECT "12To2", * FROM [qryTod12To2] UNION ALL SELECT "After2", * FROM [qryTodAfter2]; -- KN Juzer SSi308 <SSi308(a)discussions.microsoft.com> napisał | I have created a union query and now want to create a field alias to | specify which records come from which query. | The union query works as such: | SELECT * FROM [qryTodBefore10] | UNION | SELECT * FROM [qryTod10To12] | UNION | SELECT * FROM [qryTod12To2] | UNION | SELECT * FROM [qryTodAfter2]; | | | But when I try to add the field alias I get the error: Syntax Error in | From clause. The error is not any more specific than that. I have tried | so many different statements I thought it may be better to just ask: | What is the correct syntax to add a field alias called [TimeOfDay]?
From: KARL DEWEY on 1 Jun 2010 17:02 Try this -- SELECT *, "TodBefore10" AS qry_Source FROM [qryTodBefore10] UNION SELECT *, "Tod10To12" FROM [qryTod10To12] UNION SELECT *, "Tod12To2" FROM [qryTod12To2] UNION SELECT *, "TodAfter2" FROM [qryTodAfter2]; -- Build a little, test a little. "SSi308" wrote: > I have created a union query and now want to create a field alias to specify > which records come from which query. > The union query works as such: > SELECT * FROM [qryTodBefore10] > UNION > SELECT * FROM [qryTod10To12] > UNION > SELECT * FROM [qryTod12To2] > UNION > SELECT * FROM [qryTodAfter2]; > > > But when I try to add the field alias I get the error: Syntax Error in From > clause. The error is not any more specific than that. I have tried so many > different statements I thought it may be better to just ask: What is the > correct syntax to add a field alias called [TimeOfDay]? > > Thanks. > > Lori
From: SSi308 on 2 Jun 2010 07:22 Thank you that worked. I had the comma after "TodBefore10". One more question if you have time and the answer. The queries that I am using in the union include calculated fields as percents. When running the queries themselves these fields are formatted correctly. When running the union query the fields are shown as a decimal. Since I cannot use design view, how do I format the fields? (Or can this be done when creating a report?) Thanks again. Lori "KARL DEWEY" wrote: > Try this -- > SELECT *, "TodBefore10" AS qry_Source FROM [qryTodBefore10] > UNION > SELECT *, "Tod10To12" FROM [qryTod10To12] > UNION > SELECT *, "Tod12To2" FROM [qryTod12To2] > UNION > SELECT *, "TodAfter2" FROM [qryTodAfter2]; > > -- > Build a little, test a little. > > > "SSi308" wrote: > > > I have created a union query and now want to create a field alias to specify > > which records come from which query. > > The union query works as such: > > SELECT * FROM [qryTodBefore10] > > UNION > > SELECT * FROM [qryTod10To12] > > UNION > > SELECT * FROM [qryTod12To2] > > UNION > > SELECT * FROM [qryTodAfter2]; > > > > > > But when I try to add the field alias I get the error: Syntax Error in From > > clause. The error is not any more specific than that. I have tried so many > > different statements I thought it may be better to just ask: What is the > > correct syntax to add a field alias called [TimeOfDay]? > > > > Thanks. > > > > Lori
From: SSi308 on 2 Jun 2010 07:25
Thank you for the reply, however, I did try this and it did not work. Refer to Karl Dewey's answer. The comma has to go after the *. Lori "Krzysztof Naworyta" wrote: > SELECT "Before10" as TimeOfDay, * FROM [qryTodBefore10] > UNION ALL > SELECT "10To12", * FROM [qryTod10To12] > UNION ALL > SELECT "12To2", * FROM [qryTod12To2] > UNION ALL > SELECT "After2", * FROM [qryTodAfter2]; > > -- > KN > > > Juzer SSi308 <SSi308(a)discussions.microsoft.com> napisał > | I have created a union query and now want to create a field alias to > | specify which records come from which query. > | The union query works as such: > | SELECT * FROM [qryTodBefore10] > | UNION > | SELECT * FROM [qryTod10To12] > | UNION > | SELECT * FROM [qryTod12To2] > | UNION > | SELECT * FROM [qryTodAfter2]; > | > | > | But when I try to add the field alias I get the error: Syntax Error in > | From clause. The error is not any more specific than that. I have tried > | so many different statements I thought it may be better to just ask: > | What is the correct syntax to add a field alias called [TimeOfDay]? > > . > |