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 Replies
ADVERTISEMENT
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
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
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
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
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 Access...is there a way I can put in a range and Access will populate the rest in a table?
View 5 Replies
View Related
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
Feb 15, 2013
I have a rental database and I print several contacts for leases etc. what I want to do is have a unique reference number or something inserted to the report every time that it's printed. What I am trying to achieve is to keep track of which tenant corresponds to the report (Lease) printed by using reference number.
There is a seperate form which holds the tenants details and I would like to have a field on that form which would show the same reference number as the report so I can track which report was printed for who.
Whats the best/easiest way to accomplish this?
View 1 Replies
View Related
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!!
Thanks!
Tom
View 3 Replies
View Related
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?
QuotationNumberCode.PNG
View 10 Replies
View Related
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.
Code:
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
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
Mar 5, 2008
I have a table of order lines. Each line of data the order number is repeated for every line. I need to just pull out one line for each order number. I assume a query would do this but I don't know how to create it. The order number is numeric. Can some one show me the way?
View 2 Replies
View Related
Jul 4, 2005
Hey guys,
Hope you can help with this one. I have a form called "frmPatientID", based on a query "qryPatientID" and table "tblPatientID".
I have a field called HospitalNo that is set to "text" and contains the patient's unique hosptal number of 123456, 008777, etc.
However, although the above numbers have to be unique, if the patient is out of the area they get a number of 000000 and this is not unique (i.e. more than one patient can be out of the hospitals area).
So how do I get the database to control this via a rule? (As in everything NOT 000000 needs to be unique). Is it on the form on an AfterUpddate or can I do it in the table?
Many thanks,
Rusty
:D
View 5 Replies
View Related
Nov 19, 2007
We are trying to use Access to generate, job, task and craft numbers in Access. Job and task will be unique. Craft numbers can use the auto number function. We need the numbers to be 4 digits for Job and Task.
View 2 Replies
View Related
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
Aug 1, 2014
I have been working on a simple data base for some time now (beginner level) and am still trying to improve it. I would like to do something but before that I would like to have your opinion to know if it is even possible?I have a query QryMainReport:
Start Date/Time
End Date/Time
Employee
At the moment this is what the format of my report looks like (I removed other unnecessary fields):
StartTime----------EndTime---------------Employee
12/06/2014 01:00--12/06/2014 03:00------John Smith
12/06/2014 04:00--12/06/2014 06:00------Jane Doe
13/06/2014 02:00--13/06/2014 05:00------John Smith
13/06/2014 08:00--13/06/2014 08:00------Jane Doe
I would like to do as a report. (Dates would always be from Sunday to Saturday). I am not sure it is possible to do that. I suppose first it would mean:I would have to do a query to separate the times from the dates?I would have to find a way for Access to find the unique dates and unique names?Does it mean I have to use cross tab queries?
View 2 Replies
View Related
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 0....so 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
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
example
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.
Example
1 1
2 2
3 3
2 1
2 2
2 3
View 5 Replies
View Related
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
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
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
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
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 2 Replies
View Related
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
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
