How to write a query/macro to consolidate data in the same table, anddelete the duplicate one

C

CHANG

Hi,

I don't know if this is possible to use query/or macro to consolidate
data in the same table, and delete the duplicate one. My table looks
like below:

record [ID1], [ID2], [update_date], [start_date], [end_date], [notes]
1 A , 123 , 20080101 , 20070101 , 20091231 , manual
input
2 A , 123 , 20090101 , 20081201 , 20091031 , manual
input
3 A , 123 , 20100101 , 20091101 , 20100731 , manual
input
4 A , 123 , 20100801 , 20100101 , 20101231 , manual
input
5 A , 456 , 20100801 , 20100101 , 20101231 , manual
input

My goal is to consolidate the first 4 records to 1 at the max of
[update_date], which will take min of [start_date] and max of
[end_date], but select the records only when [ID1] is the same and
[ID2] is the same. Therefore the 5th record won't be changed.

record [ID1], [ID2], [update_date], [start_date], [end_date], [notes]
1 A , 123 , 20080101 , 20070101 , 20091231 , manual
input
2 A , 123 , 20090101 , 20081201 , 20091031 , manual
input
3 A , 123 , 20100101 , 20091101 , 20100731 , manual
input
4 A , 123 , 20100801 , 20070101 , 20101231 , manual
input <----
5 A , 456 , 20100801 , 20100101 , 20101231 , manual
input


Then delete the first 3 records, maybe somehow can use query to
identify the first 3 records, and replace txt in [notes] to * ? I have
a simple delete query to delete * in [notes] already.

record [ID1], [ID2], [update_date], [start_date], [end_date], [notes]
1 A , 123 , 20080101 , 20070101 , 20091231 ,
* <----
2 A , 123 , 20090101 , 20081201 , 20091031 ,
* <----
3 A , 123 , 20100101 , 20091101 , 20100731 ,
* <----
4 A , 123 , 20100801 , 20070101 , 20101231 , manual
input
5 A , 456 , 20100801 , 20100101 , 20101231 , manual
input

Could any one share the knowledge if it is doable?

Thank you,
Vincent
 
M

Marshall Barton

CHANG said:
I don't know if this is possible to use query/or macro to consolidate
data in the same table, and delete the duplicate one. My table looks
like below:

record [ID1], [ID2], [update_date], [start_date], [end_date], [notes]
1 A , 123 , 20080101 , 20070101 , 20091231 , manual
input
2 A , 123 , 20090101 , 20081201 , 20091031 , manual
input
3 A , 123 , 20100101 , 20091101 , 20100731 , manual
input
4 A , 123 , 20100801 , 20100101 , 20101231 , manual
input
5 A , 456 , 20100801 , 20100101 , 20101231 , manual
input

My goal is to consolidate the first 4 records to 1 at the max of
[update_date], which will take min of [start_date] and max of
[end_date], but select the records only when [ID1] is the same and
[ID2] is the same. Therefore the 5th record won't be changed.

record [ID1], [ID2], [update_date], [start_date], [end_date], [notes]
1 A , 123 , 20080101 , 20070101 , 20091231 , manual
input
2 A , 123 , 20090101 , 20081201 , 20091031 , manual
input
3 A , 123 , 20100101 , 20091101 , 20100731 , manual
input
4 A , 123 , 20100801 , 20070101 , 20101231 , manual
input <----
5 A , 456 , 20100801 , 20100101 , 20101231 , manual
input


Then delete the first 3 records, maybe somehow can use query to
identify the first 3 records, and replace txt in [notes] to * ? I have
a simple delete query to delete * in [notes] already.

record [ID1], [ID2], [update_date], [start_date], [end_date], [notes]
1 A , 123 , 20080101 , 20070101 , 20091231 ,
* <----
2 A , 123 , 20090101 , 20081201 , 20091031 ,
* <----
3 A , 123 , 20100101 , 20091101 , 20100731 ,
* <----
4 A , 123 , 20100801 , 20070101 , 20101231 , manual
input
5 A , 456 , 20100801 , 20100101 , 20101231 , manual
input
Click to expand...

I don't see how to do all that updating and deleting without
using some convoluted procedure. However, I think it may be
easy to get the desired dataset using a select query like:

SELECT Max(record) As LatestRecord,
ID1, ID2,
Max(update_date) As LatestUpdate,
Min(start_date) As EarliestStart,
Max(end_date) As LatestEnd,
Last(Notes) As SomeNotes
FROM mytable
GROUP BY ID1, ID2

Not sure about the Record and Notes fields.

If that returns the desired dataset, then I think I might
consider the approach of renaming the table and using the
above query as part of a Make Table or Append query to
populate a new table.
 
Top