I am using a union query in a effort to combine groups of people.
I'll use a set example to clarify...

group A consists of person 1,2 and 3

group B consists of person 4,5 and 6

if I want to add person 2 to group B then then both person 1 and person 3 must be added to group B and all of the old group B people should be added to group A.

The end result should be a group of containing persons 1 - 6

This can be easily done with a union query and works perfectly in Access 2002.

As soon as I add the INSERT INTO [table] statement into this union query Access sends the message:

Syntax Error (missing operator) in query expression
'customerID = 10
UNION
SELECT billWith ,7 as customerID
FROM BillWith
WHERE customerID = 10'.

[u]THE ORIGINAL SQL</u>
INSERT INTO BillWith
SELECT 7 as customerID, billWith
FROM BillWith
WHERE customerID = 10
UNION
SELECT billWith ,7 as customerID
FROM BillWith
WHERE customerID = 10;

It works fine without the first line. The first line has also been taken the other form
INSERT INTO BillWith (customerID, billWith)

Thanks in advance for any clarification you can add to this problem:)

Roni Estein
Roni.Estein@e-drugscanada.com
https://www.e-drugsCanada.com

__________________
Roni Estein
Roni.Estein@e-drugscanada.com
https://www.e-drugsCanada.com

What exactly is the 7 used for?
You are referencing the wrong field name.
Please provide both table structures.

Sal

Sal

Hi Sal,

The numbers 10 and 7 were the hard coded examples of the specific info I was working with.

Order 10 at the time had been grouped with 9 and 8 (Manually) and I was trying to group them with order 7 which at the time had no grouped partners. What I would normally happen is that order 10 would group with order 7 and nothing else.

I would need all of 7's grouped partners to be included, and also all of order 10's grouped partners to now also group with 7.

Sounds confusing…

The SQL I was using was broken up into two parts. (I'm no master of SQL yet). First I would group 10 and 7 to each other with the following statements.

In this example instead of 7 and 10 the current customer is mapped to customer ID.value and the person in a group being added is referenced as partner.
DoCmd.RunSQL "INSERT INTO BillWith (customerID, billWith)
                               values (" & customerID.Value & ", " & partner & ")"
DoCmd.RunSQL "INSERT INTO BillWith (customerID, billWith)
                               values (" & partner & ", " & customerID.Value & ")"

The INSERT INTO UNION query should go here to insert all combinations not already in the group

This would add the relationship and the inverse to the BillWith Table. Then using the union query, I would get all the active groups involved to join with each other as well. This would allow any person in the group to make an order in the future for the group and not depend on one contact person.

If I could do this all in one statement it would be interesting, or even if I could get the insert into union query working it would be very helpful.

The structures are:

BillWith is a table with no keys that acts as an M to N relationship.
long customerID
long billWith

Customer is our customer table
long customerID primary key Access autonumber field used.
and a ton of other fields which are irrelevant to this problem.

Basically I am trying to get a two column by n row subset of data and append that to the BillWith Table.

Thanks
-Roni


Roni Estein
Roni.Estein@e-drugscanada.com
https://www.e-drugsCanada.com

I do notice that you do not have a field name named 7. Remove the 7 and leave only the actual fiend names.
By doing "7 as customerID" you are telling the database to select the field by the name of "7" and name it "customerID" for this query.
Remove the "7" and leave only "SELECT customerID"
I guess somewhere you are trying to insert where customerid = to 7 and customer id = 10.Is that where the 7 and 10 come from?




Sal

Hi Sal,

Yes, that is the case. I was trying to hard code it as it were, I could use a parameter query I suppose instead but I seem to get the same error. Can you not hard code a data entry in SQL?

Roni Estein
Roni.Estein@e-drugscanada.com
https://www.e-drugsCanada.com

You may be able to use a reference to a form within your union query. I have nover tried it, but technically it should work.


Sal

I've been having almost exactly the same error...
but my query is much more involved than this roniestein's,

it works as a select (without the insert into), and it works as an insert into if I don't use unions, but as soon as I incorporate unions, it dies (missing operators error).

eek ...

I don't know if I can simplify the query to post it, but i'll see what I can do.

ok, i've got a solution that works (for me) thanks for your ideas

through almost a days research online i have come up with very few solutions ... none of which worked for MS Access.

however, the solutions given gave me ideas for other ways in which I could get around it.

I basically saved my select query (with multiple unions) and then

select * into [newtable]
from [savedquery]

works perfectly (including deleting info from [newtable] before it inserts, saves adding that to the code i wanted)

alternatively, if you don't want it to delete the table before hand.

use: