Solved

Select records in "chunks"

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
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…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

867 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

16 Experts available now in Live!

Get 1:1 Help Now