Union Query and Field Alias
in [Queries]
|
Prev: Queries
Next: Average time with a single entry
From: Krzysztof Naworyta on 2 Jun 2010 07:53 There's no difference between SELECT "Before10" as TimeOfDay, * FROM ... and SELECT *, "Before10" as TimeOfDay FROM ... -- KN SSi308 wrote: | 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]? || || .
From: John Spencer on 2 Jun 2010 08:40 In the report use the control's format property. (Simplest solution in my mind) You can format the calculated field using the Format function in the query HOWEVER you would need to list all the fields (instead of using the *) in the query to do so. Or you could build another query that uses the union query as its source and apply formatting there. Or in your various queries you could multiply the results by 100 and round to the needed degree. Or in your various queries you could use the format function to format the results instead of using the format property. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County SSi308 wrote: > 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 10:14
Strange, I will need to try it again for my own curiosity. I tried this syntax and did not have success, but it could be that there was some other typo. Thanks. Lori "Krzysztof Naworyta" wrote: > There's no difference between > SELECT "Before10" as TimeOfDay, * FROM ... > and > SELECT *, "Before10" as TimeOfDay FROM ... > > -- > KN > > > SSi308 wrote: > | 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]? > || > || . > > . > |