Solved

Insert Records into a new Table with 2 different incrementing numbers in 2 different fields

Posted on 2012-03-31
7
355 Views
Last Modified: 2012-06-27
Hi Experts:

I need help with populating a temp table using a loop through another temp tables’ records using command button code. I have simplified the data so that I can better explain what I need to do.

Initial Temp Table name:  “TempTbl_Pal”
Temp Table to be populated:  “TempTbl_Pal_Total”
Table that assigns Ref# per Scanned_Idn:  “Plt_Qck_Ref_Control_Tbl”

The first temp table has 2 fields that I need to use to build the update for the next temp table:
“Scanned_Idn” is a number data type field
“Plt_Ttl_Cnt” is a number data type field

A simple example for the TempTbl_Pal data would be:

Scanned_Idn = 8000000 | Plt_Ttl_Cnt = 10
Scanned_Idn = 8000001 | Plt_Ttl_Cnt = 2
Scanned_Idn = 8000002 | Plt_Ttl_Cnt = 1

The “Plt_Qck_Ref_Control_Tbl” contains one field [Plt_Qck_Ref_Idn] which acts as a quick reference number. This Ref# should be used once per Scanned_Idn, and then increased by (+1) for the next Scanned_Idn.  Therefore, if the [Plt_Qck_Ref_Idn] currently equals “150”, then this would be how the TempTbl_Pal should be updated:

Scanned_Idn = 8000000 | Plt_Ttl_Cnt = 10 | Plt_Qck_Ref_Idn = 150
Scanned_Idn = 8000001 | Plt_Ttl_Cnt = 2 | Plt_Qck_Ref_Idn = 151
Scanned_Idn = 8000002 | Plt_Ttl_Cnt = 1 | Plt_Qck_Ref_Idn = 152

Lastly, once the “TempTbl_Pal” has been updated with a Ref# per Scanned_Idn; those records need to be inserted into another temp table “TempTbl_Pal_Total”. There are four fields that need to be inserted here. The field [Plt_Idn_Cnt] needs to be populated using the [PLt_Ttl_Cnt] fields’ number for each Scanned_Idn. It is difficult to articulate what the process is in words, but I will try. For the first Scanned_Idn (above example of “8000000”) I need to insert 10 rows into the “TempTbl_Pal_Total” and the field [Plt_Idn_Cnt] must start as “1” in the first row and increment by +1 for each successive row. Here is what I need inserted into the TempTbl_Pal_Total table:

Scanned_Idn = 8000000 | Plt_Ttl_Cnt = 10 | Plt_Qck_Ref_Idn = 150 | Plt_Idn_Cnt = 1
Scanned_Idn = 8000000 | Plt_Ttl_Cnt = 10 | Plt_Qck_Ref_Idn = 150 | Plt_Idn_Cnt = 2
Scanned_Idn = 8000000 | Plt_Ttl_Cnt = 10 | Plt_Qck_Ref_Idn = 150 | Plt_Idn_Cnt = 3
Scanned_Idn = 8000000 | Plt_Ttl_Cnt = 10 | Plt_Qck_Ref_Idn = 150 | Plt_Idn_Cnt = 4
Scanned_Idn = 8000000 | Plt_Ttl_Cnt = 10 | Plt_Qck_Ref_Idn = 150 | Plt_Idn_Cnt = 5
Scanned_Idn = 8000000 | Plt_Ttl_Cnt = 10 | Plt_Qck_Ref_Idn = 150 | Plt_Idn_Cnt = 6
Scanned_Idn = 8000000 | Plt_Ttl_Cnt = 10 | Plt_Qck_Ref_Idn = 150 | Plt_Idn_Cnt = 7
Scanned_Idn = 8000000 | Plt_Ttl_Cnt = 10 | Plt_Qck_Ref_Idn = 150 | Plt_Idn_Cnt = 8
Scanned_Idn = 8000000 | Plt_Ttl_Cnt = 10 | Plt_Qck_Ref_Idn = 150 | Plt_Idn_Cnt = 9
Scanned_Idn = 8000000 | Plt_Ttl_Cnt = 10 | Plt_Qck_Ref_Idn = 150 | Plt_Idn_Cnt = 10
Scanned_Idn = 8000001 | Plt_Ttl_Cnt = 2 | Plt_Qck_Ref_Idn = 151 | Plt_Idn_Cnt = 1
Scanned_Idn = 8000001 | Plt_Ttl_Cnt = 2 | Plt_Qck_Ref_Idn = 151 | Plt_Idn_Cnt = 2
Scanned_Idn = 8000002 | Plt_Ttl_Cnt = 1 | Plt_Qck_Ref_Idn = 152 | Plt_Idn_Cnt = 1

I have used the
Dim i as integer
For i = 1 to [Plt_Ttl_Cnt]
      Insert SQL statement
Next i

But I have only used the above to work with one Scanned_Idn at a time that is bound to a form.

I am unsure how best to tackle this issue when dealing with multiple Scanned_Idn’s in a temp table. I am unsure if this requires using “Dim as recordset” and if it does, then I will need help in how to call and define that variable and then walk through each record to make sure that the [Plt_Qck_Ref_Idn] as well as the [Plt_Idn_Cnt] are updated and incremented as displayed above.

Please let me know if you will need additional information. I am using Access 2010 for the front end and SQL 2008 for the backend.

Thank you in advance for your support and help,
Sincerely,
KLB
0
Comment
Question by:CPKGDevTeam
  • 4
  • 2
7 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 37791634
When running a make-table query that includes fields from more than one table that are Autonumber fields, I would recommend explicitly converting those fields to long, which seems to overwrite the autoincrement restriction when creating the new table.

So, if you had EmployeeID (autonumber) and a DepartmentID as the PK (autonumber) fields in two different tables, you might write your query as:

SELECT clng(tblEmp.EmployeeID) as EmployeeID, tblEmp.EmpNameFirst, tblEmp.EmpNameLast, clng(tblDept.DepartmentID) as DeptID, tblDept.DeptName
INTO tblMakeTable
FROM tblEmp
INNER JOIN tblDept
ON tblEmp.DeptID = tblDept.DepartmentID

Probably not a great example, but explicitly converting those two ID fields to Long with the cLng() function should do it.
0
 
LVL 29

Assisted Solution

by:IrogSinta
IrogSinta earned 500 total points
ID: 37792565
See if this works for you:
Dim rst As Recordset
Dim refNum as Integer, cnt as Integer  

refNum = DLookUp("[Plt_Qck_Ref_Idn]","Plt_Qck_Ref_Control_Tbl")

Set rst = currentdb.openRecordset("TempTbl_Pal")
Do While NOT rst.EOF
    refNum = refNum + 1
    DoCmd.RunSql "UPDATE TempTbl_Pal SET Plt_Qck_Ref_Idn =" & refNum
    rst.MoveNext
Loop

rst.MoveFirst
Do While NOT rst.EOF
    For cnt = 1 To rst!Plt_Ttl_Cnt
        DoCmd.RunSql "INSERT INTO TempTbl_Pal_Total (Scanned_Idn, Plt_Ttl_Cnt, Plt_Qck_Ref_Idn, Plt_Idn_Cnt) VALUES (" & _
            " & rst!Scanned_Idn & "," & rst!Plt_Ttl_Cnt & "," & rst!Plt_Qck_Ref_Idn & "," & cnt & ")"
    Next
    rst.MoveNext
Loop
rst.Close
Set rst = Nothing

Open in new window


Now if the purpose of updating your first temp table with a quick reference ID was only to populate the 2nd temp table correctly, you can just skip that part and shorten your code a bit to this:
Dim rst As Recordset
Dim refNum as Integer, Cnt as integer  

refNum = DLookUp("[Plt_Qck_Ref_Idn]","Plt_Qck_Ref_Control_Tbl")

Set rst = currentdb.openRecordset("TempTbl_Pal")
Do While NOT rst.EOF
    refNum = refNum +1
    For cnt = 1 To rst!Plt_Ttl_Cnt
        DoCmd.RunSql "INSERT INTO TempTbl_Pal_Total (Scanned_Idn, Plt_Ttl_Cnt, Plt_Qck_Ref_Idn, Plt_Idn_Cnt) VALUES (" & _
            " & rst!Scanned_Idn & "," & rst!Plt_Ttl_Cnt & "," & refNum & "," & cnt & ")"
    Next
    rst.MoveNext
Loop
rst.Close
Set rst = Nothing

Open in new window

Hope this helps you out.
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 37793440
I originally had DMax in the code so that there would be no need for the table called Plt_Qck_Ref_Control_Tbl but I figured that since the other tables appeared to be temporary and that the data in them may get deleted that he needed this table to keep track of his last Reference ID.
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

Author Comment

by:CPKGDevTeam
ID: 37796333
Thanks All.

IrogSinta - I am trying to use the code that you provided (many thanks). I am actually just working on the first part where the Quick Ref Code # is assigned. Unfortunately, when the code runs, it updates all the records with the same QRCode#, instead of each incrementing by 1. For example, I have 9 rows in the TempTbl_Pal table, and the code runs 9 times, each time updating with the QR#Code (I started with the refNum = 1). So all records were updated with (1). Then the code ran again and updated them all with the (2). Then by the 9th pass, all records were updated with (9).

I have included a snapshot of how the data looks by the end of the code run.

Please advise and thank you again for the support.

Sincerely,
KLB

How the Temp Table looks after code run
0
 
LVL 29

Accepted Solution

by:
IrogSinta earned 500 total points
ID: 37796374
My bad!  If you had used the 2nd version it would have worked fine.
The first loop in the first version should have been:

Do While NOT rst.EOF
    refNum = refNum + 1
    rst.Edit
        rst!Plt_Qck_Ref_Idn = refNum
    rst.Update    
    rst.MoveNext
Loop

Sorry about that.
0
 

Author Closing Comment

by:CPKGDevTeam
ID: 37797091
Thanks IrogSinta for the quick replies and for the code. It made sense and was very easy to tweak. I have another weird consideration that I had to enter where the QR Code could not go over (999) and needed to be "reseeded" to (1). So I have added that to the code. Here is my final code, for those who may want to see how this turned out.

This was also very educational, and I have some new tricks to use thanks to you.

Cheers!
KLB

Final Code:

Dim rst As Recordset
Dim refNum As Integer, cnt As Integer
Dim strUPQRCode As String

DoCmd.SetWarnings False

Set rst = CurrentDb.OpenRecordset("TempTbl_Pal")
Do While Not rst.EOF
    refNum = DLookup("[Plt_Qck_Ref_Control_Idn]", "Plt_Qck_Ref_Control_Tbl")
    rst.Edit
        rst!Plt_Qck_Ref_Idn = refNum
    rst.Update
    rst.MoveNext
    If refNum = 999 Then
        refNum = 1
        strUPQRCode = "Update [Plt_Qck_Ref_Control_Tbl] SET [Plt_Qck_Ref_Control_Idn] = " & refNum
        DoCmd.RunSQL strUPQRCode
    Else:
        refNum = refNum + 1
        strUPQRCode = "Update [Plt_Qck_Ref_Control_Tbl] SET [Plt_Qck_Ref_Control_Idn] = " & refNum
        DoCmd.RunSQL strUPQRCode
    End If
Loop

rst.MoveFirst
Do While Not rst.EOF
    For cnt = 1 To rst!Plt_Ttl_Cnt
       DoCmd.RunSQL "INSERT INTO TempTbl_Pal_Total (Scanned_Idn, Plt_Drop_Zone, Plt_Ttl_Cnt, Plt_Qck_Ref_Idn, Plt_Idn_Cnt) VALUES (" & rst!Scanned_Idn & ",'" & rst!Plt_Drop_Zone & "'," & rst!Plt_Ttl_Cnt & "," & rst!Plt_Qck_Ref_Idn & "," & cnt & ")"
       Next
    rst.MoveNext
Loop

rst.Close
Set rst = Nothing

DoCmd.SetWarnings True
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 37797135
That's cool.
I see where you could shorten your code a bit further:
    If refNum = 999 Then
        refNum = 1        
    Else:
        refNum = refNum + 1        
    End If
    strUPQRCode = "Update [Plt_Qck_Ref_Control_Tbl] SET [Plt_Qck_Ref_Control_Idn] = " & refNum
    DoCmd.RunSQL strUPQRCode
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Documents and settings folder 30 76
SQL Syntax 5 34
Dcount using a date in a table compared to today's date 3 29
What's wrong with this T-SQL Foreign Key? 7 41
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question