Go Back  Xtreme Visual Basic Talk > Legacy Visual Basic (VB 4/5/6) > Database and Reporting > Select From/Insert Into


Reply
 
Thread Tools Display Modes
  #1  
Old 01-23-2006, 01:16 PM
lroyce's Avatar
lroyce lroyce is offline
Regular
 
Join Date: Feb 2002
Posts: 57
Default Select From/Insert Into


I am trying to figure out the easiest way to copy values from one table to another. I have two tables in an ms access database: Hardware and Archive
Right now, when Hardware is returned from a shipment, the transaction number is set to "0" to indicate the item is back in stock. Before that takes place, I would like to write the transaction number (transaction_no) and the hardware id (key_id) to the archive table as a cross reference so users can see what hardware was associated with which transaction.
Is there a way to select the transaction_no and key_id from Hardware and write those values to fields with the same names in the Archive table?
I have been trying to find an example somewhere. I can't believe I am the only person ever to have this problem
Thanks in advance for any help
__________________
"I don't want the world - I just want your half."
They Might Be Giants
Lorena
Reply With Quote
  #2  
Old 01-23-2006, 01:33 PM
00100b's Avatar
00100b 00100b is offline
Martian In Disguise

Retired Moderator
* Guru *
 
Join Date: May 2003
Location: Minneapolis, MN
Posts: 9,566
Default

The first piece that you will need is the condition to identify which records are going to be added to the archive table.

Then, you can perform a query similar to:
Code:
INSERT INTO [Archive] ([transaction_no], [key_id])
SELECT [transaction_no], [key_id] FROM [Hardware] WHERE [SomeField] = [SomeValue]
__________________
The only stupid question is the one that goes un-asked.
Reply With Quote
  #3  
Old 01-23-2006, 01:36 PM
lroyce's Avatar
lroyce lroyce is offline
Regular
 
Join Date: Feb 2002
Posts: 57
Default

The value of transaction_no would be the field I would use to select the records. Thanks!
__________________
"I don't want the world - I just want your half."
They Might Be Giants
Lorena
Reply With Quote
  #4  
Old 01-23-2006, 01:48 PM
lroyce's Avatar
lroyce lroyce is offline
Regular
 
Join Date: Feb 2002
Posts: 57
Default Error Message

Before I actually did any coding, I set the query up like this in MS Access:

INSERT INTO Archive a SELECT b.Transaction_No, b.KEY_ID from Hardware b where b.Transaction_no = "10024"

And I am getting "Syntax Error in INSERT INTO Statment"

What did I do wrong?
__________________
"I don't want the world - I just want your half."
They Might Be Giants
Lorena
Reply With Quote
  #5  
Old 01-23-2006, 02:13 PM
00100b's Avatar
00100b 00100b is offline
Martian In Disguise

Retired Moderator
* Guru *
 
Join Date: May 2003
Location: Minneapolis, MN
Posts: 9,566
Default

You can't alias the the table defined by the INSERT INTO clause and you haven't included the field list that will be inserted into.

Try:
Code:
INSERT INTO Archive (Transaction_No, KEY_ID) SELECT b.Transaction_No, b.KEY_ID FROM Hardware b WHERE b.Transaction_No = "10024"
If you were to build the query using the QueryBuilder and then click on the "SQL" view of the query, then you would see how Access would build the query string.
__________________
The only stupid question is the one that goes un-asked.
Reply With Quote
  #6  
Old 01-23-2006, 02:16 PM
lroyce's Avatar
lroyce lroyce is offline
Regular
 
Join Date: Feb 2002
Posts: 57
Default

Thanks. I set it up the way you suggested and it works great!
__________________
"I don't want the world - I just want your half."
They Might Be Giants
Lorena
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
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

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
 
 
-->