Union query - syntax error in from clause

[Creeker]

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?

 

[Remou]

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

SELECT CoName1, StatusID
FROM [qryMeetingMembersNOTComing-crb]

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

 

[genomon]

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."

 

[Remou]

I tested.

 

[genomon]

I, bested.

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

 

[Creeker]

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?

 

[Remou]

Try:

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

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

(Genomon, )

 

[Creeker]

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?

 

[Remou]

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

 

[Creeker]

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.

 

[lespaul]

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:

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:

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

or

SELECT StatusID, CoName1 FROM ....

Leslie

Have you met Hardy Heron?

 

[Remou]

What happens when you run qryMeetingALLRegistrantMembers_NOreminders?

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

 

[Creeker]

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.

 

[Remou]

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.

 

[Creeker]

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.