×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Union query - syntax error in from clause

Union query - syntax error in from clause

Union query - syntax error in from clause

(OP)
I am trying to create a simple union query and I keep getting a syntax error in the from clause.  This is my SQL statement:

SELECT CoName1, StatusID
FROM qryMeetingMembersNOTComing-crb

UNION SELECT CoName1, StatusID
FROM qryMeetingALLRegistrantMembers-NOreminders;

Can anyone tell me what I am doing wrong?

RE: Union query - syntax error in from clause

I guess Access has a problem with the query names. Try:

SELECT CoName1, StatusID
FROM [qryMeetingMembersNOTComing-crb]

UNION SELECT CoName1, StatusID
FROM [qryMeetingALLRegistrantMembers-NOreminders];

RE: Union query - syntax error in from clause

What does the error message say?
Are you absolutely certain that the table names in your SQL are spelled exactly as they appear on the database?

"Don't be irreplaceable. If you can't be replaced, you can't be promoted."
 

RE: Union query - syntax error in from clause

I tested.

RE: Union query - syntax error in from clause

I, bested.

"Don't be irreplaceable. If you can't be replaced, you can't be promoted."
 

RE: Union query - syntax error in from clause

(OP)
I do have the correct query names.  I entered your code, Remou, and got the error

"The specified field 'CoName1' could refer to more than one table listed in the FROM clause of you SQL statement."

So I added the query name to the CoName1 field.  Ran the query and it was having trouble with the dash in the query names so I changed that to an underline.

Then I got an Enter Parameter Value box for qryMeetingMembersNOTComing_crb.CoName1.  I actually got a list of company names with those "not coming" being blank.

Any other suggestions?

 

RE: Union query - syntax error in from clause

Try:

SELECT A.CoName1, A.StatusID
FROM [qryMeetingMembersNOTComing-crb] A

UNION SELECT B.CoName1, B.StatusID
FROM [qryMeetingALLRegistrantMembers-NOreminders] B;

(Genomon, :) )

 

RE: Union query - syntax error in from clause

(OP)
Tried that and the error I get is:

The specified field 'B.CoName1' could refer to more than one table listed in the FROM clause of you SQL statement.

I think I know what the problem is.  The query "qryMeetingMembersNOTComing-crb" is a 'find unmatched query' so the CoName1 field is in the query twice.  How do I differentiate between them?

 

RE: Union query - syntax error in from clause

Of course, I should have spotted that. :)

Use an alias:

SELECT CoName1 As Co1, CoName1 As Co2 ...

Then use the alias in your union query:

SELECT Co1 FROM ...

RE: Union query - syntax error in from clause

(OP)
Here is what I have now.

SELECT qryMeetingMembersNOTComing_crb.CoName1, StatusID
FROM [qryMeetingMembersNOTComing_crb]

UNION SELECT qryMeetingMembersNOTComing_crb.CoName1, StatusID
FROM [qryMeetingALLRegistrantMembers_NOreminders];

I changed the qryMeetingMembersNotComing_crb to query on the ID field so that eliminated CoName1 being in there twice.  But I'm still getting the Enter Parameter Value box for qryMeetingMembersNOTComing_crb.CoName1.  When I run the query anyhow, I get the full list of company names from that query.  The company name that is missing is from the qryMeetingALLRegistrantMembers_NOreminders query.

RE: Union query - syntax error in from clause

what's the SQL for the two queries?  When you run qryMeetingMembersNOTComing_crb alone it has to have two fields named CoName1 and StatusID in the results; when you run qryMeetingALLRegistrantMembers_NOreminders it has to have two fields named CoName1 and StatusID in the results.  That way when you say:

CODE

SELECT CoName1,StatusID FROM YourQueries
those fields are actually in the result set you are using as the source.

so does qryMeetingMembersNOTComing_crb look like:

CODE

SELECT FieldID As StatusID, NameField As CoName1 FROM ....
or

CODE

SELECT StatusID, CoName1 FROM ....

Leslie

Have you met Hardy Heron?

RE: Union query - syntax error in from clause

What happens when you run qryMeetingALLRegistrantMembers_NOreminders?

If you are having problems, it may be best to post the SQL for the two queries.

RE: Union query - syntax error in from clause

(OP)
Okay, here are the two queries:

Query 1:  qryMeetingMembersNOTComing_crb

SELECT DISTINCTROW qryCompaniesMembers.CoID, qryCompaniesMembers.CoName1, qryMeetingALLRegistrantMembers.StatusID
FROM qryCompaniesMembers LEFT JOIN qryMeetingALLRegistrantMembers ON qryCompaniesMembers.CoID = qryMeetingALLRegistrantMembers.CoID
WHERE (((qryMeetingALLRegistrantMembers.CoID) Is Null))
ORDER BY qryCompaniesMembers.CoName1;

Query 2: qryMeetingALLRegistrantMembers_NOreminders

SELECT DISTINCT tblMeetingALLRegistrants.StatusID, tblMeetingALLRegistrants.CoID, tblMeetingALLRegistrants.CoName1, *
FROM tblMeetingALLRegistrants INNER JOIN qryCompaniesMembersRegions ON tblMeetingALLRegistrants.CoID = qryCompaniesMembersRegions.CoID
WHERE (((tblMeetingALLRegistrants.StatusID)=4));

And the union query:

SELECT  qryMeetingMembersNOTComing_crb.CoName1, qryMeetingMembersNOTComing_crb.StatusID
FROM [qryMeetingMembersNOTComing_crb]

UNION SELECT qryMeetingMembersNOTComing_crb.CoName1, qryMeetingMembersNOTComing_crb.StatusID
FROM [qryMeetingALLRegistrantMembers_NOreminders];

The current error I get is Enter Parameter Value box for qryMeetingMembersNOTComing_crb.CoName1 and then StatusID.  The result is the actual list of company names with a blank company name for the one result from the NO reminders query.

RE: Union query - syntax error in from clause

I fear we need to tunnel further what are  qryCompaniesMembers and qryMeetingALLRegistrantMembers?

I suspect that the whole thing has become more complicated that it should be. It is rarely necessary to have that number of queries to get the required result.

RE: Union query - syntax error in from clause

(OP)
My conclusion also.  I was trying to use existing queries but I'm afraid I have to start from scratch which is not a bad idea.  I don't have the time to commit to it right now but I learned something from this exchange and I really appreciate your help and your patience.  Thank you.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close