Solved

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

Posted on 2012-03-31
7
353 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)
Comment Utility
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
Comment Utility
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
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:CPKGDevTeam
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
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…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

728 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now