Solved

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

Posted on 2012-03-31
7
360 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 48

Expert Comment

by:Dale Fye
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

624 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