Sequential Numbering With A Difference!

Oct 12, 2005

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


ADVERTISEMENT

Sequential Numbering

Apr 3, 2007

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 6 Replies View Related

Sequential Numbering Within Groups

Aug 8, 2005

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.

e.g.

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.

thanks,
Alex

View 11 Replies View Related

Sequential Numbering Of Query

Nov 19, 2005

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
1874864127.12511.9375160004
1874866127.1259.6875160006
1874868127.1258.5160008
1874869127.1255.8125160009
1874867111.8758.8125160007
1874863110.12514.875160003
1874865110.12510.5625160005
1874871221.62514.3751600011
1874872221.62513.81251600012
1874873221.62513.56251600013
1874875221.62511.68751600015
1874876221.62511.6251600016
1874877221.62511.06251600017
1874870221.2516.31251600010
1874874219.12511.751600014
1874878219.12510.251600018
1874879219.1257.1251600019
1874862534.5103.375160002
1874861534.536.875160001

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 3 Replies View Related

Adding Sequential Numbering To A Query

Oct 5, 2005

Hi,

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 8 Replies View Related

Queries :: Sequential Numbering Within A Query

Feb 6, 2014

I've taken on the task of transitioning excel reports (and their format) to a database. One report summarizes all the parts of a piece of furniture, and the order of the machines each part goes to.It looks similar to this:

Part# Part Name Machines/Operations
M7264Top Panel 112114116120121325216
M7265Under Top Panel 112114116120121
M7266Base Assembly 411325216311310312316

The table this comes from has both operations and setups, so I used a query to filter only the operations.The query (qryOperations) result looks like this:

PART NUMBEROPERATION ORDER
M7264 112 2
M7264 114 4
M7264 116 6
M7264 120 7
M7264 121 8
M7264 325 9
M7264 216 10
M7265 112 2
M7265 114 4
M7265 116 6
M7265 120 8
M7265 121 9

i need to add sequential numbering (1,2,3,4...) to each line of the qryOperations and use the sequential numbering as the column header.How do I add sequential numbers to the query, that restart after each change in part number? I can do this in a report easy enough, but not a query.This is the SQL of the query I need to have the sequential numbers in...

SELECT tblparts.[PART NUMBER], tblOperationCodes.MachineCode, tblOperationsList.Order
FROM tblparts INNER JOIN (tblOperationsList INNER JOIN tblOperationCodes ON tblOperationsList.OperationCode = tblOperationCodes.OperationCode) ON tblparts.[PART NUMBER] = tblOperationsList.[PART#]
WHERE (((tblOperationCodes.Function)="O"))
ORDER BY tblparts.[PART NUMBER], tblOperationsList.Order;

View 5 Replies View Related

Queries :: Sequential Numbering In Query Field

Jun 5, 2013

I have a query that groups the data by customer. How can I create a "Transaction Number" field where I assign a sequential number to each invoice, starting at 1 with the oldest invoice and increasing the sequential number by 1 with each invoice. I need to create this field in a query without code.

View 3 Replies View Related

Modules & VBA :: Update Column With Sequential Numbering

Jan 8, 2014

I have column called "order" in table called "mov" and this column has this layout

Code:
1
2
14
255
222
1755
12

And I want to update this column to be corrected numbering from 1 to the last cell number - lets say it 17540 - this update has no criteria conditions, just this field.

View 4 Replies View Related

Tables :: Modified Sequential Numbering In A Field

Feb 10, 2015

How I can create a sequential number in a field that looks like this:

Year-XXX-00

(Example: 15-100-00 -- the next sequential number would be 15-101-00)

I don't want to hard code the year, and the last 2 numbers must be entered manually.

It gets even more exciting --> this number has to be able to be duplicated in a table. Those last 2 numbers is a revision number. We might have a 15-100-00, 15-100-01, 15-100-02, etc.

View 3 Replies View Related

Modules & VBA :: Sequential Numbering Dcount Dmax Composite Primary Key

Sep 23, 2014

I have a question concerning the automatically sequential numbering of a primary key as part of a composite primary key.

Tbl_treatment:
ID=numeric field (also in tbl_pt and in tbl_tumor)
Tumornr=numeric field (also in tbl_tumor)
Treatmentnr=numeric field

[ID] and [tumornr] are fixed and i would like to automatically number [treatmentnr] per [ID] AND [tumornr] in a way that the output will look like this:

1001 1 1
1001 1 2
1001 1 3
1001 2 1
1001 2 2
1001 3 1, etcetera.

However, when i use the SQL-formula below my output looks like this. It seems as if the function is not properly taking the composite primary key of [ID] AND [tumornr] into account or not finding the true max value:

1001 1 1
1001 1 2
1001 1 3
1001 2 2
1001 2 3
1001 3 3, etcetera.

If (DCount("[treatmentnr]", "Tbl_treatment", "[ID] = " & Me.ID & "" & "AND [tumornr] = " & Me.tumornr & "")) = 0 Then Me.treatmentnr = fRowNum(False)
Else Me.treatmentnr = (DMax("[treatmentnr]", "Tbl_treatment", "[ID] = " & Me.ID & "" & "AND [tumornr] = " & Me.tumornr & "")) + 1

View 3 Replies View Related

Forms :: Text Box Bound To A Field - Change Sequential Numbering

Jul 31, 2013

One of my forms has a text box which is bound to a field called teenumber. This form is set up that "on current" has code

If Me.teenumber = 0 Or IsNull(Me.teenumber) Then
Me.teenumber = Nz(DMax("teenumber", "tblteeofftimesshotgun"), 0) + 1

This enables the text box teenumber to be auto filled with sequential numbers growing by 1 for each record. This database is for a golf tournament and this form enables user to set up tee times for shotgun start for the players.

When the user gets to tee number 18 or whatever the last hole on the course is the teenumber field needs to be reset to 1. With the above code I a unable to do this.

View 11 Replies View Related

Forms :: Sequential Numbering Based On Information In Combo Field

Sep 4, 2014

Creating a form against a table with the following fields

EC
Project
Originator
Title
Full description
Effectivity
Etc....

Easy enough to create the Project combo box, problem comes with trying to systematically assign the next EC number.

When I select a Project from a combo box I want an EC assigned with format ECXXXX-### where XXXX is the project number from the combo box and ### is a sequential number. ECs should start at -001 for all projects.

Do I need to add an EC suffix field to hold just the ###? Then what?

View 6 Replies View Related

Tables :: Adding 1000 Semi Blank Records With Sequential Numbering

May 21, 2013

I've received a database that is a digitized population register from the 19th century. All adults have been entered into the database, but all children are missing.

Every person has a unique number that corresponds with the original source (this variable is called 'no', this is not the autonumber primary key thing). Instead of searching in the original source which numbers are still missing, I would like to add the missing numbers (with no additional information, because I still need to type that in).

For example, the table now looks like this:

no - name_last - name_first - occupation etc

1 Smith Henry baker
2 Smith Mary
5 Williams John butcher
6 and so on

So 3 and 4 are missing.

How can I add these missing numbers automatically?

View 3 Replies View Related

Sequential Ranges

Aug 19, 2005

I need a form to set up records

The table is something like

AutoID, Sequence , Low, High

Data would be

1, 1, 1 499
2,2,500,999
3,3,1000,9999
4,4,10000,999999

etc

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 2 Replies View Related

Sequential Dates

Jun 2, 2005

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 5 Replies View Related

Sequential Number

Jan 22, 2007

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.

Example:

01 (is electrical)
02 (Shop supplies)
etc....

So...

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!

Thanks,
james

View 3 Replies View Related

Assigning Sequential Numbers.

Feb 12, 2007

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.

Team
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 9 Replies View Related

Sequential Number Generation

Sep 11, 2006

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?


Cheers

Dave

View 1 Replies View Related

Returning A Sequential Number

Mar 11, 2008

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?

Robert

View 3 Replies View Related

DMax() For Sequential Autonumber

Jul 23, 2005

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")
Else
NewScreenID = rst!MaxScreenID + 1
End If


rst.Close
Set rst = Nothing
Set db = Nothing

NewScreenID = ScreenID

Exit Function

Err_Execute:
'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 1 Replies View Related

Sequential Number Problem

Nov 10, 2006

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
AmountPaid
ORDate
ORNo

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..


Thanks!
Sheila

View 4 Replies View Related

Unique Sequential Numbers

Nov 22, 2004

Hello,

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.

Thank!
Elizabeth

View 1 Replies View Related

Access Sequential Number

Apr 16, 2008

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.
regards

View 14 Replies View Related

Problem With Sequential Fields In A Report

Jul 5, 2006

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 2 Replies View Related

Sequential Order Number With Alphanumeric Value

Jan 25, 2007

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.

Thanks,

View 13 Replies View Related

Tab Controls On Subform For Sequential Records

May 19, 2006

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 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved