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.

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


Assigning A Range Of Numbers To A Category

Mar 27, 2006

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

Unique Sequential Numbers

Nov 22, 2004


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

Reports :: Sequential Numbers For Records

Mar 10, 2015

Need a sequential number on report. Have an unbound text box and set it to list a running sum over group. The twist is that I'd like to have it start with an assigned starting number [Starting Number] and then add 1 to each subsequent record. It works for the first record but then the next record is last value + [Starting Number]. Basically it is a check run where I would list the starting number and the report would do the rest.

View 2 Replies View Related

Queries :: Missing Sequential Numbers

Feb 25, 2014

I have a list of what should be sequentaia numbers, but I am finding instances were a number seems to be missing..Is there a way to query the whole dataset and find all missing instances without having to trawl he list?

View 4 Replies View Related

Auto Enter Sequential Numbers?

Apr 2, 2012

Ineed to track Trailer security seals that are sequentially numbered with 8 digit numbers...They are physical seals and no efile with the numbers to upload into there a way I can put in a range and Access will populate the rest in a table?

View 5 Replies View Related

Modules & VBA :: Assigning Reference Numbers To Records Where Same Customer Appear

Nov 21, 2013

I'm trying to put together a DB for creating notifications to customers. The source file is pulled out from another system as an Excel file, which is then uploaded to Access via VBA. The user runs a query, and then sends the results (customer information) to an Excel file where we have set up a mail merge document (the notification itself).

A customer may appear in multiple records (having multiple accounts), but will only receive one notification.

What we're trying to do is give each notification an unique ID that will become its document reference number once the document is created. The number will need to be the same for all the records in which the customer appears, and they will need to be consecutive, as the documents themselves will be stored in PDF form later on in the process.

So I would like to assign an unique ID to each customer, each time it shows up when querying the uploaded source file (the query is done in a form, that has a sub-form for showing the results). Then, when exporting the query results, they will be also copied and stored to another table, along with the assigned reference number, for monitoring purposes.

The Access file itself will be split and used by multiple users (up to 9 users at a time). In theory, each user will see their own customers (each customer account is assigned to a certain user), but the number assignation will need to also depend on when the query has been run. For example, if user 1 queries the DB at 9:30AM they will get records numbered 1000 to 1050; user 2 (who queries the DB at 9:31AM) gets numbers 1051 to 2100 etc.

View 2 Replies View Related

How To Isolate Records Based On Sequential Numbers

Dec 27, 2011

I am trying to create a database that will have records that include dates and a number assigned to that date. ex., the 3rd day of the quarter will be numbered 3 and the 43rd day numbered 43. I will do this via table.

After running a query that will identify a subset of records, I only want to see those records that are part of a 10-day or greater span. There could be multiple 10-day+ spans over a given quarter.

View 2 Replies View Related

Entering Multiple Identical Orders With Sequential Numbers

Jul 12, 2006

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

Modules & VBA :: Database For Quotations - Modifying Sequential Numbers

May 29, 2015

I have a database for quotations. The database automatically generates a new quotation number every time a new quote is started. This works great, and I am very happy. My next task is to allow the employee to pull up a quote that has already been generated and edit it. I would like for the new quotation process to be followed step by step, but with all of the information already filled in.

This will allow for any edits that need to be made, and keep from having to re-enter a lot of data. I want one thing to change, which is the QuotationNumber. It is currently formatted by "yyyymmdd-01" for the first quote generated on that day. I want the edited quote to have a QuotationNumber formatted by "yyyymmdd-01a". For every edit that letter change going through the alphabet in order. How would this new QuotationNumber code differ from that of the Other?


View 10 Replies View Related

Queries :: DCount To Add Sequential Numbers By Group Only Works In Some Instances

Jun 24, 2014

I have a large table (>1M rows), and I have searched various forums for a way to add sequential numbers by Group. The query I have works, but since it's a large table, I broke it up, and did everything what starts with A-E, the F-Q, etc, and appended to a new table.

This query works, on anything that starts with the letter D or later... A-C will not work.

Basically, the source table is a list of all combinations of Part_ID and UPC_Code. I am trying to number the UPC_Code field, per Part_ID. There is an AutoNumber field (ID) that is in the table as well.

This is the SQL. Query1 is the query that is being executed, so the DCount is within this same query, if that makes sense.

SELECT tbl_upc.ID, tbl_upc.Part_ID, tbl_upc.upc_code, DCount("[Part_ID]","Query1","[Part_ID] = '" & [Part_ID] & "'")-DCount("[Part_ID]","Query1","[Part_ID] = '" & [Part_ID] & "' AND [ID] > " & [ID]) AS Seq_Num
FROM tbl_upc
GROUP BY tbl_upc.ID, tbl_upc.Part_ID, tbl_upc.upc_code
ORDER BY tbl_upc.ID;

The results of this query are that all Part_IDs that start with A through C produces a Seq_Num of 0, but any that start with a "D" or later number correctly - in other words, the first instance of a particular Part_ID is 1, then 2, and so on up to the total count of that Part_ID.

Running Access 2013.

View 2 Replies View Related

Queries :: Unable To Find Missing Sequential Numbers In A Text Field

May 5, 2013

I am trying to create a query to find missing sequential numbers in a text field. I am using this specific field as a case number which is designated as two letters, the # sign, two digits indicating the year, a dash, and then a four digit number; For Example: AB#13-1234.

The reason for this query is to tell the user of this database that a specific case number has yet to be entered and needs to be. The case numbers are unique and will never be referenced more than once.

My table name is "MainDataTbl" and the field i'm trying to find the missing case numbers is titled "CaseNumber".

View 9 Replies View Related

Queries :: Adding Sequential Numbers To Generate A Unique Reference Number

Oct 5, 2013

Basically what I have is a database for tracking/logging parcels that arrive to the office. I want to be able to generate a reference number based on the date of arrival: i.e. the reference number should be ddmmyy### where ### is a sequential number. I know that I could just use the primary key's autonumber, for the sequential number but if I do this then the sequence will not restart at 1 on each date and because we receive a lot of parcels the reference number will grow to be too big to print out on the collection slips in just a few months.

two tables (one with the date and staff on duty that day and the other with the parcel's info') with a one to many relationship

I also have a query (Named: FullLog) that picks up the following data from the tables:

Name - Description - Size - TrackingInfo' - Staff - DateReceived - Count

The field named Count is a DCount function that I used to find out the number of times each date is repeated. This is the Expression that I used:

Count: DCount("*","FullLog","DateReceived = " & [DateReceived]) [Note that DateReceived is first converted into a string using CStr()]

This is as far as I have been able to get, I have been looking for weeks for a solution to this problem but I have yet to find one. I don't even know if the DCount function is the correct way of doing it, I did read somewhere that this produces a very slow query.

Effectively what I want to be able to get is something of that resembles the following

DateReceived - ReferenceNo

051013 051013001
051013 051013002
051013 051013003
061013 061013001
061013 061013002
071013 071013001
071013 071013002
071013 071013003
071013 071013004
071013 071013005
081013 081013001
081013 081013002
091013 091013001
101013 101013001

View 6 Replies View Related

Queries :: Search For Multiple Plot Numbers Preferably In One Parameter Prompt With Comma To Separate Numbers

Aug 12, 2014

I'm having multiple problems with my database like things such as -

i'm currently working on the Query 2 - On the Phone database (ignore Query 1) and i want to search for multiple plot numbers preferably in one parameter prompt with a comma to seperate numbers. (this could be a multitude of numbers so i would like to be able to input as many as needed). Also when i do search on this query since the Criteria is a 'Between' Value i would expect everything between the 2 numbers input to show up - but a lot of numbers out of the range show up too - why is this? (The Numbers are like "69 to 136" and they will show up - but 1-69 and 136-170 would too

I would also like to implement the search results from Query 2 into the Form i currently have made but it just opens up a access table when the search is made?

i cannot link my database as it is too big for the server - But here are the Criteria for Query 2:

Plot No - (criteria = Between [Enter First Plot No:] And [Enter Last Plot No:])
Site - (criteria = Like "*" & [Enter Site:] & "*")
Product - (criteria = Like "*" & [Enter Product:] & "*"

The Query is the one im most concerned about , i can live without a form.

View 14 Replies View Related

Numbers Stored As Text Convert To Numbers?

Jan 10, 2007

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

Modules & VBA :: Automate Line Numbers And PO Numbers

Aug 24, 2014

I have 2 fields that I would like to automate if possible

One field is called "p/o number" and another field called "line no"

These fields are part of an ordering database

Let say I have 200 items to purchase form 10 suppliers

And form example 20 items from each supplier

What I do at present is put the order number on each line item and the line number


p/o number line no

1 1
1 2
1 3

2 1
2 2
2 3
2 4

What I want to do is just put the first po number in the required line . Put the first line number in i.e. "1" and the macro will complete all the p/o numbers and line numbers for me as per the ones marked in red.


1 1
2 2
3 3

2 1
2 2
2 3

View 5 Replies View Related

Assigning Yes/no A Numeric Value

Nov 12, 2007

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

Assigning Value To Checkbox

Feb 15, 2005

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

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

Sep 14, 2006

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

Assigning Pictures To Records

Jul 9, 2007

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

Assigning Data To A Query Using VBA

Jan 16, 2006

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

Assigning Asset To Employees

Jul 26, 2005

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

Assigning Picture On Selection

Aug 22, 2006

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

Assigning Design Permissions

Mar 1, 2005

I have a database that will be ran over a network. I want the freedom to add fields to the table if needed later, but I don't want anyone else to have the same permissions as me. I made an attempt at using the wizard to assign permissions, but it didn't turn out the way I needed it to. Can someone help me with this please?

View 1 Replies View Related

Error In Assigning Value From Recordset

Aug 30, 2006

I am assigning a value from a table to text box on the form through VBA. It gives the following error :

Run time error 2115

The macro or function set to the BeforeUpdate or ValidationRule property for this field is preventing MS Access from saving the data in the field. I have no BeforeUpdate or Validation event for the text box. What else could be the cause ?

The text box is unbound and I use ADODB connection.
Please assist.

View 5 Replies View Related

Queries :: Assigning Value From Array To Each Row?

May 28, 2013

I've built a query that selects certain customers based on a purchase date. I now need to assign these customers to various sales reps and display the customer information to them in another form that they can alter information in.

I was going to put my sales reps id into an array and loop it through the query updating it. Let's say i have 7 sales reps working that day, I would then enter their id's into fields and have the array assign these numbers into the query Sales Rep Id field for each row.

View 6 Replies View Related

Copyrights 2005-15, All rights reserved