Incrementing A Number Without Autonumber
Mar 26, 2006
I want to generate a unique Patient Number by using the first three letters of a particular field and then increment it by 1.
ie If the [CountryName] field is Australia I want the Patient ID to be AUS1 then AUS2 and so on.
I can do the first bit
PatientID=UCase(Left[CountryName],3) but how can I then add the number...?
(or maybe this isn't possible).
I need to avoid autonumbers becasue of the PDA program I am then transferring the database to.
View Replies
ADVERTISEMENT
May 23, 2005
I created a database for my parents last summer and it has worked perfectly for a year now, then all of a sudden when they try to add an entry to the database its saying that it can't because it will create a new field.
Turns out my parents have added 45000 entries and it has just randomly reset the autonumber and is trying to add new entries at 25000, now my parents company is in a gridlock till I can figure out what the heck access did!
The autonumber is only used when I need update or delete entries.
If I compact and repair would that fix it?
edit:
Access 2000
View 2 Replies
View Related
Aug 25, 2005
I have a table <Table1> with field <Cust_ID> with datatype <Autonumber, LongInteger,Increment>. It means that as new record is entered each Cust_ID is auto-generated with incrementing number (that is not fraction). Now, I want to create a field that will generate incrementing fractional AutoNumber as I go on adding new record. (e.g. 1st record: Cust_ID = 1.00000001, 2nd record: Cust_ID: 1.00000002, 3rd record Cust_ID: 1.00000003)
Any help is appreciated. Thanx in advance.
Prodigy.
View 3 Replies
View Related
Aug 14, 2006
So I have a table tblPO2006 with a field PO that contains a number.
Currently I have 100 records and the curent number in this field is 10804
What i want to do is when a new record is created the field updates with a +1 increment to the previous records number.
Thus the new record would be 10805 and the next 10806 etc
I dont want to use autonumber... can someone point me in the write direction of code to do this.
thanks
View 14 Replies
View Related
Jan 16, 2006
Hi all
I am adding two records to an empty table using an append query (or make table query). I would like the first record to have 1 in the ID field and the second to have 2. I will then delete 2, append 1 elsewhere and empty the table and start over again.
Any suggestions? It needs to be 1 and 2 each time not just an autonumber.
Thanks
Greg
View 1 Replies
View Related
May 5, 2005
Hey, long time reader, first time poster.
I have a database created that is used to catalog different projects. When the user adds a new record and that project is already in the database it gives the user a message stating that that project already exists. It gives the option of cancelling and goes back so the user can name it something different. Or the user can continue and it creates a duplicate copy.
Instead of creating a duplicate copy, I want it to increment a number field named [Duplicates] by 1.
Example:
If project 'A' is already in the database and the user adds it again it increments the duplicate field by one. So now the properties of project 'A' shows that there is 1 duplicate. Does that make sense?
Here's my duplicate code so far:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim Response As Integer
If Not IsNull((DLookup("Project_Name", "Development_Projects", "[Project Name]= '" & Me.Project_Name & "'"))) Then
Response = MsgBox("That Project already exists" & vbCr & vbCr & "Select Yes to duplicate record, No to cancel.", vbCritical + _
vbYesNo + vbDefaultButton2, "Duplicate Data")
If Response = vbYes Then
[Duplicate] = [Duplicate + 1]
Exit Sub
Else
Cancel = True
End If
End If
End Sub
View 2 Replies
View Related
Nov 8, 2006
I have 4 tables on my dbase. On the last table underlies all records pertaining to customer payments.
tblCollectionData
ColID - PK
PolID - FK
Amount
ORDate
ORNumber
I want to add another field which i would name -- CollectionNumber. In this field, i need an incrementing number for each PolID.
For each PolID (no duplicates), there could be 1 to 6 records of payment. I need the number to start at 1 for each unique PolID.
Please help me on this. I've been looking at different posts but i still can't find answer. :-(
Thanks!
Sheila
View 8 Replies
View Related
Oct 23, 2006
Hi All
Hope you can help, I have been looking on the forum but with no luck. I am trying to design an append query that adds any number of records to another table but need one of the fields to increment by one for each record, so for example;
I append 10 records and the other field increments from 060,061,062 etc for each record
Thanks
HITC
View 8 Replies
View Related
Dec 9, 2005
I want my db to generate our reference # automatically based on input from other fields. The number is the date record entered, the users initials, and an incremental number. Ex. 120505GMM02
My form is based on a query and I have the query generating the first two components from the date entered in reqdate and the initials entered. How can I get an incremental number generated to go with the rest.
The number should start with 01 for each day and also for each user. Therefore when GMM is entering his third record on the 5th it should generate 120505GMM03 even if there were 10 other records entered on the fifth by other users. Each users numbers would start at 01 for each day.
I asked this earlier but thought I would restate my question in hopes of getting my thoughts around this. I appreciate your patience with me.
Thanks.
Gary
View 3 Replies
View Related
Nov 30, 2011
I have a table, Purchases, which has a 1:N relationship with another table, Items. Each purchase can have multiple Items.
'PurchaseID' is the primary key of my Purchases table.
The Items table has a composite primary key, made up of PurchaseID (making it an identifying relationship) and ItemID.
As you can see, I have ItemID starting reverting back to 1 each time PurchaseID increments. PurchaseID is an autonumber on the Purchases table, however ItemID is just a Number and has to be entered manually. If I set ItemID to an autonumber, it will continue to increment even after PurchaseID changes.
What I am trying to do is to get ItemID to auto-increment but revert back to 1 each time PurchaseID increments.
The entry form for both looks like this:
The Record Source of the main form is the Purchases table, and the subform is a Multiple Items Form based on the Items table. Filling out the form will create a new Purchase record with an auto-incremented PurchaseID, and a number of new Item records with that same PurchaseID, and whatever ItemID's are specified in the subform. Currently the ItemID's have to be entered manually as 1, 2, 3, etc.
Is there a way to get them to auto-increment, but begin from 1 for each new Purchase record?
View 1 Replies
View Related
Dec 16, 2014
Been looking around for a solution for a custom ID and can't quite find one that's specific to my problem.
I am creating a log and I want to give each new log entry a new log number. The format would be: IEyy-xxx. The ID is the letters IE, then 2digit year, a dash, then 3digit incrementing number. The ID should reset back to 001 every year (e.g. if IE14-623 is last log entry of 2014, first log entry of 2015 would be IE15-001).
My table has an autonumber that I will use as the primary key- which is not the log number- and looks like this:
These are the fields in my table:
Index Log Number (autonumber and primary key)
IE number
Program Name
Date Created
View 1 Replies
View Related
Jul 3, 2006
Is there any way it would be possible to have a number automatically created using NZ() or DMax function; based on certain criteria and also when creating the number preventing a duplicate number creation in the even of a simultaneous record creation?
Here's my example:
I have a CCN Number (Currently the employee manually types it in; and it is the primary key. What I would like is to take out the human element (error possibilities) and let access create it for the employee.
Here is how the CCN looks and the breakdown of each section (the important part's will be the first 3 sets of numbers):
96-06-184-100-000
96 = Region Code
06 = Current Year
184 = Julian Calendar Date (based on the receipt date field)
100 = Filler number based on day of the week (Eg. Monday = 100, Tuesday =
200, etc.)
000 = Filler numbers (Should always be Zero's (000)
I need to find away to pull this information from a table of some sort and combine it to create the CCN and to then add 1 (incriment) to the 4th set of numbers. Example 100 (1st record of the day) 101, 102, 103, etc.
Any help or ideas would be greatly appreciated.
View 4 Replies
View Related
Apr 16, 2013
My access database is used to process vehicle trips. Each shift, a vehicle is assigned a docket number (paper based), and on this docket the driver fills out each trip he/she makes. In a shift (thus on a docket) there may be 5 - 15 trips. I want to create an incrementing number for each trip in a new field - can be created as they are processed or after the fact by looping through the resultant data - whatever is easier.
So in simple terms, return all trips for each docket number then number each trip.
View 5 Replies
View Related
Jun 3, 2015
Is it possible to copy a record 'n' number of times, incrementing the date by either days, months, weeks etc?
I have a regular payments table that will need to be edited at some point should either dates, or amounts change, but... regardless of the size of the table, it's not a problem, as these will be moved to a different table and deleted once paid.
Given the example, would it be possible to copy this record and additional 5 times, incrementing the date by one week.
View 11 Replies
View Related
Jan 19, 2006
I have a an 'Orders' table that records order details which has the order number field set as an autonumber. I also have a number of other tables which provide additional details for each particular order (eg Address details, invoice history etc) These each have a 1 to 1 relationship with the 'Orders' table. This has all been working fine for some time but having just added a new order the order number has jump from 10135 to 10137! For some reason it missed out 10136.
Any ideas why and what I need to do? If I create a new table based on the structure of the existing one then append the records to it won't I still have the same problem?
Thanks
View 3 Replies
View Related
Apr 7, 2007
I have a table with a compound key, and one of the two fields in the compound key has to start at 100 and finish at 999 and I'd like it so that each number didn't have to be typed in individually.
So I thought about using an autonumber that somehow starts at 100 but doesn't go over 999 if thats possible? Or if you have any other ideas on how I could achieve this?
Thanks
View 1 Replies
View Related
Dec 21, 2007
Hi :)
I have solved a m:n relationship by adding a junction table. The composite primary key in the junction table is made up of primary keys from the other two tables, and both of theses primary keys are set to autonumber.
My question is this: Is it correct to set both parts f the composite key in he junction table to number rather than autonumber?
I was thinking that since autonumber should be set only once, the junction table should just take on what ever value there is in the primary keys of the other two tables.
Kind Regards
Marie
View 3 Replies
View Related
Apr 7, 2007
I have a table with a compound key, and one of the two fields in the compound key has to start at 100 and finish at 999 and I'd like it so that each number didn't have to be typed in individually.
So I thought about using an autonumber that somehow starts at 100 but doesn't go over 999 if thats possible? Or if you have any other ideas on how I could achieve this?
Thanks
View 1 Replies
View Related
Aug 13, 2015
i am going to use the autonumber as an order number but I want " SC2015 in front of it so i end up with " sc20151 , sc20152 but next year i want it to change the year SC201650 .how to put the sc2015 in my report without any input from the user
View 2 Replies
View Related
Jan 2, 2015
SQL does not copy the field (autonumber) of table1 to another field (number longue) of table2. Is this normal and is there a way around it? Here is the code:
Code:
"INSERT INTO TransUPDSHTbl SELECT TranSHTbl.MemID, TransSHTbl.FirstName, " & _
" TransSHTbl.LastName, " & _
" TransSHTbl.Language, TransSHTbl.Email, TransSHTbl.Federation " & _
" FROM TransSHTbl " & _
" INNER JOIN MembersTbl ON ((TransSHTbl.LastName = MembersTbl.NOM) AND " & _
" (TransSHTbl.FirstName = MembersTbl.PRENOM)) ORDER BY MembersTbl.MemID;"
Above table1 = TransSHTbl, table2 =TranshSHUPDTbl, Field1 = MemID, Field2=MemID
View 2 Replies
View Related
Jan 16, 2008
I create autonumber as primary key in a table, then i tried to enter a record in that table. Then i deleted it, but when i enter a new record, auto number started with the next number. Ex: i enter a record and autonumber for it is 1. I deleted this record and entered a new record, but i found autonumber is 2. I want the number is 1,
Why is it happen? any solution ? help me pls...
Thanks
View 14 Replies
View Related
Apr 24, 2014
Auto number will not work for what I want to do. I am creating a simple database that will assign the user with the next incremental number in a field. The intent of the database is for the user to enter three text fields and then obtain the number. I believe that Dmax would work but do not know how to make populate the table.
View 10 Replies
View Related
Jan 14, 2013
Is it possible or not but Iany way to set up a number field to autonumber on reviews of an application.
To elaborate I have a review table linked to the primary application table. The pk on the review table will be an actual autonumber to list all the reviews ever to happen in the database. There will be multiple reviews on one application and there will be a review number for each review (1, 2, 3, etc...). Is it possible to set up as a 'number' field that will add the next review # for that particular application, and then start again at 1 for the next application?
View 2 Replies
View Related
May 5, 2005
:confused:
Hi,
I would like how I can generate automatic Id in a table with following structure. billid number, billdate date& time.
I don't like to use AutoNumber built in feature.
Regards.
Soumen.
View 1 Replies
View Related
Feb 18, 2013
I have a table with an autonumber field, which of course is indexed with no duplicates. Twice in recent days it has attempted to add a record with an autonumber that is not the high number - it is about 20 numbers below the high number. So we get a 'can't add this record' error.
I can fix this by copying the table to a temporary table and then copying it it back. Then the autonumber works correctly.
What can I do to prevent this? By the way we updated to Access 2010 a couple of weeks ago, but the data is still Access 2003. We are reluctant to update the data yet in case it causes more problems.
View 10 Replies
View Related
May 5, 2013
I have created 2 tables:
StaffID
StaffTrainingID
Table 1 (StaffID) - contains all personal details in the form of forename + surname + position + email + contact numbers ect. Each field/person has an autonumber.
Table 2 (StaffTrainingID) - contains all mandatory training/lectures (18 fields in total). I don't wish to list each staff member again in table 2 so have put a StaffID field in (data type is number).
However, when I try to type in the StaffID number in that field so I can link that person to the relevant training he/she has taken, Access won't allow it.
View 4 Replies
View Related
