Running Sum In Query

[BruceJackson]

Can a running sum field be created in a query?

I need to be able to create a field which adds 1 on for each new value. Eg:
900001
900002
900003
900004
900005

Users need to be able to specify a starting number such as "900001" and specify how many records required. In the example above, "5" records (or Start No plus 4 records).

Yes, No ?? Oh go on!

 

[scriverb]

Try this. The following requires the use of a table field designated as AutoNumber. If you don't have one already then create one in the table and name it RecCounter. If you have one already then substitute it's name for RecCounter throughout the code:

Copy and paste this SQL into a new query SQL window and save as qryRowNumberSelect:

SELECT TOP 1 (Select ([Enter Starting Number]-1) + Count(*) FROM [i][red]yourtablename[/red][/i] as Temp WHERE [Temp].[RecCounter]< A.[RecCounter]+1) AS RowNum, A.* FROM [i][red]yourtablename[/red][/i] as A;

Now use the following code behind a command button to execute the query:

Dim db as DAO.Database
Set db = CurrentDB
db.QueryDefs("qryRowNumberSelect").SQL = "Select TOP " & [How many records to select? ] & "(Select ([Enter Starting Number]-1) + Count(*) FROM [i][red]yourtablename[/red][/i] as Temp WHERE [Temp].[RecCounter]< A.[RecCounter]+1) AS RowNum, A.* FROM [i][red]yourtablename[/red][/i] as A;" 
db.close
DoCmd.OpenQuery "qryRowNumberSelect"

This should prompt the user twice. Once for the number of records to select and once for the starting rownumber sequence.




[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[BruceJackson]

I think I am guilty of trying to answer my own question in the question itself.

I will now try to explain myself clearly.

I have a form where Product Codes are input to a Product Code Table.

The codes are in the format A123456, A123457, A123458 etc. The table records are deleted prior to each time the user creates a batch of Product Codes.

What I would like to do is when the first code has been typed in, each subsequent code defaults to the next code.

Sorry Scriverb but would you like another go?

 

[Crowley16]

probably easier done in code rather than query...

dim idx as integer
for idx = 0 to NumRecs
docmd.runsql("INSERT INTO tblName(fields, ...) VALUES('" & "A" & idx & "', ...);"
next idx

 

[scriverb]

Wow, after such good work on the first one. Here is code to do what you need. This code is based on three fields on your form. RecordsToAdd, Alpha, and Numeric. Don't actually add a new record but just create these controls and then click a Command button with the following code in the OnClick event procedure:

Dim db as DAO.Database, rs as DAO.Recordset, i as Long
Dim vNumeric as Long, vRecordsToAdd as Long
vNumeric = Clng(Me.Numeric)
vRecordsToAdd = CLng(Me.RecordsToAdd)
Set db = CurrentDB
Docmd.RunSQL = "Delete * from [i][red]yourtablename[/red][/i];"
Set rs = db.OpenRecordset("[i][red]Product Code Table[/red][/i]", dbOpenDynaset)
Do
   rs.AddNew
   rs("Product Code") = Me.[Alpha] & CStr((vNumeric - 1) + i)
   rs.Update
   i = i + 1
Loop Until i = vRecordsToAdd
rs.close
db.close

Let me know if this is closer to your needs.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[BruceJackson]

Thanks Scriverb,

Tried it and fails, pointing to:

"db As DAO.Database"

in the de-bugger.

Do I need to create the fields RecordsToAdd, Alpha and Numeric in the Product table and if so what data type should they be?

 

[scriverb]

No you just need to make sure you database has made a reference to the appropriate DAO library. Open a module in design view and from the Tools menu select References. In this popup window you must have a reference to the MS DAO 3.X Object Library. If you are using Access 2k then select the 3.6 object library. 3.51 for A97.

Now run the code.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[BruceJackson]

I am using Access 2000

Now getting further but stopping at:

".RunSQL ="

?

 

[scriverb]

When it stops what is the error message? Did you update the correct table name? I only put this in in case you weren't already clearing the table of records. You can remove it if you have that already taken care of.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]