Running Record Count

  • OzGrid / Et Al,

    I have in a database the "MANUFACTURING FLOW" of the products that my company makes. ( Sample Below ) What I need to do is be able, given a "Operation" number identify the previous op and the next 4 operations. Now the issue is that in this example Operation "6619" could be say the 100th operation yet in another product it could be the 150th.

    Now I know that there isn't exactly away to say, short of looping, of getting the record number. So that I can query and extract records X to Y. So what I did was create a query which ads another column "Counter" and it has the value of 1.

    Now I know that you can create running sums, but this is where I'm having problems. I want to create a query that sums the "Counter". Essentially there will be 3 queries ran in sequence.

    1. The first query creates the complete flow ( but the "Counter" fields all have 1 )
    2. The second query essentially redisplays the complete flow but now "Counter" field has now had a running sum done to it and it now looks like a "Record Sequence Order" field ( 1, 2, 3, 4, 5, . . . etc )
    3. The third query simply returns the "Sequence" number for a selected Operation.

    With this number then I can then select the desired records from the first query.

    [FONT="Courier New"]Route_ID Operation OpDesc
    EE-ZL-1 100 OXIDE -ZL
    EE-ZL-1 121 COAT -ZL
    EE-ZL-1 124 ZEXPO -ZL
    EE-ZL-1 125 DEVELOP-ZL
    EE-ZL-1 127 DI -ZL
    EE-ZL-1 129 STAGE -ZL
    EE-ZL-1 142 LAM ETH-ZL
    EE-HVNW-1 1557 DI -NW
    EE-HVNW-1 1559 STAGE -NW
    EE-HVNW-1 1561 UV BAKE-NW
    EE-HVNW-1 1562 WL IMPL-NW
    EE-HVNW-1 1563 PT IMPL-NW
    EE-HVNW-1 1566 PR STRP-NW
    EE-HVPW-1 1571 COAT -PW
    EE-HVPW-1 1574 EXPOSE -PW
    EE-HVPW-1 1577 DI -PW
    EE-HVPW-1 1579 STAGE -PW
    EE-HVPW-1 1582 WL IMP -PW
    EE-HVPW-1 1584 VT IMP -PW
    EE-HVPW-1 1586 PR STRP-PW
    EE-ARRAY-2 2111 COAT -ARR
    EE-BLANK-5 6569 STAGE -SIL
    EE-BLANK-5 6571 BLOX ET-SL
    EE-BLANK-5 6573 PR STRP-SL
    EE-BLANK-5 6619 SD ANNL-SL
    EE-TI-9 6620 TI DEP-SIL
    EE-TI-9 6627 1ST SIN-SL
    EE-TI-9 6630 TI STRP-SL
    EE-TI-9 6633 2ND SIN-SL
    EE-TI-9 6635 SILI PROBE
    EE-BPTS-14 7622 HDP DP -CN
    EE-ALLOY-1 9580 ALLOY -PAS
    EE-ETEST-2 9650 E-TEST -ET
    EE-ETEST-2 9800 SHIP[/FONT]

    Now if this don't beat all.

    Or would it just be best to take the query that I have ( as displayed above) and then "Filter" the recordset for the "Operation"?

    I'm going to assume that when the "Filtering" is done that the cursor will move to the active record and then I can just unfilter, moveprev,movenext,movenext,movenext,movenext,movenext.

  • Re: Running Record Count

    Still working on my first cup of coffee so I am not sure I am following exactly, but you could Filter the recordset as you mentioned and when you do so it does moves to the first record of the filtered recordset.