Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Select records in "chunks"

Posted on 2013-06-25
4
Medium Priority
?
333 Views
Last Modified: 2013-06-26
This is driving me crazy.  I need to select records from an access table in "chunks".  For example: If I have a table with 1039 records, I want to select 250 at a time and update another table.  The problem that I am having is I keep getting duplicate records from a table that does not contain duplicate records.  Any help would be most appreciated.

Here is some code that I am using:

Function fnGetCRData()
    Dim crCols As String, crSQL As String, PCRSCRSql As String, rsCount As Integer, initCount As Integer, totCount As Integer
    Dim rst As Recordset
    'Get the total number of records
    Set rst = CurrentDb.OpenRecordset("SELECT CR_ID FROM tbl_Raw_CRID")
    If rst.EOF Then
      rsCount = 0
    Else
      rst.MoveLast
      rsCount = rst.RecordCount
    End If
    rst.Close
    'Initialize variables to track record selection
    initCount = 250
    totCount = rsCount
    'Loop until all CRIDs have been retrieved
    If initCount >= totCount Then
        Set rst = CurrentDb.OpenRecordset("SELECT CR_ID FROM tbl_Raw_CRID")
        'Loop through the table and create sql of CRIDs
        Do Until rst.EOF
            crCols = crCols & "(A1.CR_ID = " & rst("CR_ID") & ") OR "
            rst.MoveNext
        Loop
        crSQL = "(" & crCols & ")"
        crSQL = Left(crSQL, (Len(crSQL)) - 5)
        crSQL = crSQL & ")"
        rst.Close
        PCRSCRSql = "SELECT A1.CR_ID, A1.CR_NUM, A1.PLANT_CODE, A1.UNIT_CODE, A1.ENTERED_DATETIME, A1.CONDITION_DESC, A1.IMMEDIATE_ACTION_DESC, " _
        & "A1.SUGGESTED_ACTION_DESC, A1.CLOSED_DATETIME, A1.SIGNIFICANCE_CODE, A1.OWNER_GROUP FROM CRSDBA.V_CR_CURRENT A1 WHERE (" & crSQL & ")"
        fnMakeTable PCRSCRSql, "tbl_Raw_CR_Data", "P"
    ElseIf initCount < totCount Then
        Set rst = CurrentDb.OpenRecordset("SELECT TOP " & initCount & " CR_ID FROM (SELECT TOP " & totCount & " CR_ID FROM tbl_Raw_CRID ORDER BY CR_ID) ORDER BY CR_ID DESC")
        'Loop through the table and create sql of CRIDs
        Do Until rst.EOF
            crCols = crCols & "(A1.CR_ID = " & rst("CR_ID") & ") OR "
            rst.MoveNext
        Loop
        crSQL = "(" & crCols & ")"
        crSQL = Left(crSQL, (Len(crSQL)) - 5)
        crSQL = crSQL & ")"
        rst.Close
        PCRSCRSql = "SELECT A1.CR_ID, A1.CR_NUM, A1.PLANT_CODE, A1.UNIT_CODE, A1.ENTERED_DATETIME, A1.CONDITION_DESC, A1.IMMEDIATE_ACTION_DESC, " _
        & "A1.SUGGESTED_ACTION_DESC, A1.CLOSED_DATETIME, A1.SIGNIFICANCE_CODE, A1.OWNER_GROUP FROM CRSDBA.V_CR_CURRENT A1 WHERE (" & crSQL & ")"
        fnMakeTable PCRSCRSql, "tbl_Raw_CR_Data", "P"
        totCount = totCount - 250
        Do Until totCount <= 0
            Set rst = CurrentDb.OpenRecordset("SELECT TOP " & initCount & " CR_ID FROM (SELECT TOP " & totCount & " CR_ID FROM tbl_Raw_CRID ORDER BY CR_ID) ORDER BY CR_ID DESC")
            'Loop through the table and create sql of CRIDs
            Do Until rst.EOF
                crCols = crCols & "(A1.CR_ID = " & rst("CR_ID") & ") OR "
                rst.MoveNext
            Loop
            crSQL = "(" & crCols & ")"
            crSQL = Left(crSQL, (Len(crSQL)) - 5)
            crSQL = crSQL & ")"
            rst.Close
            PCRSCRSql = "SELECT A1.CR_ID, A1.CR_NUM, A1.PLANT_CODE, A1.UNIT_CODE, A1.ENTERED_DATETIME, A1.CONDITION_DESC, A1.IMMEDIATE_ACTION_DESC, " _
            & "A1.SUGGESTED_ACTION_DESC, A1.CLOSED_DATETIME, A1.SIGNIFICANCE_CODE, A1.OWNER_GROUP FROM CRSDBA.V_CR_CURRENT A1 WHERE (" & crSQL & ")"
            CurrentDb.QueryDefs("qry_PT_PCRS").SQL = PCRSCRSql
            'CurrentDb.Execute "INSERT INTO tbl_Raw_CR_Data SELECT * FROM qry_PT_PCRS"
            totCount = totCount - 250
        Loop
    End If
End Function

Open in new window

0
Comment
Question by:SDKCompany
  • 2
4 Comments
 
LVL 31

Expert Comment

by:hnasr
ID: 39276469
You may try:

table1 has the records, 1039 records.
table2 to update with every 250 records from table1
table3 a temp table, empty.

Process:
Select 250 records from table1 where id not in (select id from table3)
Update table2.
Write the 250 records to table3.

Loop for next 250 records.
0
 
LVL 25

Accepted Solution

by:
chaau earned 2000 total points
ID: 39277048
Read this article about pagination in MS Access.
Your code looks ok. Can you confirm that V_CR_CURRENT does not have duplicate CR_IDs.

On a side note, I see a few lines with hard-coding: totCount = totCount - 250
It is probably better to change it to totCount = totCount - initCount. That way you can easily change the initCount to any other value and do not need to remember to change totCount subtraction
0
 
LVL 25

Expert Comment

by:chaau
ID: 39277069
One small note: this code will not work in multi-user environment, when the tables you are selecting from are updated/inserted by other users.
0
 

Author Closing Comment

by:SDKCompany
ID: 39277982
I cleaned up my sql based on the article that was linked to and also found that I was not setting my crSQL variable to nothing after updating the tables and therefore the loop just kept adding the new records to the end of crSQL, hence the duplicate records.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

876 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