A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Union Query and Field Alias



 
 
Thread Tools Display Modes
  #1  
Old June 1st, 2010, 09:30 PM posted to microsoft.public.access.queries
SSi308
external usenet poster
 
Posts: 42
Default Union Query and Field Alias

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
  #2  
Old June 1st, 2010, 09:45 PM posted to microsoft.public.access.queries
Krzysztof Naworyta
external usenet poster
 
Posts: 80
Default Union Query and Field Alias

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 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]?

  #3  
Old June 1st, 2010, 10:02 PM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Union Query and Field Alias

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

  #4  
Old June 2nd, 2010, 12:22 PM posted to microsoft.public.access.queries
SSi308
external usenet poster
 
Posts: 42
Default Union Query and Field Alias

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

  #5  
Old June 2nd, 2010, 12:25 PM posted to microsoft.public.access.queries
SSi308
external usenet poster
 
Posts: 42
Default Union Query and Field Alias

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 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]?

.

  #6  
Old June 2nd, 2010, 12:53 PM posted to microsoft.public.access.queries
Krzysztof Naworyta
external usenet poster
 
Posts: 80
Default Union Query and Field Alias

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 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]?
||
|| .

  #7  
Old June 2nd, 2010, 01:40 PM posted to microsoft.public.access.queries
John Spencer
external usenet poster
 
Posts: 7,815
Default Union Query and Field Alias

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

  #8  
Old June 2nd, 2010, 03:14 PM posted to microsoft.public.access.queries
SSi308
external usenet poster
 
Posts: 42
Default Union Query and Field Alias

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 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]?
||
|| .

.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:15 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.