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


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

Assigning A Range Of Numbers To A Category

I am working on a small database for a school to track their live and silent auction. Currently I have a table set up as follows.

Silent Auction table :
Item Number
Short Item Description
Long Item Description
there are more fields that I am not interested in right now.

Mainly what I am after right now is to do some decent reporting for generating the catalog and posting the silent auction items on the web site for viewing before the night of the event.

I need to generate a report that contains the Item Number, short and long description. I need to group on Category which corresponds to the item numbers. Example:

Item Number 100 - 110 = Class Projects
110 -199 = Live Auction Items
200 - 299 = Sports and Recreation
300 - 349 = Entertainment
and so on......

I realize that had they numbered them in the 100 range I could group in my report by 100's and everything would be pretty good however, I did not have the opportuinity to be involved in the numbering scheme.

Even if the numbers had been in the order of 100s I still would need a way to say that the 100 -110 range is Class Projects.

I think I need another table and link it but I am not sure how to set up the range of numbers for the category.

Sorry to be so wordy.

Thanks in advance for the help.


Network admin trying to learn Access.

View Replies View Related

Sequential Numbers For Each Customer - Version: 2002 (10.0) XP

I need to create a sequential number for each customer and store them in the same table. For example:


So, the number is sequential within each customer

The number has 3 parts:
1-the prefix will always be the letter "P" then,
2-the next two letters is the customers unique abbreviation then,
3- comes the sequential number padded to four decimal places.

The table will also store the unique CustomerID.

I'm not sure how best to create and store the number. I thought that I would do some sort of Dlookup or DMax of the sequential number based on the customer ID but if I store the whole string then I don't think that I can do the DMax on it because of the letters. So, do I store the parts in seperate fields? And then will the padding of zeros mess up the DMax?

View Replies View Related

Entering Multiple Identical Orders With Sequential Numbers

Hello all.

I need to be able to enter a number of orders that would all be the same, but they will have different order numbers, in sequence.

I'd like to enter all the information in a form, then have "First Order Number" and "How Many?" fields, and have the system grab the detail, apply it to the order number entered, create a record in the table, then reapply the detail to the next sequential number, repeating the process until it has created the number of orders shown in the "How Many?" field.

I am at a total loss here, and any help would be GREATLY APPRECIATED!!



View Replies View Related

Sequential Unique Part Numbers - Version: 2003 (11.0)

I am looking for a way to create a part number based on the date and time based on the system clock. I plan to do a concatenate features where I add other elements but for now I am having trouble getting the date and time down to a format like: "mmddyyhhmmss" that will come up automatically like an auto number.

I plan to add two other columns to this, one being a 1 digit part# and a alpha character to designate which machine made the product.

My finished number might look something like this: "1A112806110555" this number would then be printed in a barcode as well as displayed numerically on a label.

Got any ideas? any help is greatly appreciated.


View Replies View Related

Record Numbers Not Sequential In Report - Version: 2002 (10.0) XP

I have a report with approximately 350 records which starts near the end numbers for the first 10 pages of the report and then switches to the beginning numbers. Is there a way to resequence the report so that it goes in order from record 1 to the end? Thanks for the assistance.

View Replies View Related

Sequential Numbers In Text Field - Version: 2002 (10.0) XP

My challenge is: using a 'text' field, to put numbers in normal sequence. Without using a query or macro I get something like 1, 10, 100, 101, 102, 103, etc. I want it to read 1, 2, 3, 4, etc. Many thanks in advance for your assistance!

View Replies View Related

Generating Sequential Numbers For INSERT/UPDATE Statements

Here's a function that I've used in A97, A2000 and A2002 environments:

Since autonumbers should not be used for attempting to sequentially number things like orders, invoices or ..., the logical question is:

"What can I use to generate the next sequential number? I have a shared database with multiple users!"

The most common answer is: "Roll your own code (carefully)".

However, I've found that repeatedly rolling my own code each time I need a "next number" is tedious and results in duplicate coding effort.

So, I came up with a user defined function that I named xGenNxtNbr.

It can be used directly in/with SQL INSERT and UPDATE statements and will attempt to do the insert or update up to 20 times, and will pass back the generated sequence number (or 0 if unsuccessful).

Brief syntax description:
'Example to insert next order number, where customer = 7, customer purchase order = 004-A40B

Dim x as Long
Dim strWk as String

strWk = "INSERT INTO tblOrders(OrdNbr,CustNbr,CustPO) VALUES (xGenNxtNbr(), 7,""2004-A40B"");"

x = xGenNxtNbr(strWk,"tblOrders","OrdNbr") 'x will be 0 or the generated next_number

'Restriction: The SQL statement can contain only one instance of xGenNxtNbr().

The attached zipped sample A97 mdb contains the code and a sample form that illustrates its use.

Some key points to note in the code are:

1. The use of a .recordsaffected statement to detect the success or failure of the insert/update operation. It is crucial to the task, since the .execute process does NOT raise an error (in A97, at least) on a duplicate key violation.

2. The On Error statement is used to handle things like sql syntax errors, broken databases, etc.

3. It uses a CurrentDB.execute call, so if you are using your own special "transaction commit/rollback" processing, it may need to be adjusted a bit to fit your needs, but for normal application processing, I've found it to be quite useful.

4. It assumes that the number being generated is a LONG, in the range 1 to 2^31-1.



View Replies View Related

Display English Numbers In Arabic Numbers - Version: 2003 (11.0)


I've have an application where we display everything in English but sometime we would like to print reports which contains English numbers in Arabic-Hindic format. How can I accomplish this? PLEASE?
Also, I tried to conver tbut I get ??/??/???? why is this?


View Replies View Related

Numbers Stored As Text Convert To Numbers?

Ok so in excel I have some numbers that are stored as text. The reason being that they are zip codes and some begin with 0 and excel doesn't want numbers to start with when I import these into an access field that has an input mask for zip codes...will it convert these correctly since the field is a text with input mask?

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 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 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 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 Yes/no A Numeric Value

Complete newbie question, I have various yes/no boxes that i want to give a numeric value. The i want to add up these numbers and report it back to a text box.

Any help you can offer would be greatly appreciated.

Thanks in advance

View Replies View Related

Assigning Value To Checkbox

I think this should be a simple question, but I just cannot figure it out. I have a form which contains a check box. I want to set the value of a field in a table to 0 if the box is unchecked and 1 if the box is checked.
How can I do this


R Tomalin

View Replies View Related

Need Help Assigning Permissions

I'm having a huge problem with this. I have a database which I put on our server, where 4 duty sections currently use. Is it possible to have permissions that work along with filters? This is what I'm trying to accomplish: Someone from Engineer section needs to use the same form as someone from the Communication section, however they shouldn't need to view each other's information. Is there a way to lock out the records that they should not see for security purposes? I hope you can make sense of what I'm trying to say. Any help you could provide would be awesome, thanks.


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

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

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

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

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

I Need Help - Assigning Age To Etary Group - Can You Help, Pls?

Hi all!

In my very first thread i bring a doubt that persutes me since laste Friday. I have search in the web an in this forum but, unfurtunantly, I didnīt find a answer for my problem. I hope some of you can help me :)

I am currently trying to construct a Data Base which will keep between 1.000.000 and 5.000.000 registries, and will have about 80 to 90 Fields. As you can see, its very much information to be hold and i need to ensure that the queries, that will be made to this DB, will be the simpliest and fastest as it can be possible.

Anyway, my problem is the folowing one:

I have a table, named Person, and in that tabe I got a field named Age and other on named Desig_AgeGroup. I want to assign the Age to the correnpondent AgeGroup automaticly (e.g.: if there is a registry in the field Age of the table Person thas is equal to 12, I would like that in the Field AgeGroup, in the table Person, appear " Between 1 and 14 years old" after that i typed 12 in the Age field, or after I imported Data to the field Age). For this proupose I had created another table, named AgeGroup, whit the following fields: Cod_AgeGroup, Desig_AgeGroup, Age_min, Age_max. At this point, my idea is to compare the Age, from the table Person, whith Age_min and Age_max, from the table AgeGroup.
However, the only thing i have acomplished whit this method was a Text box that apear in the field AgeGroup whith all the Age groups that I had specified in the field Desig_AgeGroup of the table AgeGroup.:(
Can someone tell me How can the Age groups apear automaticly in that field?

I hope I have been explicit...and sorry my poor, poor english
I aprreciate, since now, any help i can get from you.


View Replies View Related

Assigning Pictures To Records

I notice that this topic has been done to death.....I have a database that is embedding the pictures and the predictable problems has manifested...the database is swelling up....

I'm learning to adapt the database to include the file location in the form and have the picture box refer to the file location, rather then embedding the picture into it....the problem that I have is that although I know how to do it because I get it.....this database will be run by a number of people, many of which don't have much experience and it must be simple to input the file location. Is there a way to simplify putting the file location in the text box aside from typing it in manually (a search window; like the one that appears when trying to open a file or insert an object, perhaps)....

View Replies View Related

Assigning Data To A Query Using VBA

I have a form that has a button. This button is used to open a report. The command used to open the report refers to a query and sets the criteria so the report generated only returns values the button asks for.

I know how to set the criteria of a query using VBA, but is there a way to set a field that has no data to now have data?

I have a field in a query called Type and it is set to Null (SQL: Null As Type)
I would like to do this in the VBA, but subsitute the Null for a value that would be specific to the button.

View Replies View Related

Assigning Asset To Employees

Hi, I've lost touch of Access few years back & now I'm back again using Access... kinda rusty & yet rushing for a proj... so I'll very much appreciate if someone can help me on this..... :)

I've a database storing inventory details & after adding these details, i'm supposed to have another form where i'll start assigning these assets to different employees..& of cos, i'll need to edit this form over time if the employee left the company or asset is being transferred to another person. However, this asset list is quite comprehensive with thousands of records, so it isn't feasible for me to list all assets at one go & start assigning different asset to different employee... any idea how I shld go abt assigning? shld i minimise the search by invoice key & track no? (each invoice contains a few grp of items & each grp will contain several items, therefore, i've assigned an invoice key for each invoice & track no for each item.)

any help is very much appreciated.... thanks :)

View Replies View Related

Assigning Picture On Selection

Hey to all. I have this form in which I need according to a user selection to assign a picture and display it on the same form. Is this possible? How do I do this? Or can I display the picture to another form or a report based on the first form?

Thanks in advance.

View Replies View Related

Assigning A Rank To A Group

Dear all,

I would appreciate any help you can offer with this

I have a table that contains 6 colums of experimental data:

Experiment ID
Model ID
Gene ID
Delta Expression

For each Gene ID I may have many Delta expression values in different experiments, identified by the Experiment ID.

I have a series of queries that calculate the average Delta Expression value for each Gene ID value and then selects the top 50 Gene IDs according to this average.

Query 1: Calculates avg delta expression for each gene id
SELECT DISTINCTROW [experimental data].[geneid], avg([delta expression]) AS [Mean Delta expression]
FROM [experimental data]
GROUP BY [experimental data].[geneid];

Query 2: select the top 50
SELECT TOP 50 [AVG delta exp].[GeneID], [AVG delta exp].[mean delta expression]
FROM [AVG delta exp]
ORDER BY [AVG delta exp].[mean delta expression] DESC;

Now a 3rd query then takes this output and gives me all experimental data on Gene IDs that appear in the top 50. What I want to do is preserve the ranking, so that all the data is still shown, but each gene ID also has it's top 50 ranking. Then order the output accoring to the ranking and the gene id.

SELECT DISTINCT [experimental data].[GeneID], [experimental data].[delta expression], [experimental data].[direction], [experimental data].[model id], [experimental data].[reference]
FROM [experimental data], [Top 50 genes]
WHERE [experimental data].[GeneID]=[Top 50 genes].[GeneID];

Any ideas ?



View Replies View Related

Copyrights 2005-15, All rights reserved