Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old September 30th, 2003, 07:12 PM
Authorized User
 
Join Date: Sep 2003
Location: , , Canada.
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
Reply With Quote
  #2 (permalink)  
Old October 2nd, 2003, 08:48 AM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
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
Reply With Quote
  #3 (permalink)  
Old October 3rd, 2003, 01:07 PM
Authorized User
 
Join Date: Sep 2003
Location: , , Canada.
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
Reply With Quote
  #4 (permalink)  
Old October 3rd, 2003, 04:11 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
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
Reply With Quote
  #5 (permalink)  
Old October 3rd, 2003, 04:37 PM
Authorized User
 
Join Date: Sep 2003
Location: , , Canada.
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
Reply With Quote
  #6 (permalink)  
Old October 3rd, 2003, 05:20 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
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
Reply With Quote
  #7 (permalink)  
Old December 21st, 2004, 01:42 AM
Registered User
 
Join Date: Dec 2004
Location: , , .
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.

Reply With Quote
  #8 (permalink)  
Old December 21st, 2004, 07:24 PM
Registered User
 
Join Date: Dec 2004
Location: , , .
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)



Reply With Quote
  #9 (permalink)  
Old December 21st, 2004, 07:47 PM
Registered User
 
Join Date: Dec 2004
Location: , , .
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];
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 09:04 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.