Query to add a record count of a column AND reset on change of another field?

[bordway]

Hi,

I need a running count of one field AND reset on the change of anther field.
Examples below of current output and desired output.

My current query returns about 1700 records.
The columns/data looks like this:
JOB:............Part:.....Level:.....Seq
123:............3659........0.........3
123:............3672........0.........4
123:............0176........0........16
123:............1739........1........17
ZYX:............0271........1........18
ZYX:............3659........0.........3
ZYX:............3672........0.........4
ZYX:............0139........0.........7
QQQ:............0176........0........16
QQQ:............1739........1........17
QQQ:............0271........1........18

And this is what I really need.

JOB:............Part:.....Level:.....Seq.....RunnningCountOfSeq_ResetByJobValueChange
123:............3659........0.........3.........1
123:............3672........0.........4.........2
123:............0176........0........16.........3
123:............1739........1........17.........4
ZYX:............0271........1........18.........1
ZYX:............3659........0.........3.........2
ZYX:............3672........0.........4.........3
ZYX:............0139........0.........7.........4
QQQ:............0176........0........16.........1
QQQ:............1739........1........17.........2
QQQ:............0271........1........18.........3

A query would be ideal... rather than reports.
Since it is going to be used once or twice then discarded.


Thanks in advance



Bruce O

 

[maxxev]

Hi, i've been working on something like this, I can't get it to work in my crosstab but in a standard query it works.

Though I will point out that in my data ID is a unique value and from your data above i'm not sure you have a unique value in your data for this to work.
But perhaps it will give you some ideas.

Rank: (select count(*) from [Table] where [ID]<[TableAlias].[ID] AND [JOB]=[TableAlias].[Job]  ;)+1

Hope this helps.

 

[Andrzejek]

To align data, you may want to use [ignore] [pre] [/pre] tag:[/ignore]

[pre]
JOB Part Level Seq
123 3659 0 3
123 3672 0 4
123 0176 0 16
123 1739 1 17
ZYX 0271 1 18
ZYX 3659 0 3
ZYX 3672 0 4
ZYX 0139 0 7
QQQ 0176 0 16
QQQ 1739 1 17
QQQ 0271 1 18
[/pre]

Just FYI

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[bordway]

Thanks for the example.
I'll test this as soon as (if) I get back to my office



.......flights grounded yesterday due to the severe weather on the east coast.
.............still stuck at the airport
................. a few more hours & I should finally be out of here.


If your in the U.S. I hope your 4th is going well.
... in my case not off to a great start but... starting to look up.

Bruce O