Delete query problem.

[J.Windebank]

I am trying to help a friend out with delete query he needs, but it is beyond what I can do.

Here is the table structure:

tblSomething
- ID (PK)
- RVersion
- RHost
- RUser
- RLocation
- RDateTime
- RCompleted

Now, the program that enters this data cannot be edited, so whenever the program enters some data, it makes "duplicate" type records.

For example:

ID, RVersion, RHost, RUser, RLocation, RDateTime, RCompleted
1,AIDA32 v3.40,TECHS,pmurray,NOT IMPLEMENTED,14/5/03 15:18:00,1
2,AIDA32 v3.40,TECHS,pmurray,NOT IMPLEMENTED,14/5/03 15:21:00,1
3,AIDA32 v3.40,C115,jeff,NOT IMPLEMENTED,14/5/03 15:24:00,1
4,AIDA32 v3.40,C115,jeff,NOT IMPLEMENTED,14/5/03 15:25:00,1

Now, what I need to do is create a delete query that will only keep the most recent log file for each RHost.

So in the previous example, we want to only keep:

2,AIDA32 v3.40,TECHS,pmurray,NOT IMPLEMENTED,14/5/03 15:21:00,1
4,AIDA32 v3.40,C115,jeff,NOT IMPLEMENTED,14/5/03 15:25:00,1

Can anyone help?

Thanks

Edit: Ooops, thought he wanted one record per day for each host, but it turns out only to be one record per host full stop.

 

[crosmill]

Create a query that groups all the fileds except the ID and the Date and any others that may be different.
Make a new table from this query.
Add all the columns that were not included, onto the new table.
Match the new table with the old table on all columns that have been populated in the new table.
Append the ID, date etc to the new table.

This will give you unique data in the new table.

If you want to have the latest entry, you can order the append query by date or highest ID.

You can then renmae your new table to the old table name, but make sure all the table properties are the same, esspecially the autonumber.

I know you know, (everyone does, and everyone does it. honest.) but back up your db first.