Incrementing Number For Old Records
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 Replies
ADVERTISEMENT
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
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 5 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
Jun 9, 2005
I wan to create a table with a date / time starting at a specific time and then added a number of records with each record being a minute later than the first.
The starting point could be hard coded or by user entry and the loop would run for a certain number of times .....
Any ideas on how to do it ..... poss a macro :confused:
View 3 Replies
View Related
Jun 27, 2014
I have almost no experience of VBA outside of working a little with codes generated for me like docmd etc.
I am trying to create a database for resourcing staff.
I have a form where the user inputs a start date and an end date which updates a table.
I have a query that works out the number of working days between those dates.
I have a table with all the working dates between now and 2016
I want to put a duplicate record button on the form (I can do that bit)
The tricky part is I want it to create a new record for each date in the dates table between the start and end dates.
Example 23/06/2014-18/07/2014 = 20 working days. I want to end up with 20 records with start dates as below and the same end date.
23/06/2014, 24/06/2014,25/06/2014,26/06/2014,27/06/2014,30/06/2014,01/07/2014,02/07/2014,03/07/2014,04/06/2014,07/07/2014 etc up to 18/07
View 9 Replies
View Related
Jan 21, 2014
I have a form [IUDATA]
I have a add record button.
I have a date field [DATEIN]
I have a text field [DRPNO]
If the [DPRNO] field is empty, I would like the user to have the [DPRNO] field be automatically populated after the user enters a date.
I'd like the format of [DPRNO] to be "dpr YY-XXX"
Where:
YY is the year of the [DATEIN] field and
XXX is number of records in that year.
So for example, if it was the 4th record with a 2013 date the [DPRNO] would be dpr 13-004.
View 12 Replies
View Related
Jul 10, 2006
I've created a report and report has the same number of pages as the number of records that it's displaying.
If there are two records, the report has 4 pages...the first 2 are the actual report and then the other 2 are a copy. If there are 3 records, the report would have 3 copies (...6 pages).
Any idea how to change this so that I have only one copy of the report?
View 1 Replies
View Related
Mar 22, 2013
One shows my form with the Transporters Subform with 3 entries, and 1 entry.The three line items that say "Transporter" are in one subform. I used this code
Code:
Private Sub Form_Current()
If Me.RecordsetClone.RecordCount >= 3 Then
Me.AllowAdditions = False
End If
End Sub
to limit the number of records I can add to 3 or less.My issue is that I lost the blank text box that allows you to add another record. So, if I only have one Transporter listed, there's no box to let me add a second or third.I have the following properties for the Transporters Subform set to "Yes":
Data Entry
Allow Additions
Allow Deletions
Allow Edits
Allow Filters
View 3 Replies
View Related
Oct 26, 2005
Here is my issue. In a table with an Auto Number index some records have been deleted. I have been able to recreate them along with their original auto number. The problem is that I do not know how to append these records forcing the original auto number. I have tried changing the auto number field to a number field in the table, this works except I cannot change it back to auto number.
I am sure I’m not the first with this question or issue. I did search through a couple hundred entries about auto number before I posted this question.
Jim
View 7 Replies
View Related
Jan 9, 2007
Hello there, some help would be nice! Say i have a booking field. If people wanted to book a number of seats, how would i make it so that when the number of seats that was entered on the booking form was entered it would add on to the exsisting number of seats taken?
So say for film A There is 20 seats available and 4 of been booked, then someone wants to book 2 more, how do i make it so now, the number of seats taken shows up as 6 as apposed to 4?
Id like this done without VB if possible?
Thanks!
View 1 Replies
View Related
Jul 15, 2007
Hi everyone,
My name is Stuart and I am from the North east of England. I am a total beginner in using access.
I am a bird of prey conservationist involved in numerous breeding projects for endangered species of raptors. Currently I am looking to set up a breeding programme which will involve a daily report log involving diet, weight, activities, equipment used, etc, etc.
What I want to do is create a table whereby each day I create a new form with its own unique incrementing reference number. Say I create a form today and it is recorded as 000001. Later on today I may create another form with all of the same information fields but with varying levels of information. I want that second form to be recorded as 000002. Tomorrow I may create a third which is 000003.
Is this a process that can be created in access and if so would you know how to do it? I would greatly appreciate your help if you could, please.
Sincerely,
Stuart
View 4 Replies
View Related
Jun 26, 2007
Hi
I have a table within which I store data showing the results of tests.
I wish to create a certificate(as a report) which displays these results. The certificate should have a unique number which is then stored with the original record within the original table, called 'Sample Results'
I have created an append query(CertDetailsQuery) to pull the appropriate date into another table(CertDetails), using criteria etc, however I am struggling with the creation of a field which creates the unique reference for the certificate. I have tried autonumbering inthe "CertDetails" table but this gives a unique number for each record selected by the append query, whereas I want each record selected to have the same reference (as they will be on the same certificate together).
I hope this is clear, help if you can,
Many thanks
Richard
View 2 Replies
View Related
May 20, 2005
Hello,
I am new to the world of databases, and I would like to know the easiest way to have a column with auto incrementing numbers.
I want that everytime a user enter data in a form, the new record goes into the database with a unique number.
thanks in advance
View 3 Replies
View Related
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
Oct 31, 2004
Hi,
I am reasonably new to access and i am having a little trouble with what i would expect to be a simple problem to fix.
What i am trying to do is this...
I have a table set up to work with an asp web page. each record in the table has a unique ID which is set to autonumber.
I also have a field which hold the URL of an image used in the asp file. The address remains the same for all of the records except that the file name it points to increments by one each time.
example:
the record ID is 173 and the file address is:
http://www.berkeley.tv/thumbnails/thumb_173.jpg
Basically, if i could automatically take the ID number and enter it after 'thumb_' it would all work fine.
Hope someone can help me out,
Thanks in advance.
View 1 Replies
View Related
Nov 26, 2006
I have this bit of code in a routine.
CustNum is a Numeric field with a number
I need to increase by one
With the code below It comes always as 1
Set Db = CurrentDb
Set rs = Db.OpenRecordset("Miracle_Cloth_Main", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!Name = NewData
rs!Cust_ID = NewData
rs!CustNum = [CustNum] + 1
rs.Update
rs.Move 0, rs.LastModified ' Go to new record
'Me.Bookmark = rs.Bookmark ' Go to new record
'rs.Close
Could someone help with the correct code.
View 2 Replies
View Related
Oct 15, 2005
Help needed on Access development.
I am endeavoring to develop a set of tables and forms to control a fireworks display. Here is my problem Master form hold display data (IE Location and date) and customer info (no problems here) the sub form holds the the products and how they are fired. This is my problem This form shows several fields Auto number for key field, number for child link (Display ID), number for data, number for data, text and the next is also number (This field needs to be consecutively numbered for each display) therefor on setting the form property to open as new form (Display)the first row of the record set begins with the first shot. How can I code the procedure and what property of the control box should be used.
View 4 Replies
View Related
Apr 12, 2006
I have a field called EmployeeID, and it has text as data type. The format that I wanted to use for entering an EmployeeID was D01, then the next would be D02, etc.
Is it possible so that when a user enters all details of one employee and wants to create a record for a new employee that the EmployeeID will be automatically incremented. Does anyone know any VBA code which I can use to to do this? Or should I just use an input mask?
Thanks
View 12 Replies
View Related
