Well, that's going to be very difficult to do as a straight SQL Query (or collection of Queries). The easiest way to handle it in this situation, is to use VBA code to loop through the Records and, whenever it finds a duplicate entry, delete the data in that Field.
Before I get to that though, I'm gonna clamber up on my soapbox and preach a little bit.
<soapbox>
From what I can see of your data, you're treating the Tables as a collection of Spreadsheets (instead of actual Tables in a relational database). This is fairly typical of people that are just getting into relational databases and, as long as the applications are small and simple, doesn't hurt things too much.
That said, I'd strongly recommend learning about normalization and implementing it in your database(s). It makes things much easier to handle as the database gets larger and more complex, it saves space, and it decreases the possibility of orphaning data/Records when editing information.
</soapbox>
OK, sorry about that, on with the VBA! I've tried to add comments to the code so you can see what I'm doing, but feel free to ask if you have any questions.
Code:
Public Sub Remove_Duplicates()
' This Sub loads the Table tbl_ListagemFretes into a Recordset and "fixes"
' any multiple consecutive values of the Field Frete by replacing the
' consecutive duplicates with empty strings. This Sub does not take any
' arguments and does not return any values.
On Error GoTo Error_Remove_Duplicates
Dim db1 As DAO.Database
Dim work As DAO.Workspace
Dim rst As DAO.Recordset
Dim boolWork As Boolean
Dim strCompare As String
Set db1 = CurrentDb()
Set work = DBEngine(0)
' There really should be an ORDER BY Clause here, but since I don't know the
' full design of the Table, you will need to come up with that part on your
' own
Set rst = db1.OpenRecordset("SELECT * FROM tbl_ListagemFretes", dbOpenDynaset, dbSeeChanges) ' There really should be an ORDER BY clause in here!
' Only do the following if there's at least one (1) Record in the Table
If Not rst.RecordCount = 0 Then
strCompare = "" ' Initial value to compare "Frete" against
' Wrap everything in a Transaction so any database changes can be undone in
' the event of an error
work.BeginTrans
boolWork = True ' Transaction flag variable
' Loop through the entire Table
Do While Not rst.EOF
' Compare the previous Record's Frete value (recorded in strCompare) with
' the current Record's value
If rst("Frete") = strCompare Then
' If they match
' Delete the value of Frete for the current Record, do not change
' strCompare
With rst
.Edit
!Frete = ""
.Update
End With
Else
' If they don't match
' Change strCompare to match the new value of Frete, do not delete the
' value in the current Record
strCompare = rst("Frete")
End If
' Move to the next Record
rst.MoveNext
Loop
' If we haven't encountered any errors, save the changes to the database
work.CommitTrans
boolWork = False
End If
Function_Closing:
' Clean up our Object variables
On Error Resume Next ' Disable error checking to prevent an infinite loop!
rst.Close
Set rst = Nothing
Set work = Nothing
Set db1 = Nothing
On Error GoTo 0 ' ALWAYS turn error checking back on when you're done!
Exit Sub ' Exit the Sub/Function
Error_Remove_Duplicates:
' If we encounter an error
' If we're in the middle of our Transaction
If boolWork = True Then
' Unset our Transaction flag variable to prevent an infinite loop!
boolWork = False
' Undo all our changes to prevent data corruption or unanticipated changes
work.Rollback
End If
' Display the error message
MsgBox Err.Number & ": " & Err.Description
' Jump to the end of the Sub/Function
Resume Function_Closing
End Sub
Novice
Windows 7 64bit 
Access 2007
Originally Posted by Rawb
Combo Box showing Blank Values
Posting Permissions
Reply With Quote