Solved

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

Posted on 2012-03-31
7
358 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 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

739 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