Delete Dups Query Failing

[esn]

I have a form set up for data entry with a subform linking child
records in a different table. In the case where a user closes the
form halfway through entering child records then reopens it to finish
adding the child records, I don't want to keep the duplicate master
record they create when they reenter the information in the controls
bound to the master table. So I made a simple find duplicates query,
added a "MaxofID" field, and joined it to a delete query that should
delete the second instance of the duplicate record. I was going to
run this query whenever the form is closed. Unfortunately, I get an
error message saying Access is unable to delete the records from the
specified table. Any ideas? The help topic that pops up seems way
off, since it only discusses the possibility that the database is
opened as read only and I can manually delete the records from the
table with no problems. Here are the queries:

Find Duplicates Query:
SELECT First([UPFU Mammal Visit Data].Site) AS [Site Field], First
([UPFU Mammal Visit Data].Day) AS [Day Field], First([UPFU Mammal
Visit Data].Visit) AS [Visit Field], First([UPFU Mammal Visit
Data].Date) AS [Date Field], First([UPFU Mammal Visit
Data].Start_time) AS [Start_time Field], First([UPFU Mammal Visit
Data].End_tIme) AS [End_tIme Field], Count([UPFU Mammal Visit
Data].Site) AS NumberOfDups, Max([UPFU Mammal Visit Data].ID) AS
MaxOfID
FROM [UPFU Mammal Visit Data]
GROUP BY [UPFU Mammal Visit Data].Site, [UPFU Mammal Visit Data].Day,
[UPFU Mammal Visit Data].Visit, [UPFU Mammal Visit Data].Date, [UPFU
Mammal Visit Data].Start_time, [UPFU Mammal Visit Data].End_tIme
HAVING (((Count([UPFU Mammal Visit Data].Site))>1) AND ((Count([UPFU
Mammal Visit Data].End_tIme))>1));

Delete Query:
DELETE [UPFU Mammal Visit Data].*, [UPFU Mammal Visit Data].ID
FROM [Find duplicates for UPFU Mammal Visit Data] INNER JOIN [UPFU
Mammal Visit Data] ON ([Find duplicates for UPFU Mammal Visit Data].
[Date Field] = [UPFU Mammal Visit Data].Date) AND ([Find duplicates
for UPFU Mammal Visit Data].[End_tIme Field] = [UPFU Mammal Visit
Data].End_tIme) AND ([Find duplicates for UPFU Mammal Visit Data].
[Start_time Field] = [UPFU Mammal Visit Data].Start_time) AND ([Find
duplicates for UPFU Mammal Visit Data].[Visit Field] = [UPFU Mammal
Visit Data].Visit) AND ([Find duplicates for UPFU Mammal Visit Data].
[Day Field] = [UPFU Mammal Visit Data].Day) AND ([Find duplicates for
UPFU Mammal Visit Data].[Site Field] = [UPFU Mammal Visit Data].Site)
WHERE ((([UPFU Mammal Visit Data].ID)=[Find duplicates for UPFU Mammal
Visit Data]![MaxOfID]));

Thanks in advance for any insight!

 

[KenSheridan via AccessMonster.com]

The reason for deletion of the 'duplicate' row being prevented is probably
because of the related rows in the referencing table, the enforcement of
referential integrity preventing the deletion while those rows exist. If you
did delete the row those rows would either be 'orphaned' and consequently
useless, or if cascade deletion is enforced would be deleted automatically
along with the row in the main referenced table.

The need to delete duplicate rows can be, and moreover should be, avoided by
creating a unique index on the set of columns which uniquely identify a row.
You are using 6 columns in the GROUP BY clause of your first query to do this,
but you may well need less. Once the index is created the user will not be
able to insert a duplicate row in the UPFU Mammal Visit Data table. To
recommence entering related rows in the subform all the user needs to do is
to navigate to the original record in the main form and insert more rows in
the subform.

The easiest way of creating the index is by making the two or more columns
the table's composite primary key, which you do in table design view by Ctrl-
clicking on each field, making sure you click on the field selector (the
little grey rectangle to the left of the field name), then right-click and
select 'Primary key' from the shortcut menu.

If you are already using another column as the primary key you can index the
fields uniquely by selecting indexes from the View menu. Enter a suitable
index name in one row of the left column, then enter all the column names on
separate rows of the Field Name column. With the first row (the one with the
index name) selected enter Yes as the 'Unique' property.

Ken Sheridan
Stafford, England

I have a form set up for data entry with a subform linking child
records in a different table. In the case where a user closes the
form halfway through entering child records then reopens it to finish
adding the child records, I don't want to keep the duplicate master
record they create when they reenter the information in the controls
bound to the master table. So I made a simple find duplicates query,
added a "MaxofID" field, and joined it to a delete query that should
delete the second instance of the duplicate record. I was going to
run this query whenever the form is closed. Unfortunately, I get an
error message saying Access is unable to delete the records from the
specified table. Any ideas? The help topic that pops up seems way
off, since it only discusses the possibility that the database is
opened as read only and I can manually delete the records from the
table with no problems. Here are the queries:

Find Duplicates Query:
SELECT First([UPFU Mammal Visit Data].Site) AS [Site Field], First
([UPFU Mammal Visit Data].Day) AS [Day Field], First([UPFU Mammal
Visit Data].Visit) AS [Visit Field], First([UPFU Mammal Visit
Data].Date) AS [Date Field], First([UPFU Mammal Visit
Data].Start_time) AS [Start_time Field], First([UPFU Mammal Visit
Data].End_tIme) AS [End_tIme Field], Count([UPFU Mammal Visit
Data].Site) AS NumberOfDups, Max([UPFU Mammal Visit Data].ID) AS
MaxOfID
FROM [UPFU Mammal Visit Data]
GROUP BY [UPFU Mammal Visit Data].Site, [UPFU Mammal Visit Data].Day,
[UPFU Mammal Visit Data].Visit, [UPFU Mammal Visit Data].Date, [UPFU
Mammal Visit Data].Start_time, [UPFU Mammal Visit Data].End_tIme
HAVING (((Count([UPFU Mammal Visit Data].Site))>1) AND ((Count([UPFU
Mammal Visit Data].End_tIme))>1));

Delete Query:
DELETE [UPFU Mammal Visit Data].*, [UPFU Mammal Visit Data].ID
FROM [Find duplicates for UPFU Mammal Visit Data] INNER JOIN [UPFU
Mammal Visit Data] ON ([Find duplicates for UPFU Mammal Visit Data].
[Date Field] = [UPFU Mammal Visit Data].Date) AND ([Find duplicates
for UPFU Mammal Visit Data].[End_tIme Field] = [UPFU Mammal Visit
Data].End_tIme) AND ([Find duplicates for UPFU Mammal Visit Data].
[Start_time Field] = [UPFU Mammal Visit Data].Start_time) AND ([Find
duplicates for UPFU Mammal Visit Data].[Visit Field] = [UPFU Mammal
Visit Data].Visit) AND ([Find duplicates for UPFU Mammal Visit Data].
[Day Field] = [UPFU Mammal Visit Data].Day) AND ([Find duplicates for
UPFU Mammal Visit Data].[Site Field] = [UPFU Mammal Visit Data].Site)
WHERE ((([UPFU Mammal Visit Data].ID)=[Find duplicates for UPFU Mammal
Visit Data]![MaxOfID]));

Thanks in advance for any insight!

 

[esn]

Thanks Ken, but I don't think that will work - the main form's "data
entry" property is set to yes, so users can't navigate to a previously
enetered record. I tried leaving the data entry property set to yes
for the subform and switching the main form to allow additions but not
edits or deletions, but this caused the subform not to display at
all. I'd prefer to keep it set up for data entry so that users don't
have to navigate to a new record to start entering data - they can
just start typing as soon as the form is opened.

Another thing I should have noted is that referential integrity isn't
enforced for the relationship between the master and child tables, so
that shouldn't be causing the error.

 

[John Spencer]

Your delete query won't work as it is structured. You need to only have one
table specified in the FROM clause. Construct a WHERE clause that identifies
which record you want to delete. Without trying to simplify, you should be
able to use the following.

Delete Query:
DELETE [UPFU Mammal Visit Data].*
FROM [UPFU Mammal Visit Data]
WHERE [UPFU Mammal Visit Data].ID IN
(SELECT [UPFU Mammal Visit Data].ID
FROM [Find duplicates for UPFU Mammal Visit Data]
INNER JOIN [UPFU Mammal Visit Data]
ON [Find duplicates for UPFU Mammal Visit Data].[Date Field]
= [UPFU Mammal Visit Data].Date
AND [Find duplicates for UPFU Mammal Visit Data].[End_tIme Field]
= [UPFU Mammal Visit Data].End_tIme
AND [Find duplicates for UPFU Mammal Visit Data].[Start_time Field]
= [UPFU Mammal Visit Data].Start_time
AND [Find duplicates for UPFU Mammal Visit Data].[Visit Field]
= [UPFU Mammal Visit Data].Visit
AND [Find duplicates for UPFU Mammal Visit Data].[Day Field]
= [UPFU Mammal Visit Data].Day
AND [Find duplicates for UPFU Mammal Visit Data].[Site Field]
= [UPFU Mammal Visit Data].Site
WHERE [UPFU Mammal Visit Data].ID)=
[Find duplicates for UPFU Mammal Visit Data]![MaxOfID])

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[KenSheridan via AccessMonster.com]

I would counsel very strongly against your current approach. Not only is the
potential for undermining the integrity of the database far too great, but
users are being forced re-enter data unnecessarily, with the consequent risk
of disparities between the original and the re-entered data, which means that
the rows are not 'duplicates', thereby negating what you are attempting to do.


You can achieve your ends with a conventional, and safe, approach:

1. Create a unique index on the set of columns within the table which, in
combination, must have distinct values. This prevents illegal 'duplicate'
rows being inserted into the table.

2. Enforce referential integrity between the tables. An unenforced
relationship serves no purpose other than the minor one of setting the
default join type for when a query on the two tables is created in design
view. It does leave the database wide open to orphan rows being created in
the referencing (many side) table, however. Enforcing the relationship on
the other hand prevents this.

3. Instead of opening the form in Data Entry mode do one of the following:

3.1 Open the form normally and in its Open event procedure move it to a new
record with:

DoCmd.GoToRecord acForm, Me.Name, acNewRec

The user can then navigate to an existing record if necessary. Ideally for
this you should include a means of selecting the existing record such as an
unbound navigational combo box with code in its AfterUpdate event procedure
to move to, or filter the form to the existing record. We can help with this
if you provide more information on the relevant fields in the main table by
which the record can be identified.

3.2. Open the form in Data Entry mode, but include a navigational combo box
as above, and include a line in the code for its AfterUpdate event procedure
to set the form's DataEntry property to False.

3.3 Open the form via an intervening dialogue form which gives the user the
choice of entering a new record or existing record, and if the latter is
selected enable a combo box on the dialogue form for selecting the existing
record to be opened.

Ken Sheridan
Stafford, England

 

[esn]

Thanks John - worked like a charm.

Ken - I agree with everything you've said and I plan on restructuring
as soon as I have all the data in the same place. Unfortunately right
now it exists in several different files of various formats on several
different computers in several states. I was just trying to throw
something together for the last bit of data entry that needs to be
done. When I do get my hands on everything I can eliminate all those
duplicate fields and set up a relationship based on a foreign key with
referential integrity, but before I get to that point there are a few
hoops that require jumping through.