Adding Sequential Numbering To A Query


Just trying to figure out how I can run a select query on some data I have and how to also include an additional field which has a value counting the number of rows.... eg in the first row there would be the usual results and the new field would have a value of 10001, second row would be 10002, third would be 10003.

I've done some searches for sequential numbering but couldnt find anything so far.

Thanks, j

View Replies


Sequential Numbering Of Query

I need to generate line numbers for an access query. This query is run via VB code. After it runs, a Dlookup is performed to search for the line number of a particular order detail id. When it finds it, it puts the line number along with other unique order detail information into a text file to be used by a computerized saw that cuts the parts out. Here's a sample query:

DetailID SortID Frmlgnth Stilelgnth OrderID SeqNo

The query is supposed to be sorted with SortID Descending, Frmlgth Ascending, Stilelgth Ascending - in that order. The sort is working fine. However, the Sequential numbering is not. The OrderdetailID has NO bearing on the sort order. Here is my SQL code:

SELECT OrderDetails.OrderDetailID, ProductTypes.ProductTypeSortOrder, OrderDetails.FrameStileLengthActual, OrderDetails.FrameRailLengthActual, OrderDetails.OrderID, (SELECT count(*) FROM OrderDetails As x WHERE x.orderdetailid <= OrderDetails.orderdetailid AND x.orderid = orderdetails.orderid) AS SeqNo
FROM OrderDetails INNER JOIN ProductTypes ON OrderDetails.ProductType = ProductTypes.ProductType
GROUP BY OrderDetails.OrderDetailID, ProductTypes.ProductTypeSortOrder, OrderDetails.FrameStileLengthActual, OrderDetails.FrameRailLengthActual, OrderDetails.OrderID
HAVING (((OrderDetails.OrderID)=[forms]![orders]![orderid]))
ORDER BY ProductTypes.ProductTypeSortOrder, OrderDetails.FrameStileLengthActual DESC , OrderDetails.FrameRailLengthActual DESC;

Does anyone have any suggestions? I'm not too good with SQL, but I can figure it out (I think!). I've searched all the formus, but all the reference I can find regarding this don't work for me in my application...

I greatly appreciate any help you can give!

View Replies View Related

Sequential Numbering (HELP)

I have a table (tbl_Forms) which contains the following columns
ID, Form Number, Descrip, Next Number to Print

First Issue.
I have a form that based on the above table and includes an unbound text box labeled forms requested.
I need the next number to print field to increment by the number put into the forms requested text box. I also need this value to be stored in the tbl_forms table once the printing is complete. So that the next time the form is printed this number is show.

Second Issue.
I need the sequential numbers to print of the bottom of the pages.

I need some serious help

Thanks in advance

View Replies View Related

Sequential Numbering

Shall be grateful for clarifications for these two :

1. I have two fields - TheYear (set to take the current year) and another for sequential numbering. In fact I created this to replace the autonumber field. As suggested by a member I created a Generate button with the criteria as under

Private Sub Generate_Click()
If IsNull(Me![NumFld]) Then
Me![NumFld] = Format(Nz(DMax("[NumFld]", "[DiaryTable]", "[TheYear]='" & Year(Date) & "'"), 0) + 1, "00000")
End If
Me![NumFld] = Format([NumFld], "00000")

End Sub

It works fine. and when the year is changed, the numbering starts from 1 again. My question is how do I make it to auto generate the number so that the user doesnt have to click the Generate button everytime to get the number. Suggestions please.

2. Is it possible to change this sequential numbering midway i.e. to start from a different number and increment by one?

Grateful for help

View Replies View Related

Sequential Numbering With A Difference!

I have an existing table with a field labelled Job Number (17 Job Numbers). Each month I import a new table and match each record via another field (serial number). For each new record, one or many, I want to attach a sequential Job Number. In this example the new job numbers need to start at 18 then 19 etc. How is this possible? I am fairly new to Access so please be gentle!. Cheers

View Replies View Related

Sequential Numbering Within Groups

Hi all,

Simple question (I hope)...

Need to devise a way of updating a field with an ascending sequential number within a group of records of similar type.


Say I have records which consists of numerous IDs and various job descriptions within the each ID

13000 Head of Maths 1
13000 School Secretary 1
13000 Head of Maths 2
13000 Head of Maths 3
13000 School Secretary 2
14000 Head of Maths 1

So in this case each 'Head of Maths' and 'School Secretary' sequentially increments until there is a new ID.

Any help appreciated.


View Replies View Related

Re - Sequential Numbering - Version: 2007 (12.0)

I am pretty new to access and programming.

I am building a customer database from where I could also do my billing.

I would like to generate the bill numbers automatically while I enter billing information, based on the company from which i would like to do the billing.

So, say if the last bill entered for Company 1 is number 3, when I enter a new bill for the company the new bill number should automatically be sequenced to bill number 4.

I would like to know how I could do that.

I went thru this article but it doesn't seem to work.

View Replies View Related

Sequential Numbering - Version: 2002 (10.0) XP

This is bound to be really straight forward but i can't work it out.

All i want to do is have a text box on my form create a sequential number each time a new task is created. I then want that number to be recorded in a field called ComputerNo in a table.

Any advice would be most welcome.


View Replies View Related

Sequential Numbering Macro - Version: 2002 (10.0) XP

I have a form called AddTransaction based on a table called tbl_Transaction. The table has three fields TransactionID(PK/autonumber), TransactionNo(#), ProjectID(#), among others. I want the TransactionNo field to record new transactions, starting from number 1, based on the ProjectID. So when I add a transaction it assigns the next sequential number, for itís ProjectID. If a new ProjectID is added, the TransactionNo should start with number 1. The TransactionNo for each ProjectID starts with 1.

Iíve tried using the macro below, which I adapted from another posting (, but nothing happens. No number appears on the table.

Nz(DMax("[TransactionNo]","Tbl_Transactions","[ProjectID] ='&[ProjectID]&'"),0)+1

Would greatly appreciate any help.

View Replies View Related

Sequential Numbering Without Autonumber - Version: 2002 (10.0) XP

I have addressed this question before and here I am again. I went back to some old posts and tried to make this work, but ran into problems. Here is my requirements:

1. User Form with auto-update field that sequentially puts the next number in after the last number.
2. Have the number begin at 1 again as the year changes

The following fields are identified in the Table

CC# (long integer)
CCYear (formatted yyyy)

I entered the following code in the database itself

Public Function GetNextCC()
GetNextCC = Nz(DMax("CC#", "CCNumber=" & Year(Date)), 0) + 1
End Function

I entered the following code in the form properties
Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me![CC#]) Then
Me![CC#] = GetNextCC()
End If
End Sub

This isn't working. I open the form in add mode and the field is empty. When I enter the first field Document Number the field stays blank. When I try to save the record (field is still empty), it give me an error that the form has an error. I've only changed the form by adding the code above. When the form was autonumber it worked fine; however, i had to create another database for the next year.

Can someone please tell me what I'm doing wrong.

Thanks in advance.

View Replies View Related

Sequential Numbering To Correlate With Specific Fields - Version: 2003 (11.0)

I have created a database that allows construction superintendents to create field work orders(FWO). Each project we have has a company designated project number that should be on each FWO. Each FWO for that project should also have a number, preferably sequential. Example: Project 2008-0010, FWO 2008-0010-01, 2008-0010-02, etc.

How can I make a field in the table that combines the project number with a sequential number? Bear in mind the database has numerous projects and numerous FWO's, so auto number doesn't work unless I creates separate FWO tables for each project.

View Replies View Related

Sequential Numbering Tied To Dates - Design Question - Version: 2002 (10.0) XP

hello all,
Thinking about how to phrase this question led me to solve most of it myself, but I think i might have ended up down the slightly wrong path.

I have a First Aid DB that lists encounters. I've been asked to change that to a number that in some way ties in game ID and encounter # (so, the 3rd patient at the 5th game is ID 5-3 or something like that )
I have my data entry form do a DMAX for encounter number, and it looks up the GameID from the GameID table, and it concatenates GameID and Encounter ID.

(now, here's the table/relationship part)

the DMAX uses date as the criteria. GameIDs are stored in a seperate table and the concatenated value is stored as the Encounter ID in the table of Encounters.
The problem that arose: In the event of a partial game that is rained out, technically the first 2 innings of that game are game 5, and when the game is replayed for the full 9 innings, that is ALSO game 5. So, this would lead to there being 2 encounters numbered 501

It seems like the only way to fix this is to store game ID with the record, and let DMAX use that as the criteria, but then I'm storing the concatenated encounter ID and the Game ID which seems duplicative.

basically, if you can understand all my description, should I just add GameID to the table? or am I missing a simple tidy solution to this?

Thanks in advance,

View Replies View Related

Sequential Number Created By A Query URGENT

I am trying to create a sequential number field in a query. I cant use an auto number for the fact that i am combining multiple tables. Any help with this would be greatly appreciated.

View Replies View Related

Numbering In A Query

I want to distribute certain materials (found in a query) to several warehouse locations in a sequential order.

So the first found material needs to be put in warehouse location 001,
the second found material needs to be put in warehouse location 002,

How can I number the lines in a query?

I donít want to run a create-table-query with an auto number field because the warehouse locations are limited and every time the query runs the numbering has to start at 001.

View Replies View Related

Numbering In An Query

I would like to create an expression that would number each item in sequential order in a query .
I cannot use autonumber on the table because I am pulling it from SQL and do not have clearance to change the actual tables.
I have been searching through this forum for the last 2 hours and have not come across anything similar to this, so I hope I'm not covering a topic that has already been covered.

Any help will be greatly appreciated.


View Replies View Related

Numbering Records In Query

Hello, I have a question. I don't really know much about access, but I am really comforatable with SQL (DB2 and MySQL Programmer). Anyways, I was wondering if there is any way to do the following in SQL.

I have a Students Table that holds the names of students, an applications table that holds different applications, and an offers table that holds offers for the different applications.

The relationships for the tables are :
One Student Has Many Applications, One Application has Many Offers

IE: One Application can have up to three offers attached to it. I have the following query running to get all of the offers for all students in a specified term:

SELECT DISTINCT Offers.tblStudentProgCodeFK, Names.StudentID, Names.FName, Names.LName, (Offers.Code), tblPrograms.ProgName, Names.[Country Of Birth], Offers.[OCAS Number]
FROM qryApplications_Offers AS Offers, [Names], tblPrograms, (SELECT DISTINCT StartingDate, EndingDate FROM StartDates WHERE Term LIKE "*"+[Forms]![frmSelector]![Term]+"*") AS B
WHERE (Names.StudentID = Offers.StudentID AND (tblPrograms.ProgCode = Offers.Code OR Offers.Code=0) )
(Offers.StartDate BETWEEN B.StartingDate AND B.EndingDate);

What I was wondering is if there is any way to add a field in the output that would number the selections. Right now it returns a table like:

tblFK StdID Code
34440 20394 0112
34440 20394 0123
34440 20394 0234
34234 25847 0100
47364 34857 0111
47364 34857 0311

I would like to do something like this:

tblFK StdID Code Choice
34440 20394 0112 0
34440 20394 0123 1
34440 20394 0234 2
34234 25847 0100 0
47364 34857 0111 0
47364 34857 0311 1

Where the choice column would count the choice for that student. Is this even possible? There is no choice number in the Applications or Offers table so I would need to do this in the query....

Any help would be appreciated

View Replies View Related

Numbering Lines In A Query

How do I number the lines in a query? I have a 'Top 50' query, and I want to number the lines 1 through 50.

View Replies View Related

Auto Numbering A Select Query -

Can this be done ??

I have a query on a timer that basically looks at a linked table every minute and tells me whose in the database. I want to number the rows in a column so I can reference this to a form that has 7 spaces. The number of 'users' could vary from 0 - 23, although this will soon be limited to 7 (hence the 7 spaces) to aid performance and speed. I am then going to reference the number to the unbound space on the form, iif(isnull()) 'ing the blank cells so it isnt an error.

I thought of an append query, but the sequence will always start at the next number, not from 1 each time - or as far as im aware it will - so I cant reference the cell then.

Can any one help please.

View Replies View Related

Sequentially Numbering The Records In A Query

I need to add a column to a sorted query which effectively numbers from 1 to N. It is intended as a ranking field for later statistical analysis.

I can do this manually by saving the query as a table, then introducing a new autonumber field.

However, I need to do this automatically, as this is just one query out of many in a large and complex setup. Is it possible to add an autonumber field to a query?

View Replies View Related

Numbering The Records Of A Query - Version: 2000 (9.0)

Hi folks,
I'm using the code module below to create a extra column which will number, in serial order, the records produced by my query. The problem is that when it creates the number list and the list reaches a record in the reference field which is repeated, it repeats the number used when the record was first encountered, like shown below. How can I edit the code or correct the issue so that number continue in serial order '1 thru 6'.

ConstMon No.
Jan 1
Feb 2
Mar 3
Apr 4
Feb 2
Mar 3
Jan 1

Function Serialize(qryname As String, keyname As String, keyvalue) As Long
On Error GoTo Err_Serialize
Dim db As Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset(qryname, dbOpenDynaset, dbReadOnly)
rs.FindFirst Application.BuildCriteria(keyname, rs.Fields(keyname).Type, keyvalue)
Serialize = Nz(rs.AbsolutePosition, -1) + 1
Set rs = Nothing
Set db = Nothing
End Function

SELECT QryDiscChart7.ConstMon, Serialize("QryDiscChart12","ConstMon",[ConstMon]) AS Expr1
FROM QryDiscChart7;

Thanks in advance.

View Replies View Related

Sequential Ranges

I need a form to set up records

The table is something like

AutoID, Sequence , Low, High

Data would be

1, 1, 1 499


Ho do I go about ensuring my sequences are correct

ie I wouldn't be able to add a record - 5,5,100,200

or if a user was to delete a record the user would be informed the sequences were out?

Thanks for any assistance.

View Replies View Related

Sequential Dates

Hi, Newbie Question,
what can I include in my code to prevent a user from entering a date into a textbox (via code via combo boxes)
that is an earlier date than the last or latest date field record entry. The two questions I s'pose I'm asking are how do I reference that
last or latest Record (which maybe the user just inputted via the form) & how do I approach the code. The user will be inputting data
for his club members for a monthly sporting event & I'm trying to prevent him accidentally entering an earlier date than the last meet.

View Replies View Related

Sequential Number

Hello, I am very new to access and I am trying to create a simple sytem to help us create part numbers. We are using a 2 digit descriptor to help define the part some. Then we want to add a sequential 7 digit number on to that.


01 (is electrical)
02 (Shop supplies)


01-0000001 would be a part number for an electrical part.

I do not understand how to have access add the zeros to the front. I would also like to be able to jump a bit out of line to reserve part numbers for certain projects and also include some that we already have.

If this make any sense to anyone, any help would be appreciated!


View Replies View Related

Assigning Sequential Numbers.

Yo there - thought Id fire a question out regardin numbering:

I have a table players and a table team.

Players sign up to teams, and I have a report which shows each team, and the players underneath.

Player 1
Player 2

Now, after all the players are assigned to a team, I want to assign each player within each team a random number, sequential.

So when I run the report, it will be like:

Team A
1. Player 1
2. Player 2

Team B
1. Player 1
2. Player 2

Any pointers!?

Cheeas -

View Replies View Related

Sequential Number Generation

Hi guys, im new to all this so take it easy

Im trying to create a document register in Access 97 to create and track document numbers, for this i want to assign a 6 number unique id to each record starting at 000001 going up in order to 999999 which will form part of a unique docuement number ...i know this may be a really easy thing to do but can anyone tell me how?



View Replies View Related

Returning A Sequential Number

I have a form on which I need to print a sequential number - increasing by 1 each time the form is printed. I cannot think of a way to do that - perhaps something returned from a query? Any suggestions?


View Replies View Related

DMax() For Sequential Autonumber

Hello Access friends,
Trying to have a sequential autonumber for the ScreenID with the DMax () function.
Please advise on what is wrong with the following :
=Nz(DMax("[ScreenID]","[Screenprep]","[ScreenID] = '" & [CarModel] & "-" & Left$([Category],1) & "'")+1,0)
Neither putting this code in control source or beforeupdate event of the form is not working.

I have looked around and from previous posts in the forum come up with this module. But it is not working either:
Public Function NewScreenID() As String

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim ScreenID As String
Dim CarModel As String
Dim Category As String

On Error GoTo Err_Execute

Set db = CurrentDb()

Set rst = CurrentDb.OpenRecordset("SELECT Max([Screenprep].[ScreenID]) AS MaxScreenID from [Screenprep];", dbOpenSnapshot)

If IsNull(rst!MaxScreenID) Then
NewScreenID = [CarModel] & "-" & Left$([Category], 1) & Format(1, "0000")
NewScreenID = rst!MaxScreenID + 1
End If

Set rst = Nothing
Set db = Nothing

NewScreenID = ScreenID

Exit Function

'An error occurred, return blank string
NewScreenID = ""
MsgBox "An error occurred while trying to determine the next sequential number to assign."

End Function

In advance thank you for your time.Can someone please guide me on how to sort this out?

View Replies View Related

Sequential Number Problem

I've tried using DMax codes shared by the generous members of this group. But i can't get the results i needed. :-(

My table consists of the following fields:

ColID - PK
PolID - FK

I added another field which i named "ColNo" - this field should assign a sequential number to a new payment record for a specific PolID.

My table looks like this:

ColID PolID AmountPaid ORDate
11125 123 2,000 10-Sep-06
11126 123 2,000 10-Oct-06
11127 123 2,000 10-Nov-06
11128 456 1,000 8-Aug-06
11129 456 1,000 8-Sep-06
11130 789 4,000 21-Aug-06

Again, my idea is to create a sequential number for the ColNo field:

ColID PolID AmountPaid ORDate ColNo
11125 123 2,000 10-Sep-06 1
11126 123 2,000 10-Oct-06 2
11127 123 2,000 10-Nov-06 3
11128 456 1,000 8-Aug-06 1
11129 456 1,000 8-Sep-06 2
11130 789 4,000 21-Aug-06 1

I placed the code Nz{DMax("field name","table name"),0)+1 on the control source of the ColNo field and i also tried copying numerous similar codes to the BeforeInsert, OnCurrent, etc.. events, but the result is ALWAYS THE NEXT MAXIMUM NUMBERFOR THE "ENTIRE" POLID.

What i mean is, if i enter a new record, it would give me a sequential number of "4". I figured out this is because 3 is the last maximum number as i have shown on my sample table. Even if i use the PolID "789", the number shown on the ColNo field results to "4".

IT SHOULD HAVE BEEN "2". Considering that the last ColNo used for PolID 456 was "1".

Hope someone could help me on this..


View Replies View Related

Unique Sequential Numbers


I have a field called "Project #" that needs to generate a unique number each time a command button is clicked. For example, when the button is clicked the blank "Project #" field is filled in with "653001". After the "Project #" field is filled in the button also runs a code to create a report exported into Excel. This report will only have the records with the Project Number of "653001".
Then, when more records are added and it's the end of the next week, the button is clicked again and the "new" blank "Project #" fields will be filled with the next Project number which would be "643002". A report will again be created but now with only the records with the Project number of "643002".
The first three characters will always be the same for the Project Number "653" but the last three will increase whenever the button is clicked to update any new records that have a blank "Project #". So, it should look like: 653001, 653002, 653003, 653004, etc.

I know it sounds confusing but I hope someone can help.


View Replies View Related

Access Sequential Number

Hi, im pretty new to access, i need some help or a pointer on how to set up a sequential number. i have a orders table with an order number starting GO how to i get access to count up in ones from the starting number.

View Replies View Related

Problem With Sequential Fields In A Report

I'm making a database that has to do with ski lifts. There is a report I'm trying to make about some safety equipment, the way the boss wants it structured with six consecutive text boxes, like this:

[Function][location][check box][Function][location][check box]

Now the text boxes are filled with values from a table. The problem is that if you put two text boxes side by side it will just display the same values. I was wondering if there is a way to get it to display consecutive values from the table even if they are side by side.

View Replies View Related

Sequential Order Number With Alphanumeric Value

Dear All,

I know that there may have been many posts on this and I have searched for a total of about 2 hours in these forums before posting, so please don't shoot me down for asking this.

I simply need to know how I can create a unique, alphanumeric order number. I was going to use Autonumber, but from what I have read on these forums, that is not the way to go, as Autonumber is simply to identify the record.

What I need is an number like so: ST000. It needs to start at ST142 and increase by one each time. Obviously I would like to avoid gaps from records being deleted or not completed etc.....

I really have no clue how to do this, and would appreciate it if anyone can point me to a post that would help, or offer me the solution.


View Replies View Related

Tab Controls On Subform For Sequential Records

I have a form with 3 tabs...each tab has the same subform embedded in it.

There are 3 sequential records in the database, and my desire is to have each record display on its' proper tab.

How can I set filter criteria for each subform to append the record sequence identifier to the link master/child fields to ensure that only the appropriate record displays on each tab?

Basically, I trying to use tab controls with embedded subforms to display the many side of a one to many relationship. The parent form contains the data for the parentrecord, and I want the subform, with a tab for each related record, to show the child side of the data relationship. One child record per tab, utilizing the same identical sub-form for each.

View Replies View Related

Copyrights 2005-15, All rights reserved