Solved

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

Posted on 2012-03-31
7
357 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
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

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!

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

713 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