Create a number sequence

[m kappler]

How to create a number sequence in a query that changes when a value in a certain field changes. For example:

ID City No Sequence

121 London.....1
121 Oslo.........2
121 Berln........3
122 Paris.........1
122 Rome........2
124 Moscow.....1
125 Madrid.......1
125 Madrid.......2

Have posted elsewhere. Can't seem to crack this one at all. Any help greatly appreciated

Kappler

 

[RichO]

You can easily do this in a report.

Using the report wizard you can set it up to group by any field (in your case ID).

Then in the detail section next to city, add an unbound text box, set its control source to =1 and set the Running Sum property to Over Group.

 

[schumarg]

Follow-up

Hi,

I've been searching for a similar answer to this problem.

I'm using the =1 to show line numbers, but when it's grouped the number starts over with the first line in the next group. Is there anyway to have it continue without starting over with "1".

Thanks,
Bob

 

[Pat Hartman]

Change the runningSum property for the control from OverGroup to OverAll.

 

[schumarg]

Followup

Hi,

I've done that. But it gives a running sequence to the end of the report. My report is about 20 - 22 pages and with each page I want the detail number to start at 1 again. Theres another catch, each page has 1 - 3 groups.

Suspect this needs to be done with code vs. a control property.

Thanks for your help, and should you know what code, would greatly appreciate your help.

Bob

 

[Pat Hartman]

There is no group over page. You'll need to code your own.