Full Version: Problems W VBA Syntax For Insert Into And Union Queries
UtterAccess Forums > Microsoft® Access > Access Queries
davidinsc
I'm an Access newbie who is trying to learn a few new tricks by expanding into VBA Programming. I've come across something that I think is pretty straightforward, but have been unable to fix.

I've created a submodule with a SQL Query that attempts to INSERT the records returned by a UNION ALL query into an empty table in my database.

I receive a "Run-time error '3075': Syntax error (missing operator) in query expression '[tblEligible_tmp].[ELIGIBLE] UNION ALL SELECT [tblEngagedCards_tmp].[DEPARTMENT]'.

I've copied the processed SQL code from the VBA Editor's Immediate window into Access 2013's SQL Design View to test the SQL language.

I get a similar error: "Syntax error (missing operator) in query express '[tblEligible_tmp].[ELIGIBLE] UNION ALL SELECT [tblEngagedCards_tmp].[DEPARTMENT]'.

When I simply remove the INSERT INTO line, the UNION query works just fine.

Here's the code, as entered in Design View SQL:

INSERT INTO
tblEligibleEngaged_tmp
SELECT
[tblEligible_tmp].[DEPARTMENT],
[tblEligible_tmp].[ELIGIBLE],
Count([tblEngagedCards_tmp].[CountOfCard ID]) AS ENGAGED
FROM
[tblEligible_tmp] LEFT JOIN [tblEngagedCards_tmp] ON [tblEligible_tmp].[DEPARTMENT] = [tblEngagedCards_tmp].[DEPARTMENT]
GROUP BY [tblEligible_tmp].[DEPARTMENT], [tblEligible_tmp].[ELIGIBLE]
UNION ALL
SELECT
[tblEngagedCards_tmp].[DEPARTMENT],
"0" AS ELIGIBLE,
Count([tblEngagedCards_tmp].EMP_ID) AS ENGAGED
FROM
[tblEngagedCards_tmp]
GROUP BY [tblEngagedCards_tmp].DEPARTMENT, "0" HAVING ((([tblEngagedCards_tmp].DEPARTMENT)="OTHER"));


Any suggestions?
projecttoday
INSERT doesn't support UNION queries, easily remedied by doing 2 separate queries.
davidinsc
Thanks for the quick response Robert.

Problem solved!

I spent way to much time trying to chase this down in Microsoft's support resources and nowhere spotted anything that would indicate you couldn't use a Union query with an Insert statement.

Even now, I can't find a clear answer when I search for "Can I use a UNION query with INSERT statement in Microsoft Access" .

Am I looking in the wrong place or is this just a case of "learning on the job"?
projecttoday
Maybe I shouldn't have put it that way because I don't really know that for a fact and I'm sorry for that. But I've never seen it done that way and I've been working with Access for 13 years. I know that the SELECT clause of an INSERT (as well as the INNER JOIN clause of an UPDATE) just don't allow the full range of options that you might happen to want.
theDBguy
Hi david/robert,

Welcome to UtterAccess!

Perhaps something like this might work...

INSERT INTO TableName
SELECT *
FROM (SELECT...
FROM...
LEFT JOIN...
ON...
GROUP BY...
UNION
SELECT ...
FROM...
GROUP BY...
HAVING...)

(untested)
Just a thought...
projecttoday
But does it work?

What about just saving the union query and using the saved query? Does INSERT support saved queries? (I don't think so.)

Two separate inserts should work fine.
theDBguy
>>Does INSERT support saved queries? (I don't think so.)<<

Why not? I'm thinking it does... For example, are you talking about something like this?

INSERT INTO TableName (FieldName) SELECT FieldName FROM QueryName
projecttoday
Yes.
theDBguy
Okay. I haven't tested the above, but I am thinking it should work. Do you know if it wouldn't? If not, do you know why?

PS. Okay, I just did a quick test using the following query, and it worked.

INSERT INTO Table2 ( f1, f2 )
SELECT Query1.f1, Query1.f2
FROM Query1;

PPS. Here's the SQL for Query1

SELECT Table1.f1, Table1.f2
FROM Table1;
projecttoday
I thought maybe I had tried it once and it didn't but I guess I was wrong.

Thanks for your clarification.

So davidinsc has another option if he wants.
theDBguy
Hi Robert,

It was good exercise. Cheers!
davidinsc
Thanks Robert and DBGuy for the quick response.

I decided to go with two separate queries so as to get the project moving again. That approach is working well.

DBGuy, I did attempt to enclose a portion of the query in parentheses as you had suggested, however I ended up with (different) error messages using that approach as well. I did not take the time to troubleshoot those messages as Robert's suggestion worked.

Thanks again for both of your answers, and the speed in which you delivered them.

I'll have more posts (questions smile.gif ) soon.
projecttoday
thumbup.gif
theDBguy
Hi, Glad to hear you got it to work. My suggestion was more than merely enclosing some parts in parenthesis. It's actually a completely different syntax. But that doesn't matter now. Good luck with your project.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.