Duplicate value & delete

[dede]

Hi
I will import some data from a texte file.(see below)
Some data are duplicate or more ==> by doing a duplicate query, Il will
retrieve all duplicate. They are duplicate only on the three first column,
Location are different.

The next step will be sort them and delete them but not the last Location
line.

I already do that in Excel by doing ligne by ligne (with a macro) but due
to the increasing data I will try to do it in Access !

Could you help me ?

ObjectID Batch PlateId Location 1 Location 2 Location 3 Location 4
0012265 03 B007986 1710_386 Cabinet_3 Shelf_35B D07
0012265 03 B007986 1710_386 Cabinet_3 Shelf_03B D07
0021904 04 B008053 1710_386 Cabinet_3 Shelf_37A A03
0021904 04 B008053 1710_386 Cabinet_3 Shelf_05A A03
0021904 04 B008053 1710_386 Cabinet_3 Shelf_05A A05

 

[KARL DEWEY]

sort them and delete them but not the last Location line.
What in the data can be used to determine which record is the 'last
Locationline'?
Are the records dated?

 

[dede]

Hi Karl
No I do not have any date, but the last location mean (due to the sort) the
last data with this objectID ==> the next line will have a over objectID

 

[KARL DEWEY]

but the last location mean (due to the sort)
What are you sorting on?
Can you restate this as I do not understand?

 

[dede]

Sorry the first data were not sorted ==>
With a Ascending Sort I will retrieve

ObjectID Batch PlateId Location 1 Location 2 Location 3 Location 4
0012265 03 B007986 1710_386 Cabinet_3 Shelf_03B D07
0012265 03 B007986 1710_386 Cabinet_3 Shelf_35B D07
0021904 04 B008053 1710_386 Cabinet_3 Shelf_05A A03
0021904 04 B008053 1710_386 Cabinet_3 Shelf_05A A05
0021904 04 B008053 1710_386 Cabinet_3 Shelf_37A A03

And as results I will need
ObjectID Batch PlateId Location 1 Location 2 Location 3 Location 4
0012265 03 B007986 1710_386 Cabinet_3 Shelf_35B D07
0021904 04 B008053 1710_386 Cabinet_3 Shelf_37A A03

==> All line containing the same (ObjectID, Batch & PlateID) will be deleted
only the last line will appear
(The over ones are Historic location, but due to a bug, in my query I
retrieve all data, and the only way to distinguish each over is the ascending
sorting)

 

[John Spencer]

In a relational database there is no way to get the answer unless you have
some means of specifying which record is the last based on the data in the
table. Records have no order.

Your example seems to imply that the Location 3 field could be used to
determine the order and therefore which record is last. Is that correct? I
would suspect not. The use of the phrase "historic location" would lead me to
think that there is a time component involved, but none of the fields you show
seems to have anything to do with time.

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

 

[dede]

Thanks John
I were thinking to this answer !!
==> Not really but it's true that location3 & 4 together will determine it

but I could try with only the location3, but How I have to do it ?

 

[John Spencer]

This MIGHT work. I don't have time to test.

SELECT *
FROM [SomeTable] as S
WHERE [Location 4] =
(SELECT Max([Location 3])
FROM [SomeTable] as T
WHERE T.[ObjectID] = S.[ObjectID]
AND T.Batch = S.Batch
AND T.PlateID = S.PlateId
AND T.[Location 1] = S.[Location 1]
AND T.[Location 2] = S.[location 2]
AND T.[Location 3] = S.[Location 3]
ORDER BY T.[Location 3] Desc, T.[Location 4] DESC)


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

 

[dede]

Hi John

Tooks me quite some time but looks not too bad,
I Still have two cases :
1)in the first cases I do not have any Location3 because Location1 is =
Removed (Location 2,3 and 4 is empty)
==> I resolved this problem by a doing a other query with only the removed one
2) in the second case I have to find the Max location 4 if Location 3 is
similair
ObjectID Batch PlateId Location 1 Location 2 Location 3 Location 4
0023727 03 B016053 1710_386 Cabinet_5 Shelf_68B A01
0023727 03 B016053 1710_386 Cabinet_5 Shelf_68B D02

Could I do the second case with the same query ? with a IIF ?

This MIGHT work. I don't have time to test.

SELECT *
FROM [SomeTable] as S
WHERE [Location 4] =
(SELECT Max([Location 3])
FROM [SomeTable] as T
WHERE T.[ObjectID] = S.[ObjectID]
AND T.Batch = S.Batch
AND T.PlateID = S.PlateId
AND T.[Location 1] = S.[Location 1]
AND T.[Location 2] = S.[location 2]
AND T.[Location 3] = S.[Location 3]
ORDER BY T.[Location 3] Desc, T.[Location 4] DESC)


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

Thanks John
I were thinking to this answer !!

==> Not really but it's true that location3 & 4 together will determine it

but I could try with only the location3, but How I have to do it ?

.