Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Select records in "chunks"

Posted on 2013-06-25
4
Medium Priority
?
332 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 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

688 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