Solved

Select records in "chunks"

Posted on 2013-06-25
4
326 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 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 24

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 24

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

790 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