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
Solved

Tricky table issue

Posted on 2013-06-05
9
218 Views
Last Modified: 2013-06-07
Please see the attached file/table.  Here is what I have to find a way to do.

1) If the records have the same "Sales Record Number", and the field named "User ID" is blank, COPY the data from "User ID", "Buyer Fullname", "Buyer Phone Number", "Buyer Email", "Buyer Address 1", "Buyer Address 2", "Buyer City", "Buyer State", "Buyer Zip", "Buyer Country", "Payment Method", and "Custom Label" INTO the records with the empty "User ID" that have the same "User ID" as the record being copied from.

and then,

2) Delete the record that had the information being copied from. In this example it would be record #2.

and,

3) Cycle through all the records in the table.  The example holds just a sampling of the records.  There could be many records.

I'd like to keep the data in the original table (until I review the results and them I'll delete them and end up with a new "cleaned" table.  This final "cleaned" table would be named "tblCleanedRecords".

I sure hope I explained this one well.

Thanks ahead of time to anyone that will take this one on.

--Steve
Sample2.accdb
0
Comment
Question by:SteveL13
  • 5
  • 3
9 Comments
 
LVL 84
ID: 39223601
So in your tblImportRecords, you'd copy the data from Record #2 into both Record #3 and #4?
0
 

Author Comment

by:SteveL13
ID: 39223639
Exactly.  But don't change the data in the fields named "Sales Record Number", "Item ID", "Item Title", "Sale Price", or "Sale Date" because that data is already there and should not be changed.
0
 

Author Comment

by:SteveL13
ID: 39224053
Ok, so I've tried to do this myself byt am stuck.  Here's my onclick code for a command button to accomplish this.  Am I even close?

Private Sub Command60_Click()

    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("tblImportRecords")

    'Check to see if the recordset actually contains rows
    If Not (r.EOF And r.BOF) Then
        rs.MoveFirst
        Do Until rs.EOF = True

    'Perform an edit
    If IsNull(UserID) Then
        rs.Edit
        rs! "User ID" = DLookup("[User ID]", "tblImportRecords", "[ID] = [ID]-1")
        rs! "Buyer Fullname" = DLookup("[Buyer Fullname]", "tblImportRecords", "[ID] = [ID]-1")
        rs! "Buyer Phone Number" = DLookup("[Buyer Phone Number]", "tblImportRecords", "[ID] = [ID]-1")
        rs! "Buyer Email" = DLookup("[Buyer Email]", "tblImportRecords", "[ID] = [ID]-1")
        rs! "Buyer Address 1" = DLookup("[Buyer Address 1]", "tblImportRecords", "[ID] = [ID]-1")
        rs! "Buyer Address2" = DLookup("[Buyer Address2]", "tblImportRecords", "[ID] = [ID]-1")
        rs! "Buyer City" = DLookup("[Buyer City]", "tblImportRecords", "[ID] = [ID]-1")
        rs! "Buyer State" = DLookup("[Buyer State]", "tblImportRecords", "[ID] = [ID]-1")
        rs! "Buyer Zip" = DLookup("[Buyer Zip]", "tblImportRecords", "[ID] = [ID]-1")
        rs! "Buyer Country" = DLookup("[Buyer Country]", "tblImportRecords", "[ID] = [ID]-1")
        rs! "Payment Method" = DLookup("[Payment Method]", "tblImportRecords", "[ID] = [ID]-1")
        rs! "Custom Label" = DLookup("[Custom Label]", "tblImportRecords", "[ID] = [ID]-1")
        rs.Update
        rs.MoveNext

        Loop
       
    End If

    MsgBox "Data Cleaning Finished."

    rs.Close

    Set rs = Nothing

End Sub
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 29

Expert Comment

by:IrogSinta
ID: 39224368
Here you go:
Private Sub Command60_Click()
    Dim rs As Recordset, rs2 As Recordset
    Dim db As Database
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("Select * From tblImportRecords Where [User Id] Is Not Null")
    
    Do While Not rs.EOF
        Set rs2 = db.OpenRecordset("Select * From tblImportRecords Where [User ID] Is Null AND [Sales Record Number]='" & rs![Sales Record Number] & "'")
        If Not rs2.EOF Then
            'cycle through records with missing info and update
            Do While Not rs2.EOF
                rs2.Edit
                rs2![User ID] = rs![User ID]
                rs2![Buyer Fullname] = rs![Buyer Fullname]
                rs2![Buyer Phone Number] = rs![Buyer Phone Number]
                rs2![Buyer Email] = rs![Buyer Email]
                rs2![Buyer Address 1] = rs![Buyer Address 1]
                rs2![Buyer Address 2] = rs![Buyer Address 2]
                rs2![Buyer City] = rs![Buyer City]
                rs2![Buyer State] = rs![Buyer State]
                rs2![Buyer Zip] = rs![Buyer Zip]
                rs2![Buyer Country] = rs![Buyer Country]
                rs2![Payment Method] = rs![Payment Method]
                rs2![Custom Label] = rs![Custom Label]
                rs2.Update
                rs2.MoveNext
            Loop
            'Delete source record
            rs.Delete
        End If
        rs2.Close
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
    Set rs2 = Nothing
    
    MsgBox "Data Cleaning Finished."
        
End Sub

Open in new window

0
 

Author Comment

by:SteveL13
ID: 39225749
Wow!  Perfect.  But it sure is slow.  I have 7,548 records in the original table.  I wonder if there is a way to speed this up.  On my computer the whole process took 5 minutes.

Also, as you can see with the code below I have coded for a progress meter.  But what is interesting is the progress meter never moved during the 5 minutes.  When the process finished the meter went away.  Never moved.

Here's my latest code with everything included...

Private Sub cmdImportAndClean_Click()
On Error GoTo Err_cmdImportAndClean_Click

    If MsgBox("This function will ask you to select an Excel file, import the data into this program, and clean the data. Do you want to continue?", vbYesNo + vbQuestion + vbDefaultButton2) = vbYes Then

    MsgBox "This function will take a few minutes.  Do not abort the function or the data integrity will be compromised."

    DoCmd.Hourglass True
   
    'Initialize the progress meter.
    SysCmd acSysCmdInitMeter, "Reading Data...", Count

    DoCmd.SetWarnings False

    'Import Excel file into tblTempImport
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tblTempImport", "C:\Users\Steve\Desktop\Import Files\ImportFile.xlsx", True

    'Use tblTempImport to make table named tblImportRecords
    DoCmd.OpenQuery "mktblqryImportRecords", acViewNormal, acAdd
   
    'Run query to delete records from tblTempImport
    DoCmd.OpenQuery "delqryDeleteRecordsFromtblTempImport", acViewNormal, acEdit
   
    'Data cleaning process
    Dim rs As Recordset, rs2 As Recordset
    Dim db As Database
   
    Set db = CurrentDb
    Set rs = db.OpenRecordset("Select * From tblImportRecords Where [User Id] Is Not Null")
   
    Do While Not rs.EOF
        Set rs2 = db.OpenRecordset("Select * From tblImportRecords Where [User ID] Is Null AND [Sales Record Number]='" & rs![Sales Record Number] & "'")
        If Not rs2.EOF Then
            'cycle through records with missing info and update
            Do While Not rs2.EOF
                rs2.Edit
                rs2![User ID] = rs![User ID]
                rs2![Buyer Fullname] = rs![Buyer Fullname]
                rs2![Buyer Phone Number] = rs![Buyer Phone Number]
                rs2![Buyer Email] = rs![Buyer Email]
                rs2![Buyer Address 1] = rs![Buyer Address 1]
                rs2![Buyer Address 2] = rs![Buyer Address 2]
                rs2![Buyer City] = rs![Buyer City]
                rs2![Buyer State] = rs![Buyer State]
                rs2![Buyer Zip] = rs![Buyer Zip]
                rs2![Buyer Country] = rs![Buyer Country]
                rs2![Payment Method] = rs![Payment Method]
                rs2![Custom Label] = rs![Custom Label]
                rs2.Update
                rs2.MoveNext
            Loop
            'Delete source record
            rs.Delete
        End If
        rs2.Close
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
    Set rs2 = Nothing
   
    DoCmd.SetWarnings True
   
    DoCmd.Hourglass False
    DoCmd.Beep
   
    'Remove the progress meter.
    SysCmd acSysCmdRemoveMeter
   
    MsgBox "Data Cleaning Finished."

    Else
   
    MsgBox "Import/Clean Function Cancelled"
   
    End If
   
Exit_cmdImportAndClean_Click:
    Exit Sub
   
Err_cmdImportAndClean_Click:
    MsgBox Err.Description
    Resume Exit_cmdImportAndClean_Click

End Sub
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 39226253
This would be much faster by running a query instead. I'm on the road right now but I can get back with you late tonight.
0
 

Author Comment

by:SteveL13
ID: 39226657
Thank you very much.  I really appreciate it.
0
 
LVL 29

Accepted Solution

by:
IrogSinta earned 500 total points
ID: 39227979
Try this version:
query1 marks which records to delete
query2 updates the records
query3 deletes the marked records

Just click the button on your form.  Also it's preferable to use Execute rather than Open query.  One reason is so that you don't have to turn off warnings.
Sample2.accdb
0
 

Author Closing Comment

by:SteveL13
ID: 39229171
Absolutely PERFECT!  Thanks.
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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
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…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

840 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