How to creating a running number in an access query

[zoldy]

Here is what I am trying to do if anyone can help I would appreciate it.

Let say these are my query results currently

part3 Desc3 Price3
part2 Desc2 Price2
part9 Desc9 Price9

note there is no significance to the numbers it is just an example...

Now what I want to create is a fourth feild that will display like this..

part3 Desc3 Price3 1
part2 Desc2 Price2 2
part9 Desc9 Price9 3

Basically counting the results and displaying the number

 

[DreamerZ]

While probably not very elegant, it works. Try this. It basically does what you want and places a number in another field based on how they are ordered in the query. The query is just a standard query. The code counts the number of projects and assigns that count number to the PriorityNumber field.

    'Update Priority Numbers in Projects Table
stDocName = "PrioritizationQuery"
intProjectCount = DCount([ID], "PrioritizationQuery") - 1

DoCmd.Close acForm, "PriorityScorecard", acSaveYes

DoCmd.OpenQuery stDocName, acViewNormal, acEdit

'MsgBox intProjectCount 'Testing

While intProjectCount > 1
    DoCmd.GoToControl ("PriorityNumber")
    DoCmd.GoToRecord acDataQuery, stDocName, acGoTo, intProjectCount
    SendKeys intProjectCount, True
    intProjectCount = intProjectCount - 1
        'MsgBox intProjectCount 'Testing
        'TempPriorityNumber.ControlSource = PriorityNumber
        
Wend
    DoCmd.GoToControl ("PriorityNumber")
    DoCmd.GoToRecord acDataQuery, stDocName, acGoTo, intProjectCount
    SendKeys intProjectCount, True
        '    MsgBox "At Record 1" Testing
    
    DoCmd.Close acQuery, "PrioritizationQuery", acSaveYes

Hope that helps.


DreamerZ
simplesolutions@prodigy.net
[ignore]

http://www.geocities.com/simplesolutions@prodigy.net

[/ignore]

 

[zoldy]

thank you so much for your reply.... the only question I have and this may be a dumb one but where do I insert this code... do I have to create a module for this?

 

[DreamerZ]

I put the code under a button on the form. That way when the user is finished updating information on the form, they click the button and the records are re-prioritized. Put the code in the OnClick section of the button. All that does go in a module.


DreamerZ
simplesolutions@prodigy.net
[ignore]

http://www.geocities.com/simplesolutions@prodigy.net

[/ignore]

 

[zoldy]

Sorry if I am not explaining this correctly but...

I am not using a form .... I have a query which I use to output results to a file. These results are then used elsewhere. The problem is I need to include that number in the results of the output file. Basically if the query has let say 10 records I want the fourth field to be as follows

1
2
3
4
5
6
7
8
9
10

 

[DreamerZ]

Where are you running the query from? How is it being initialized? At what point is it opened? It would be at that point you would put the code. So, if the query is run when the DB is opened, put the code there. If the query is run on a timed even, put the code there. Are you running the code manually from the Objects list? If so, you could create a macro that runs the code that opens and adjusts the query. Then, instead of running the query manually, you would run the macro.

In essence, the query is run somehow. That's where you would put the code.


DreamerZ
simplesolutions@prodigy.net
[ignore]

http://www.geocities.com/simplesolutions@prodigy.net

[/ignore]

 

[zoldy]

Ok yes I am running it from a macro... I just don't see how I can run this within a macro... sorry to be a pain but can you be very specific on how to do this..

Thanks

 

[DreamerZ]

Ack! That does change it a bit. You need to create a module (don't name it the same as the function). In the module, create the function with the code:

Function Count_Records()
...
End Function

In the macro, add the line RunCode with the Function Name the same as the function you created "Count_Records()" - with the parens.

When the macro is run, the function is run. So, the code is stored in the function. The function is stored in a module. The macro calls the code to run.

Hope that helps!


DreamerZ
simplesolutions@prodigy.net
[ignore]

http://www.geocities.com/simplesolutions@prodigy.net

[/ignore]

 

[zoldy]

Man I feel stupid but I can not get this to work...

my query name is qry_models_3 so placed this in your sample code and created a module.

then called it from the marco before calling the open query.

I get an error on [ID] which I assume is becuase I don't have a feild called ID .....

Not sure what to do from here...

 

[DreamerZ]

Someone gave me their code and it took awhile for me to get it to work too. I might have left out some important declarations. Here they are. Put them at the top of the function.

Dim stDocName As String
Dim stLinkCriteria As String
Dim intProjectCount As Integer

'Update Priority Numbers in Projects Table

'Name of the Query (yours is qry_models_3)
     stDocName = "qry_models_3"

'Integer variable to store the count number.  You might be able to put in stDocName instead of the query name since stDocName = the query name.  You need a field to count.  I used ID since ID is individual to each record.  Put a different field name in place of ID that's unique.
     intProjectCount = DCount([ID], "qry_models_3") - 1

'This line is unnecessary since you're not using a form
     'DoCmd.Close acForm, "PriorityScorecard", acSaveYes

'Opens the query in normal view to do all the numbering
     DoCmd.OpenQuery stDocName, acViewNormal, acEdit

'MsgBox intProjectCount 'Testing

'This is the actual count.  It says that while the count of the records produced by the query are greater than 1, do the commands.  The "PriorityNumber" is the field where you want to stored the count number from your table.
     While intProjectCount > 1
         DoCmd.GoToControl ("PriorityNumber")
         DoCmd.GoToRecord acDataQuery, stDocName, acGoTo, intProjectCount
         SendKeys intProjectCount, True
         intProjectCount = intProjectCount - 1
        'MsgBox intProjectCount 'Testing
        'TempPriorityNumber.ControlSource = PriorityNumber

'Once intProjectCount becomes zero (it's less than 1) This code is run.  It puts a 1 in the top record and closes and saves the query.  Again, replace "PriorityNumber" with the field name that you want to store the count number in your table.
Wend
    DoCmd.GoToControl ("PriorityNumber")
    DoCmd.GoToRecord acDataQuery, stDocName, acGoTo, intProjectCount
    SendKeys intProjectCount, True
        '    MsgBox "At Record 1" Testing
    
    DoCmd.Close acQuery, "qry_models_3", acSaveYes

The fields you need to change in the code to work with your table are the query name, the ID (which is the unique field for each record), and the "PriorityNumber" (which is the field to store the count number.

The PriorityNumber field will be blank when first run and when a new record is created. It's filled when the count code is run.



DreamerZ
simplesolutions@prodigy.net
[ignore]

http://www.geocities.com/simplesolutions@prodigy.net

[/ignore]

 

[rdodge]

Yeah, I have never known SQL itself to do any of the following:

A running total column

A counting column

Multiple row calculations that's NOT associated with grouping data.

For any of these tasks to be done, I had to run the SQL, take the data into something like a temp table, then use some other language (I.e. spreadsheet formulae or VBA coding) to manipulate the additional fields for however it needed to be manipulated.

Previously when I had to deal with the above situations, I dealt with getting data from our main DB Program, which I can only use a third party program for that, then have the query linked to an Excel spreadsheet (is not programmed to allow linking into Access), and then manipulate the data from there.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[zoldy]

ok getting somewhere now I get an error on this line

DoCmd.GoToRecord acDataQuery, stDocName, acGoTo, intProjectCount

The error is 2505
an expression in argument 4 has an invalid value

 

[DreamerZ]

That line says that in the DataQuery "qry_models_3" go to the last record number.

intProjectCount = the count of the unique records displayed by the query minus 1 to activate the last record.

I'm not sure where the invalid value is coming from. You might want to start opening up the MsgBox 'TESTING lines to make sure the variables are being recorded properly. For example, the first MsgBox ('MsgBox intProjectCount 'Testing) prior to the counting should tell you how many records the query displays. If that's null or wrong, then start there.


DreamerZ
simplesolutions@prodigy.net
[ignore]

http://www.geocities.com/simplesolutions@prodigy.net

[/ignore]

 

[zoldy]

the value is correct ... I can not enter the value manualy in that field prioritynumber maybe that's why I get the error. How should that field be defined to allow input?

 

[DreamerZ]

In the table it's just a number, long, with nothing else but the defaults. Default value is zero and the Diplay Control is a text box. Required and indexed are no. Everything else is blank.

In the query, it's just a field with no critera and no sorting.

DreamerZ
simplesolutions@prodigy.net
[ignore]

http://www.geocities.com/simplesolutions@prodigy.net

[/ignore]

 

[zoldy]

There is no table.... this is a field created on the fly used for export purposes only.... although even if I make a table I still get the same error...

not to second guess you because you have been so helpful but are you sure this is possible?

If I open the query I can not update the feild manualy so how is the code going to update that field?

 

[zoldy]

I found this code at microsoft and when used in the sample database it works fine and will give me what I want. However when I use this in my database my results display as #error ...

can someone tell me what this might mean

The field looks like this (I have replaced these field names with mine)

RunSum: fncRunSum([CategoryID], [UnitsInStock])

The code is as follows

Function fncRunSum(lngCatID As Long, lngUnits As Long) As Long
'Variables that retain their values.
Static lngID As Long
Static lngAmt As Long

If lngID <> lngCatID Then
'If the current ID does not match the last ID, then (re)initialize.
lngID = lngCatID
lngAmt = lngUnits
Else
'If the current ID matches the last, keep a running sum for the ID.
lngAmt = lngAmt + lngUnits
End If

'Pass the running sum back to the query.
fncRunSum = lngAmt
End Function

 

[rdodge]

Static keyword tells the code not to remove the variable once it has been established, even with it being in a procedure/function rather than in the global declaraction area of the module.

After your query has been updated, you would setup something like:

RST.MoveFirst
Do While RST.EOF = False
RST.Fields(&quot;fldRunningTotal&quot.Value = _
fncRunSum(RST.Fields(&quot;CatID&quot.Value,RST.Fields(&quot;Qty&quot.Value)
Loop

In this case, lngID would start out as 0 and lngAmt would start out as 0.

One problem I see with this method, how are you going to reset the variables to 0 after looping through the recordset since they are a procedure level variable. If they don't get reset back to 0 when you run this a second time, the results are going to be INCORRECT.

The only way I see this being possible is either the recordset record count get's passed to the function, so as the function can reset the variables (probably the better way), a reference of the recordset get passed to the function, or the procedure level variables are set as public global variables with the code resetting them to 0.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[DreamerZ]

The code does work in my application. We prioritize a lot of records with that query and code. It doesn't sound, however, that it's going to work for you. I'm not sure why. I can't duplicate the problems you have.

Hopefully someone else has a solution.


DreamerZ
simplesolutions@prodigy.net
[ignore]

http://www.geocities.com/simplesolutions@prodigy.net

[/ignore]

 

[zoldy]

in the sample database this code does exactly what I want .... However in my database I get the

#error

what does that mean ... is it a generic error?