Deleting duplicate records

[BadgerLikeSpeed]

Right, back again with another issue. I've tried googling, and have not been able to make sense of the information. In terms that a complete novice would understand, can anyone help with this?
I have a table ([WatCrew]) where the dates of crew on board a boat are entered, relevant fields are [ID] (which is autonumbered), [Person], [SignOn] and [SignOff]. Each week the crew enters the names of the people on board, as well as te dates on and off. Many of the crew stay on for more than 2 weeks, but sometimes they are moved around to another vessel before their expected sign off date. What this means is that I often end up with duplicate entries in the table where the [Person] and [SignOn] dates are the same, but the [SignOff] and autonumbered [ID] field are different. The last entered record for each person is the one I'd like to keep.
Enough background, now for the question...
What I'd like to do is have some code which deletes duplicate values in my table, but leaves the latest (highest numbered) record intact. Is this possible? I need this to run in the backround, asi don't want my users to be able to access my table. Once I have the code I intend to run it when the crew entry form is closed (not sure if that is at all relevant, or if anyone has any better ideas I'm open to suggestion!).
Any and every suggestion would be much appreciated...

 

[pwbrown]

You need to build a query or queries to do that. Then you just enter a line of code to execute the query at the point you mentioned.

 

[Rx_]

My kid is on watch duty and other duties at Kings Point. As a matter of record (ship log), do you really want to delete assignments? If you want a form or report to only show the "last one you would like to keep" that would be possible with the query. By keeping the other information, it would produce an audit trail of the assignments and transfers.

Was wondering if Microsoft Office Demo for Access had a free template. They did. It is new for Access 2010.
http://office.microsoft.com/en-us/c...=AC,&Version=14&qu=project template&Result=2#
In case the above doesn't work: Got to this demo at -
http://office.microsoft.com/en-us/templates
Then choose MS Access (in other) and search for "project template"
It was the first one in Access 2010 Web demo

My guess is that the Customer would be your Ship / Boat. This is set up to assign an Employee (or midshipman) with a Task to a Customer.
The reports are designed for current situation and per employee, per customer kind of thing.

Depending on your requirements, this might be an excellent template to copy and modify. The Table structure and queries will give you some ideas.

 

[BadgerLikeSpeed]

I decided that this was all far too complicated... Went for this instead:

If DCount("*", "CrewQ") > 0 Then
MsgBox ("Please edit sign off date")
Else

So now there's a query [CrewQ], and if the sign on/person combination already exists then the user is prompted to edit the dates... BUT... It still leaves the name and date in the form creating an entry with a sign on and name field, but no sign off date. So I've created a delet query that cleans up the table when the crew entry form is closed.

Private Sub Form_Close()
DoCmd.SetWarnings False
DoCmd.OpenQuery ("DelCrewBlank")
DoCmd.SetWarnings True
End Sub

 

[BadgerLikeSpeed]

Rx,
I'm only creating a database for the tugs to report their crewing to the office in a simpler way than they currently do, not for keeping the records on board, and also it's only editing the records if people sign off before their expected dates, if they were moved to another vessel for example. But thanks for raising th epoint...