report generating duplicates

  • Thread starter Thread starter David
  • Start date Start date
D

David

I'm trying to create a form that will generate a report on
duplicates from a table. I've written a query that will
filter out all records except duplicates.

All duplicates (and the original) are listed on the form
continuously, preceded by a checkbox. I want to designate
rows to be deleted by the checked box in front.

I've already tried coding something when the form loads
up. But I've had no success referencing the actual
checkbox.

Any ideas?
 
David,

As you have discovered an unbound control on continuous forms, in your case
a checkbox, has the same value for every instance of the form. The only way
to show the checkbox ticked for duplicate records is to bind it to some
column of the query or table which indicates that the record is a duplicate.

You can

a) Add a field to the table which indicates that a record is a duplicate and
use your find duplicates query to populate the field.

b) Create a temporary table which stores the primary key of the original
table and a field indicating that the corresponding record is a duplicate,
then join this temporary table with the query you are using as the
recordsource for your continuous form (or include all required fields and
use the temporary table as the recordsource).

c) Create a new query that includes a IsDuplicate field in addition to the
fields in your existing recordsource.

The best option will depend on how you are identifying duplicates.

Good luck,

Rod Scoullar
 
Rod:

I appreciate the suggestions; they were very helpful.

I found that the checkbox's control source is linked to
the IsDuplicate column of my query to find duplicates.
The query itself is structured as such:

SELECT
[tblLeave].[IsDuplicate],
[tblLeave].[EmployeeID],
[tblLeave].[DateBeginning],
[tblLeave].[DateEnding],
[tblLeave].[NumberOfHours],
[tblLeave].[UnionID],
[tblLeave].[Purpose],
[tblLeave].[Agency],
[tblEmployee].[LastName],
[tblEmployee].[FirstName]
FROM
tblEmployee
INNER JOIN
tblLeave
ON
[tblEmployee].[EmployeeID]=[tblLeave].[EmployeeID]
WHERE
(((tblLeave.EmployeeID)
In
(SELECT
[EmployeeID]
FROM
[tblLeave] As Tmp
GROUP BY
[EmployeeID],
[DateBeginning],
[DateEnding],
[NumberOfHours]
HAVING
Count(*)>1
And [DateBeginning] = [tblLeave].
[DateBeginning]
And [DateEnding] = [tblLeave].[DateEnding]
And [NumberOfHours] = [tblLeave].
[NumberOfHours])))
ORDER BY
[tblLeave].[EmployeeID],
[tblLeave].[DateBeginning],
[tblLeave].[DateEnding],
[tblLeave].[NumberOfHours];


My problem now is loading a recordset that I can change
and update. I want to load this new query into the
recordset, and then run a while loop, searching for
all duplicates ONLY (leaving the originals unchecked) and
have check each record or row that is a duplicate on the
form.

So far, I have written this code to load and compare
records in a recordset, but it won't allow me to make
changes to tblLeave, which has the IsDuplicate column.
How can I put this check in the checkbox of the individual
row?

Private Sub Form_Load()
Dim db As Database
Dim qdf As queryDef
Dim rs As Recordset
Dim OldKey As String
Dim NewKey As String
Dim SQLCall As String

SQLCall = "SELECT [tblLeave].[IsDuplicate], [tblLeave].
[EmployeeID], " _
& "[tblLeave].[DateBeginning], [tblLeave].
[DateEnding], " _
& "[tblLeave].[NumberOfHours], [tblLeave].[UnionID], "
_
& "[tblLeave].[Purpose], [tblLeave].[Agency],
[tblEmployee].[LastName], " _
& "[tblEmployee].[FirstName] FROM [tblEmployee] INNER
JOIN [tblLeave]" & _
"ON [tblEmployee].[EmployeeID] = [tblLeave].
[EmployeeID]" & _
"WHERE ((([tblLeave].[EmployeeID]) In (SELECT
[EmployeeID]" & _
"FROM [tblLeave] As Tmp GROUP BY [EmployeeID],
[DateBeginning], " _
& "[DateEnding],[NumberOfHours] HAVING Count(*)>1" & _
"And [DateBeginning] = [tblLeave].[DateBeginning]" & _
"And [DateEnding] = [tblLeave].[DateEnding]" & _
"And [NumberOfHours] = [tblLeave].[NumberOfHours])))"
& _
"ORDER BY [tblLeave].[EmployeeID], [tblLeave].
[DateBeginning], " _
& "[tblLeave].[DateEnding], [tblLeave].[NumberOfHours]"


'this will read data from the current database UNION into
variable rs
Set db = CurrentDb
Set qdf = db.CreateQueryDef("qryDuplicate", SQLCall)

With qdf
Set rs = .OpenRecordset(dbOpenDynamic)
End With

rs.MoveFirst
NewKey = rs.Fields("EmployeeID").Value & "; " & rs.Fields
("DateBeginning").Value & _
"; " & rs.Fields("DateEnding").Value & "; " & rs.Fields
("NumberOfHours")

If rs.RecordCount = 1 Then
OldKey = " "
End If

'compare each record of the table to OldKey
Do While Not rs.EOF
'if oldkey = newkey, then it is a duplicate record, check
the duplicates ONLY, and then
'make oldkey the next record to compare to next record

If OldKey = NewKey Then
With rs
'rs.Edit
rs.Fields("IsDuplicate").Value = True
'rs.Update ("IsDuplicate")
End With
'DoCmd.RunSQL "UPDATE [tblLeave] SET [IsDuplicate] = TRUE"


OldKey = NewKey
rs.MoveNext
If rs.EOF = True Then
Exit Sub
Else
NewKey = rs.Fields("EmployeeID").Value & "; "
& rs.Fields("DateBeginning").Value & _
"; " & rs.Fields("DateEnding").Value & "; " & rs.Fields
("NumberOfHours")
End If

'if oldkey does not equal newkey, then it is not a
duplicate record and must check with
'the rest of the records in the table
Else
OldKey = NewKey
rs.MoveNext
If rs.EOF = True Then
Exit Sub
Else
NewKey = rs.Fields("EmployeeID").Value & "; "
& rs.Fields("DateBeginning").Value & _
"; " & rs.Fields("DateEnding").Value & "; " & rs.Fields
("NumberOfHours")
End If

End If
Loop

End Sub
 
David,

Unless I am mistaken your problem is caused by the structure of the table
you are using. tblLeave appears to distinguish between records by using
EmployeeID and DateBeginning. These two fields should uniquely define the
leave. This should be the primary key. You have duplicate primary keys
which is causing the problems.

The leave table only needs to store EmployeeID, DateBeginning and DateEnding
(and possibly NumberOfHours, UnionID, Purpose and Agency if these relate
only to the period of leave). Other fields eg FirstName should be stored
with the Employee table.

That is by the by. What we have to do to solve the problem is to do what
you have done in the latter part of your code.
The only difference is that we won't bother to use a query, we'll create a
recordset from tblLeave directly ordered by EmployeeID, DateBeginning,
DateEnding, NumberOfHours. We then loop through the recordset testing to
see if a record is a duplicate of the previous one and if so set the
IsDuplicate value to True.

I started to write some code but you are obviously quite capable of doing
that yourself.

Let me know if this worked.

Rod Scoullar.
 
David,

Unless I am mistaken your problem is caused by the structure of the table
you are using. tblLeave appears to distinguish between records by using
EmployeeID and DateBeginning. These two fields should uniquely define the
leave. This should be the primary key. You have duplicate primary keys
which is causing the problems.

The leave table only needs to store EmployeeID, DateBeginning and DateEnding
(and possibly NumberOfHours, UnionID, Purpose and Agency if these relate
only to the period of leave). Other fields eg FirstName should be stored
with the Employee table.

That is by the by. What we have to do to solve the problem is to do what
you have done in the latter part of your code.
The only difference is that we won't bother to use a query, we'll create a
recordset from tblLeave directly ordered by EmployeeID, DateBeginning,
DateEnding, NumberOfHours. We then loop through the recordset testing to
see if a record is a duplicate of the previous one and if so set the
IsDuplicate value to True.

I started to write some code but you are obviously quite capable of doing
that yourself.

Let me know if this worked.

Rod Scoullar.
 
David,

Unless I am mistaken your problem is caused by the structure of the table
you are using. tblLeave appears to distinguish between records by using
EmployeeID and DateBeginning. These two fields should uniquely define the
leave. This should be the primary key. You have duplicate primary keys
which is causing the problems.

The leave table only needs to store EmployeeID, DateBeginning and DateEnding
(and possibly NumberOfHours, UnionID, Purpose and Agency if these relate
only to the period of leave). Other fields eg FirstName should be stored
with the Employee table.

That is by the by. What we have to do to solve the problem is to do what
you have done in the latter part of your code.
The only difference is that we won't bother to use a query, we'll create a
recordset from tblLeave directly ordered by EmployeeID, DateBeginning,
DateEnding, NumberOfHours. We then loop through the recordset testing to
see if a record is a duplicate of the previous one and if so set the
IsDuplicate value to True.

I started to write some code but you are obviously quite capable of doing
that yourself.

Let me know if this worked.

Rod Scoullar.
 
Back
Top