Union Query - Please Help! (1 Viewer)

[PKJ]

Hi Everyone,
The query below shows syntax error in FROM clause in Access-2007 -

SELECT * FROM q_StationWiseTR_Final
UNION
SELECT * FROM q_StnPosted_NotFilled_TR-Form
ORDER BY SeniorityLevel, StnName, IfWantTransfer DESC , Remark;

-- Both the SELECT * has common/same fields. Access does not error while saving the query but shows syntax error while run/datasheet view.

would somebody help please!!

regards - PKJ

 

[vbaInet]

When you run the query does it show the results?

Are the fields in the same order in your tables?

 

[PKJ]

Thanks vbaInet!

You have asked -

-When you run the query does it show the results?
No, error message appears in the design view window.


Are the fields in the same order in your tables?
Yes, the fields have same name and same order.

regards - PKJ

 

[pbaldy]

The inadvisable symbol in the second table/query name will require it to be bracketed.

SELECT * FROM [q_StnPosted_NotFilled_TR-Form]

 

[vbaInet]

Welcome to AWF by the way.

Start by writing the names of the fields insrtead of asterisk (*) and take out your ORDER BY line.

 

[PKJ]

Many Many Thanks!!

Lots of thanks to pbaldy - your advice has worked.
Thanks to vbaInet for advice.
regards - PKJ

 

[vbaInet]

The inadvisable symbol in the second table/query name will require it to be bracketed.

SELECT * FROM [q_StnPosted_NotFilled_TR-Form]

Paul, was it the hyphen?

 

[pbaldy]

Paul, was it the hyphen?

Yes; the underscores are fine, but the hyphen is not.

PKJ: no problem, glad it worked for you. Welcome to the site!

 

[vbaInet]

Yes; the underscores are fine, but the hyphen is not.

Thanks. It's interesting that it's lets you save a name with such a character but doesn't like it when you're trying to call the query.

 

[pbaldy]

True, but that's no different than a space. Unfortunately, Access lets you do a number of things that aren't really a good idea.

 

[vbaInet]

True, but that's no different than a space. Unfortunately, Access lets you do a number of things that aren't really a good idea.

That's right. It should at least prompt. I've just never used a hyphen when naming an object before so I've not encountered that problem. As with spaces I know to enclose them in square brackets.

I don't know if the hyphen issue is talked about in the help files. Not come across it.

 

[PKJ]

pbaldy & vbaInet,
Thanks Friends for the high quality help you are providing on this forum. Though I am not an expert but I would enjoy learning on this forum.

My regards to both of you - PKJ (Jaipur, India)

 

[vbaInet]

I'm glad you gained something out of this. All thanks to Paul.