SteveL13
asked on
Tricky table issue
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
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
So in your tblImportRecords, you'd copy the data from Record #2 into both Record #3 and #4?
ASKER
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.
ASKER
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("t blImportRe cords")
'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
Private Sub Command60_Click()
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("t
'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
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
ASKER
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_Clic k
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\Im port 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 "delqryDeleteRecordsFromtb lTempImpor t", 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_Cli ck:
Exit Sub
Err_cmdImportAndClean_Clic k:
MsgBox Err.Description
Resume Exit_cmdImportAndClean_Cli ck
End Sub
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_Clic
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\Im
'Use tblTempImport to make table named tblImportRecords
DoCmd.OpenQuery "mktblqryImportRecords", acViewNormal, acAdd
'Run query to delete records from tblTempImport
DoCmd.OpenQuery "delqryDeleteRecordsFromtb
'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_Cli
Exit Sub
Err_cmdImportAndClean_Clic
MsgBox Err.Description
Resume Exit_cmdImportAndClean_Cli
End Sub
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.
ASKER
Thank you very much. I really appreciate it.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Absolutely PERFECT! Thanks.