Assign number count to records

[DISI]

I would like to build a query that assigns a record number to each record displayed. i.e. The first record would have a record number "1", second record = "2" etc. Is there a way to do this in a query? Help would be appreciated.

 

[ghubbell]

Fast reply:If you are using this to output to a report, add a textbox in the report detail section. Control Source " =1 "
Set its Running Sum to OverAll. Gord
ghubbell@total.net

 

[JerryDennison]

Access doesn't work with record numbers per se. Since records can be ordered and filtered in any number of ways, record numbers as most are familiar with them have no real meaning. What exactly are you trying to do? The suggestion ghubbell gave will assign sequential row numbers to the output of a report but doesn't actually assign values to these records. So if you run the report again sorted a different way then you may have the same actual records with a different number beside them.

 

[MichaelRed]

Different response. Not related to the report thingggy.

You CAN generate a sequential number within a query. It does NOT work the way you would expect (at least for assigining record numbers). Previously dissceuused in Tek-Tips. To do it, you need to have a module with a static variable. Each "access" to a record in the query will - of course - invoke the procedure. The procedure increments the static variable and p[laces it in the recordId/number field. Thus 'scrolling' through the query 'refreshes' the Id Number - the values are Unique, but change and are no longer 'sequential' and almost never start w/ "1".


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[DISI]

Thanks for the quick response. I believe MichaelRed's suggestion might work, but let me explain why I want this and there may be a much easier way to my desired end result.

I have a table of patient pharmacy records. I need to calculate a running total on a numeric quantity field and determine when (the date) the quantity reaches a threshold. The fields in my table "RX" are: "ID", "date", "quantity".

Can anyone give me the syntax to calculate the running total. I tried the example from MS Access downloads but to no avail.

Thanks.

 

[MichaelRed]

Post a moderate/small sample of the data and I will generate the query. Please indicate any parameters (criteria) and the field which you want the 'sum' for.

A sample "results" would also be useful.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[DISI]

Here is a sample:

ID Date Quantity
1 1/1/99 10
1 2/15/99 12
1 3/18/99 5
2 1/5/99 7
2 5/1/99 8
2 5/21/99 10

I want to know the date when the running total of quantity for each patient ID reaches or exceeds 20. For patient #1 it would be on 2/15/99 and for patient #2 it would be 5/21/99.

Thanks Michael. I primarily use Access for data analysis and develop minimally so my VBA is quite limited. Your assistance is much appreciated.

Paul Faculjak
paul@dataintegritysolutions.com

 

[MichaelRed]

I don't see any direct way to do EXACTLY what you want in a query (at least not w/o using a module), so I modified the process a little. Also, your text description of the desired results "... reaches or exceeds 20 ... " does not correspond to the example ... #2 it would be 5/21/99 ... , so the module (arbitrairly) does it to match the example. To chaneg this, just add the "=" to the line (If MySum > 20 ... to read If MySum >= 20 ... )

The module just sets the "over20" field to true for the record where the quantity exceeds the threshold. To extract the record where this is occurs, run the procedure and then do a query aginst the table for the condition where the over20 field is set.

You need to sure that the table is ordered by the Id and the date fields to get the correct results.

Public Function basRunningSumByDate()

    'tblPatDose
    'ID     Date      Quantity
    '1      1/1/99       10
    '1      2/15/99      12
    '1      3/18/99      5
    '2      1/5/99       7
    '2      5/1/99       8
    '2      5/21/99      10

    Dim dbs As Database
    Dim rst As Recordset

    Dim MyPatient As Long   'Patient ID
    Dim MySum As Long       'Patient Sum
    Dim ExceedFlg As Boolean

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("tblPatDose", dbOpenDynaset)

    While Not (rst.EOF)
        ExceedFlg = False
        MyPatient = rst!Id      'Get a patient ID
        MySum = 0
        Do While rst!Id = MyPatient
            With rst
                MySum = MySum + !Quantity
                .Edit
                    If (MySum > 20 And Not ExceedFlg) Then
                        !Over20 = True
                        ExceedFlg = True
                     Else
                        !Over20 = False
                    End If
                .Update
            End With
            rst.MoveNext
            If (rst.EOF) Then
                Exit Function
            End If
        Loop
        Do While rst!Id = MyPatient
            rst.MoveNext
            If rst.EOF Then
                Exit Function
            End If
        Loop
    Wend

End Function

MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[DISI]

Thanks Michael,

I reproduced the sample table exactly from your/my example and built a module in Access 97 copying exactly what you wrote.

I get a "Runtime error: Item not found in this collection" and when I debug "!Over20 = False" is highlighted.

Now I may be too much of a novice to totally understand what to do with your code. I assumed I was to create the module and then run it. From there I was at a loss, but I did not get any further than the runtime error.

I tried the same thing in Access 2000 with different problems. I use both Access 97 and 2000 depending on the client. Will your code work in both?

Please excuse my ignorance and thank you for "holding my hand" on this one.

Paul Paul Faculjak
paul@DataIntegritySolutions.com

 

[MichaelRed]

Paul,

Sorry. I somehow left out the need to modify the table to include a field [Over20] as Yes/No.

This is the table, as I 'constructed' it - with the change to add the field [Over20]

[b]
Id	DoseDate	Quantity	Over20[/b]
1	1/1/99	        10	        No
1	2/15/99	        12	        Yes
1	3/18/99	        5	        No
2	1/5/99	        7	        No
2	5/1/99	        8	        No
2	5/21/99	        10	        Yes

If you add the field, I believe it should work.




MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[MichaelRed]

P.S.,

You may also need to change the table name. I just 'assigned' [tblPatDose] as the table name, as I did not notice a specific reference to the name you use for the table.

The "Code" should work in either '97 or 2K, it was 'written' in '97.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over