how change field in duplicate query?

[Ian Elliott]

Thanks for any help.
I am trying to change the date (to one day less of the second repeated
record) of a field in about 120 repeated records (repeating in two fields
different than the date field) in a table that has about 1600 records. It
would take about 10 minutes or so to do by hand, and I figured out a quick
way to do in Excel, but a query would be even nicer. I figured maybe an
update query based on a find duplicates query would work, but I am stumped.
I want to change the first (earlier date) record of the two records to one
day earlier than that of the second record.
Here's the table (an example of duplicates on field1 & field2):
field1,field2,field3,field4,field5,field6
A012E,001,01/01/2004,SAF,248,01/01/2100
A012E,001,12/19/2005,SAF,194,01/01/2100

I would like the query to change the 01/01/2100 of the first record
(field5=248) to one day less than 12/19/2005, ie 12/18/2005.

Sorry for the long explanation but any help much appreciated.
Thanks again.

 

[Lord Kelvan]

do you want to actually change the data or just display it
differentally

what about for the next record

if you have

A012E,001,01/01/2004,SAF,248,01/01/2100
A012E,001,12/19/2005,SAF,194,01/01/2100
A012E,001,12/30/2005,SAF,183,01/01/2100

do you want it to be

A012E,001,01/01/2004,SAF,248,12/18/2005
A012E,001,12/19/2005,SAF,194,12/30/2005
A012E,001,12/30/2005,SAF,183,01/01/2100

regards
Kelvan

 

[Ian Elliott]

Thanks for the quick response.
I would like to actually change the data.
I hadn't thought of a 3-replicant scenario-and I don't think there would be
one.

Thanks.

 

[Lord Kelvan]

well i have three queries and that will compensate for the thee or
more senerio if there ever is one



SELECT thetable.field1, thetable.field2, thetable.field3,
thetable.field4, thetable.field5, thetable.field6, (SELECT
Count(field1) +1 FROM thetable AS newthetable WHERE newthetable.field1
= thetable.field1 and newthetable.field2 = thetable.field2 and
newthetable.field3 < thetable.field3) AS recordnum
FROM thetable
ORDER BY thetable.field1, thetable.field2, thetable.field3;

save the above as
qrythetablecontrol

SELECT qrythetablecontrol.field1, qrythetablecontrol.field2,
qrythetablecontrol.field3, qrythetablecontrol.field4,
qrythetablecontrol.field5, IIf((select max(recordnum) from
qrythetablecontrol as newqrythetablecontrol where
newqrythetablecontrol.field1 = qrythetablecontrol.field1 and
newqrythetablecontrol.field2 = qrythetablecontrol.field2)=[recordnum],
[field6],(select [field3]-1 from qrythetablecontrol as
newqrythetablecontrol where newqrythetablecontrol.recordnum =
qrythetablecontrol.recordnum+1)) AS newfield6 INTO thetableprepdata
FROM qrythetablecontrol;

save the above as
qrythetableprepdata

UPDATE thetable INNER JOIN thetableprepdata ON (thetable.field5 =
thetableprepdata.field5) AND (thetable.field3 =
thetableprepdata.field3) AND (thetable.field4 =
thetableprepdata.field4) AND (thetable.field2 =
thetableprepdata.field2) AND (thetable.field1 =
thetableprepdata.field1) SET thetable.field6 = [newfield6];

save this one as whatever

you need to run the second one then the third one to update your table

since you didnt tell me your table name or field names i just used
field1 - field6 and thetable as the table name

if you are unable to convert it to your table structure then tell me
your tablename and field names and ill convert it for you

hope this helps

Regards
Kelvan

 

[Ian Elliott]

Wow thanks very much Lord Kelvan. I am embarrased to ask, since you have gone
to the trouble of helping me, but since I think the two scenario is the only
possible scenario, is it possible to have just one query? That would save a
little time and make the procedure less complex.
Thanks again for any help.

 

[Lord Kelvan]

no the first table sets up the ranking so the values can be compared
and the second table is to be used to move the data around to be
updated by createing a table FOR TEMPORARY PURPOSES ONLY to be used
for update because the thrid query you use for updating the value
cannot update based on a query that uses the table you are trying to
update.

Regards
Kelvan

 

[Ian Elliott]

Thanks alot Lord Kelvan! I will use that.