Solved

Select records in "chunks"

Posted on 2013-06-25
4
328 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
[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
  • 2
4 Comments
 
LVL 30

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 500 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

Technology Partners: 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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
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…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

756 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