Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old September 30th, 2003, 07:12 PM
Authorized User
 
Join Date: Sep 2003
Posts: 52
Thanks: 0
Thanked 0 Times in 0 Posts
Default Insert into error with union query in Access 2002

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
 
Old October 2nd, 2003, 08:48 AM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Sal

Sal
 
Old October 3rd, 2003, 01:07 PM
Authorized User
 
Join Date: Sep 2003
Posts: 52
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old October 3rd, 2003, 04:11 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old October 3rd, 2003, 04:37 PM
Authorized User
 
Join Date: Sep 2003
Posts: 52
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old October 3rd, 2003, 05:20 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old December 21st, 2004, 01:42 AM
Registered User
 
Join Date: Dec 2004
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via ICQ to Nifty
Default

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.

 
Old December 21st, 2004, 07:24 PM
Registered User
 
Join Date: Dec 2004
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via ICQ to Nifty
Default

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)



 
Old December 21st, 2004, 07:47 PM
Registered User
 
Join Date: Dec 2004
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via ICQ to Nifty
Default

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

use:

Code:
insert INTO [tablename]
Select * FROM [savedquery];





Similar Threads
Thread Thread Starter Forum Replies Last Post
Insert Query Error & Run-Time Error 3022 DavidWE Access 1 July 31st, 2008 11:17 AM
Top percent Query acting strangely in access 2002 jeremy1048 Access 4 January 15th, 2008 09:27 AM
Union query in Access?? lisabb Access 5 October 24th, 2007 10:04 PM
Run-time error 7: Out of Memory - Access 2002 alexisb BOOK: Expert One-on-One Access Application Development 4 October 12th, 2004 12:11 PM
Error on Make-Table Query In Union Query rylemer Access 1 August 20th, 2003 07:42 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.