Update query Unique index and Order by Desc

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

StvJston - 15 Aug 2006 17:38 GMT

I have a User requriement to dynamically enter a line number in report
definitions and existing line numbers > new line number get incremented so
that all sections order correctly. (Cobol bleah!)

I am using a continuous subform to show report sections.

tblReportSections

RptSecID PK
RptID
SectionID long
SectionDescript

"TEST",0,"00Section"
"TEST",1,"01Section"
"TEST",2,"02Section"
"TEST",3,"03Section"
"TEST",4,"04Section"

Insert new section (1) and renumber existing greater than new

"TEST",0,"00Section"
"Test",1,"New 01Section" (New Entry)
"TEST",2,"01Section"
"TEST",3,"02Section"
"TEST",4,"03Section"
"TEST",5,"04Section"

I created a unique index on RptID and SectionID. Throws correct error 3022
from continuous form in form_error. Query below will work to update except
that the rows to be updated must be ordered Descending to not throw another
3022 error.

Is there a way to order the records to be updated in descending order so
that this will work?

UPDATE tblReportSection SET tblReportSection.SectionID = [SectionID]+1
WHERE (((tblReportSection.SectionID)>=[iRptSec]) AND
((tblReportSection.RptID)=[iRptID]));

Thanks in advance

Reply to this Message

John Spencer - 15 Aug 2006 18:25 GMT

How about running two queries sequentially?

first query adds 1 and then set the value to negative.
UPDATE tblReportSection
SET tblReportSection.SectionID = ([SectionID] +1) * -1
WHERE tblReportSection.SectionID>=[iRptSec] AND
tblReportSection.RptID=[iRptID]

2nd query then sets the negative values to positive values
UPDATE tblReportSection
SET tblReportSection.SectionID = [SectionId] *-1
WHERE tblReportSection.SectionID<0 AND
tblReportSection.RptID=[iRptID]

> I have a User requriement to dynamically enter a line number in report
> definitions and existing line numbers > new line number get incremented so
[quoted text clipped - 39 lines]
>
> Thanks in advance

Reply to this Message

StvJston - 15 Aug 2006 18:49 GMT

John your solution works perfectly.

I actually thought I'd found a way to do this with a self join for some
reason still got the 3022 error after updating the first row of the set.

UPDATE tblReportSection AS ts INNER JOIN [SELECT tblReportSection.SectionID,
tblReportSection.RpSectID,
tblReportSection.RptID
FROM tblReportSection
ORDER BY tblReportSection.SectionID DESC]. AS x ON (ts.RptID = x.RptID) AND
(ts.RpSectID = x.RpSectID) SET ts.SectionID = ts.SectionID+1
WHERE (((x.RptID)=[iRptID]) AND ((x.SectionID)>=[iRptSecID]));

I can wrap your solution in a transaction and all is well!

Thanks!

> How about running two queries sequentially?
>
[quoted text clipped - 53 lines]
> >
> > Thanks in advance

Reply to this Message

giorgio rancati - 15 Aug 2006 20:17 GMT

Hi StvJston,
try this
----
UPDATE (Select TOP 100 * From tblReportSections
Where RptID=[iRptID] AND SectionID>=[iRptSec]
Order By SectionID DESC) AS tbl
SET SectionID = [SectionID] +1
----

Bye

Signature

Giorgio Rancati
[Office Access MVP]

> I have a User requriement to dynamically enter a line number in report
> definitions and existing line numbers > new line number get incremented so
[quoted text clipped - 37 lines]
>
> Thanks in advance

Reply to this Message