Numbering

[Hasan]

I am appending a table, in it field 3 is a number field
and increments by 1 for each record but i would like it
to rest to 0 each time field 2 changes, a bit like
grouping it. Any idea how i could do so.
Thank you

 

[Tom Ellison]

Dear Hasan:

It sounds like a simple "Group Ranking" is in order. If so, please
post a query that returns the columns you want and I can add the
ranking. This query must ORDER BY the column(s) that make(s) up your
"group".

Do the grouping columns form a unique index to the table? If so, have
you declared this to prevent duplication?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Hasan]

Insert into new_objh(objslip, objitem_no, objseq, objdesc)
Select objslip, objseq, objdesc
From objh
OrderBy objslip;

so i would like objitem_no to increment by 1 and each
time objslip changes the objitem_no starts with 0 and
then increments for all the objseq in that objslip

-----Original Message-----

 

[Tom Ellison]

Dear Hasan:

Within each set of rows where objslip does not change, the rows must
also be ordered. I'll take it that this is done by ordering by
objseq, since seq may stand for sequence. If this is correct, then
the ranked query would be:

SELECT objslip, objseq, objdesc,
(SELECT COUNT(*) FROM objh T1
WHERE T1.objslip = T.objslip AND T1.objseq < T.objseq) AS Rank
FROM objh T
ORDER BY objslip, objseq

How well this works may depend on whether objslip and objseq are
unique taken together.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts