By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,508 Members | 2,849 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,508 IT Pros & Developers. It's quick & easy.

INSERT INTO Table2 from Table1 - maintain sequential index in Table2?

P: 2
Hello, I'm not sure if this has been asked here before (wouldn't know what to search for).

Here's my situation...

I have records in Table1 that I need to insert into Table2.
However, there is a field in Table2 that is sequential (like an index) that I need to maintain...
Therefore I would need my query to lookup the max value for that index, then add one for each new record added from Table1

Illustration

Table1:
ID
5
6
7
8

Table2:
ID-------TranKey
1 56000
2 56001
3 56002
4 56003

I need to insert the records from Table1 into Table2 (based on ID), and then have TranKey remain sequential...

Table2:
ID-------TranKey
1 56001
2 56002
3 56003
4 56004
5 56005
6 56006
7 56007
8 56008


I would imagine I'd have to use the MAX function to determine the last used value for TranKey in Table2, and a WHILE loop to insert the records... but I'm not sure how to implement everything into one procedure.

Can anyone help me?
Any assistance would be greatly appreciated!
Feb 4 '10 #1
Share this Question
Share on Google+
3 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
Some example code I just wrote, you just need to modify it with some selection code on WHICH records to append/Transfer (If you don't want all records transferred). To do that, add a Where clause to the OpenRecordset statement.



Expand|Select|Wrap|Line Numbers
  1. Public Sub AppendWithMax()
  2.  
  3. 'Open a recordset
  4.     Dim myRS As DAO.Recordset
  5.     Set myRS = CurrentDb.OpenRecordset("SELECT * FROM Table1 ORDER BY ID", dbOpenDynaset, dbReadOnly)
  6.  
  7.  
  8. 'Set warnings to false, otherwise we get warnings when doing append query
  9.     DoCmd.SetWarnings (False)
  10.  
  11. 'Loop through records appending data.
  12.     Dim strSQL As String
  13.     Dim lngTranKey As Long
  14.  
  15.     Do While Not myRS.EOF
  16.         lngTranKey = DMax("TranKey", "Table2") + 1
  17.         strSQL = "INSERT INTO Table2 ( ID, Field1, TranKey )" & _
  18.                 " SELECT Table1.ID, Table1.Field1, " & lngTranKey & " AS Expr1" & _
  19.                 " FROM Table1 " & _
  20.                 " WHERE ID=" & myRS!ID
  21.  
  22.         DoCmd.RunSQL strSQL
  23.  
  24.  
  25.         myRS.MoveNext
  26.     Loop
  27.  
  28.  
  29. 'Return to normal warning level
  30.     DoCmd.SetWarnings (True)
  31.  
  32. End Sub
Feb 5 '10 #2

TheSmileyCoder
Expert Mod 100+
P: 2,321
Im also guessing that simply making hte TranKey into a autonumber column is not the solution you were looking for, which would be somewhat simpler :P
Feb 5 '10 #3

P: 2
This worked perfectly, Thanks!
Feb 5 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.