|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
April 8th, 2010, 10:36 AM
posted to microsoft.public.access.queries
|
|||
|
|||
Duplicate value & delete
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 
|
|
#2
April 8th, 2010, 04:09 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Duplicate value & delete
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? -- Build a little, test a little. "dede" wrote: 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
|
|
#3
April 9th, 2010, 01:04 AM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Duplicate value & delete
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" wrote: 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? -- Build a little, test a little. "dede" wrote: 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
|
|
#4
April 9th, 2010, 03:05 AM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Duplicate value & delete
but the last location mean (due to the sort)
What are you sorting on? the last data with this objectID == the next line will have a over objectID Can you restate this as I do not understand? -- Build a little, test a little. "dede" wrote: 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" wrote: 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? -- Build a little, test a little. "dede" wrote: 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
|
|
#5
April 9th, 2010, 09:11 AM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Duplicate value & delete
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) "KARL DEWEY" wrote: but the last location mean (due to the sort) What are you sorting on? the last data with this objectID == the next line will have a over objectID Can you restate this as I do not understand? -- Build a little, test a little. "dede" wrote: 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" wrote: 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? -- Build a little, test a little. "dede" wrote: 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
|
|
#6
April 9th, 2010, 04:38 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Duplicate value & delete
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 wrote: 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) "KARL DEWEY" wrote: but the last location mean (due to the sort) What are you sorting on? the last data with this objectID == the next line will have a over objectID Can you restate this as I do not understand?
|
|
#7
April 9th, 2010, 05:55 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Duplicate value & delete
Thanks John
I were thinking to this answer !! the Location 3 field could be used to determine the order and therefore which record is last. Is that correct? == 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" wrote: 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 wrote: 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) "KARL DEWEY" wrote: but the last location mean (due to the sort) What are you sorting on? the last data with this objectID == the next line will have a over objectID Can you restate this as I do not understand? .
|
|
#8
April 9th, 2010, 06:37 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Duplicate value & delete
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 wrote: Thanks John I were thinking to this answer !! the Location 3 field could be used to determine the order and therefore which record is last. Is that correct? == 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" wrote: 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 wrote: 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) "KARL DEWEY" wrote: but the last location mean (due to the sort) What are you sorting on? the last data with this objectID == the next line will have a over objectID Can you restate this as I do not understand? .
|
|
#9
April 11th, 2010, 09:36 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Duplicate value & delete
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 ? "John Spencer" wrote: 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 wrote: Thanks John I were thinking to this answer !! the Location 3 field could be used to determine the order and therefore which record is last. Is that correct? == 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" wrote: 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 wrote: 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) "KARL DEWEY" wrote: but the last location mean (due to the sort) What are you sorting on? the last data with this objectID == the next line will have a over objectID Can you restate this as I do not understand? . .
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
