I Need A Table To Increment.

Sep 15, 2005

I have a database that has a few tables and in one of them (Originators), there is all the info about my loan originators including there comission level. I would like it when they turn in a certain amount of loans there commision level will increase. Is this posible? Here is my DB setup.

Tables:

Originators- 1 to many-
OriginatorID,FirstName,LastName,OrigSSN, Address,City,State,PostalCode,HomePhone,WorkPhone, CellPhone,FaxNumber,EmailName,BirthDate,Level(This is the commision level, and its in %).

Customers- 1 to many-CustomerID,FirstName,LastName,SSN,HomePhone,MobiPh one,FaxNumber,Address,City,State,Zip,Email,BirthDa te,CreditScore,SpouseFirst,SpouseLast,SpouceSSN,Sp ouceBirthDate,SpouceCreditScore,OriginatorID.

Loans- many to 1-
LoanID,LoanType,StartDate,EndDate,LoanLender,Custo merID.

Lenders- 1 to 1-
ID,LoanLender.


When a customer is added to the DB it is done with a form that has OriginatorID(which is autocreated), CustomerID(also auto) and some other of the customer stuff like phone numbers. When this happens I would like to have the originators % level increase, say after every 4 customers entered. Is it possible?

Thanks,

Scott

View Replies


ADVERTISEMENT

Field In Table Auto Increment

Feb 12, 2008

I have a table with order numbers I would like to auto increment the number part of the order when new record is added to the table.

My order numbers looks like this:

UZSK-0001
UZSK-0002
UZSK-0003
UZSK-0002

How to make access increment the number part?

View 14 Replies View Related

Modules & VBA :: DAO To Update Table Recordset - Increment

Jan 20, 2015

I have inherited an old database with a new field to add some new invoice numbers and increment the number to each recordset by + 1. Starting at 001. I have formatted the field to a Number Field and formatted it to 000"/2015". I now need to populate over 2000 records with the sequential numbers for each record.

I have created the following code as my attempt. Of course it doesn't work!

Code:
Sub AddNumbertoRecords()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim counter As Long
counter = 1
Set db = CurrentDb()

[Code] ....

View 7 Replies View Related

Queries :: Alter Table With Auto-increment Field?

Mar 10, 2015

I am attempting to update an oracle table using MS Access.

One of the fields to be updated is a primary key number field.

I'd like to use the autoincrement field to update that field.

My sql is:

Code:

Alter table testAutoNum Add column progid autoincrement (1001,2)

This sql nicely creates the new field in the table, but it doesn't start at 1001 and increment by 2; it starts and 1 and increments by 1.It is also creating an autonumber and I need it to be a number.

What am I doing wrong?

View 7 Replies View Related

Tables :: Populate Table With Repeat Then Increment Then Repeat

Aug 7, 2013

Beginner with Access 2010 - making additions to one field in a JunctionTable. Need to extend a field by adding some new numbers but have found it easier to Export to Excel - make changes and replace in accdb.

I have found an Excel formula that does what I want but starts from the number "1".

=INT((ROW()-1)/9)+1

Which starts with number "1" and repeats this 8 times then increments by one and repeats 8 times.

But I want to start with a number of choice (7270 and go to 9028). Have seen some VBA versions but none give me control of the start number. I am then able to update the second empty field on the JunctionTable from a query. (this is a fixed number).

View 9 Replies View Related

Auto Increment Value

Mar 23, 2008

Hi All

How to assign value to a field based on the value of the same field in the previous record?
Autonumber has its limitations for my purpose. Because, after I start filling up a form, I some times cancel the form filling process, and it affects the continuous numbering.

I guess the issue can be resolved by using some thing like record count facility. i.e. current' record's field value = number of records till previous record + 1.

How to exactly codify or execute the above by vba coding.

The Field Name is "SerialNo.", and Table Name is "StudentData"


Thanks

Padhuka

View 5 Replies View Related

Auto Increment

Jan 29, 2008

I am importing an Excel file with existing data using the Import wizard to build my table. It has an ID field (CID) which I would like to keep and set to auto increment starting with the next number.
Can this be done ?

View 2 Replies View Related

Round Up To Next $25 Increment

Dec 30, 2005

Hi,

Need a little help with rounding up. In A2K I have a form with a textbox that displays a security deposit. Security deposit is calculated by rounding the payment to the next $25 increment, hence a payment of 324.53 should have a security deposity of $325.00, but a payment of $325.01 should have a security deposit of $350.00. The code I am using for a datasource for txtSecurity deposit usually works correctly, but for this payment ($324.53) it rounds to $350.00. Here is the code..

=(([txtPayment]25)*25+[txtSecDepRndTo])

txtSecDepRndTo holds the $25 incremental value.

Incidentally, if the payment is $324.49 my code rounds the Security Deposit to $325.00 as it should.

Any help would be greatly appreciated.

View 3 Replies View Related

Increment Number

Oct 14, 2004

This is the problem. If I have a field that contains something like this:

GHP-K-0000D
How can I make the number portion (0000) increment by one when a check box is checked?

Thanks in advance

View 7 Replies View Related

Increment Field Value

Feb 2, 2012

i have 2 field "CLIENT ID" and "CLIENT CODE". when i post a new record, i want the "CLIENT ID" and "CLIENT CODE" to merge to give me 1 field "CLIENT". the CLIENT CODE should be prompted for eg. THO or BLT and added to the CLIENT ID which will compose of 001 or 002. The CLIENT ID should also increment when each new CLIENT record is posted.

The example should make things clear.

CLIENT ID CLIENT CODE CLIENT
001 THO THO001
002 THO THO002
001 BLT BLT001

it should look up the last CLIENT value and then for the new record just continue on the previous entry.

View 9 Replies View Related

Increment Field Values

Feb 13, 2006

Hi

I have a numeric field called FileNo and an autonumber field called FileID in a table called tblFile.
I also have a lookup numeric field called FileTypeID (with values 1 or 2 or 3) on another table called tblFileType.

I need the value of FileNo which I am showing on a Form frmFile to change dependant on the value of the FileTypeID
i.e. If FileTypeID = 1 FlieNo should start at 100
If FileTypeID = 2 FileNo should start at 200
If FileTypeID = 3 FileNo should start at 300
Then when I create a new record I need to increment by 1 the value of FileNo according to the FileTypeID

I've setup a button and attached this code to its onclick event but it only works as long as I don't change the FileTypeID


Dim B As Integer
Dim H As Integer
Dim N As Integer

Do While FileID > 0
DoCmd.GoToRecord,,acPrevious
If FileTypeID = 1 Then
B = FileNo
Else
If FileTypeID = 2 Then
H = FileNo
Else
If FileTypeID = 3 Then
N = FileNo
End If
End If
End If
Loop

DoCmd.GoToRecord,,acLast
If FileType = 1 Then
FileNumber = B + 1
Else
If FileType = 2 Then
FileNumber = H + 1
Else
If FileType = 3 Then
FileNumber = N + 1
End If
End If
End If


ViRi

View 5 Replies View Related

Modify Auto ID Increment

Aug 22, 2007

Hello,

Can someone tell me how I can change the increment in an existing database.

The PK is currently Auto ID with each new record currently 1001, 1002 and so on.

What I am trying to do is say starting monday the next new entry begins at 5003 versus 1003, in essence I am trying to change my first number to a 5 versus a 1.

Is this possible?

Thanks..
Fen How

View 8 Replies View Related

Increment Order Number By 1

Nov 27, 2007

A short question deserves a short answer:

DMax

View 1 Replies View Related

Automatically Increment To Uppercase, How?

Mar 31, 2006

in an initials field... i want it so that it could automatically increment the 3 letters to uppercase... how? if it can't be done in a table where can it be made (i.e. in a form, query etc)

View 2 Replies View Related

Primary Key With Date And Increment

Apr 7, 2006

I'm not sure if it's a simple task, keep in mind I'm new to using access. We have a need at work for a small db just to keep track of some things and access seems appropriate.

I was attempting to set up the primary key in a date / increment format.

Basically the format would be year, month, day, increment (starting at 001)

060125-001
060125-002
060125-003

etc

next day I would like the key to change according to date and reset the increment to start again at 001

060126-001
060126-002

etc

I do realize they are different data types, I'm just not sure how to attack setting it up. Thank you in advance for any help / suggestions. :)

View 3 Replies View Related

Increment Field In Query

May 11, 2007

Here's my query:

SELECT units, nation, unique_brigades,
Switch ( [unique_brigades]='A', 2000, [unique_brigades]='B',3000, [unique_brigades]='C',4000) AS brigade_number,
unit_urn_desc AS urn_desc
FROM units

What I'd like to be able to do is increment the values (2000,3000,4000) by 1 for each new row. Does anyone know a way to do this?

View 1 Replies View Related

Rounding Duration To An Increment Of 15

Mar 13, 2008

Hi all,

I am trying to get the formula to round to 15 seconds to charge rating events.:confused:

so 76 seconds will calculate up to 90 seconds..
& 91 will be 105 seconds.. and so on.

Any help greatly appreciated.

Thanks Dean

View 9 Replies View Related

Increment A Value In An Unbound Form

Feb 3, 2005

Hi Everyone,

I will do my best to try and explain what I am trying to accomplish.

I have a form that has two fields [NumbX] and [NumbY]
They way they display on my form is [Numbx] of [NumbY]. These two field show which transaction of the total number of transactions. So basically if someone comes in to do a transaction, they could be doing just one ([NumbX =1] and [NumbY] =1). If they are doing 3 transactions the fields would be [NumbX]=1 and [NumbY]=3. As they complete subsequent transactions [NumbX] increased incrementally, until X =Y.

Every transaction ends with a Print Form opening and the user confirming the printing of the transaction. In the print form I have coded it to clear the main form once X= Y otherwise retain some of the information input in some of the fields for the subsequent transactions. Right now, I have it set up that the user inputs [NumbX] manually. What I would like to do is have that automatically increase by 1 when the print form closes. This should continue until [NumbX]=[NumbY].

The fields are unbound and used solely for driving other activity (opening other forms) so autonumber is not an option.

Any suggestions??

Thanks

View 3 Replies View Related

Auto-increment Values

Dec 26, 2006

hello

I have set up the primary key as auto-increment through sql.
coursereference int AUTO_INCREMENT,
I have also entered VBA code for event handler below:

Private Sub Form_AfterInsert()
Me.Requery
DoCmd.GoToRecord , , acLast
End Sub

Every time when I delete the previous value , the next one entered is not decreased by one.
Could you please help how to set it up?

Thanks

View 3 Replies View Related

General :: Autonumber - Increment Only If Row Above Is Different

Mar 11, 2014

I urgently need to generate sequential numbers (not unique - duplicates are needed as & when) via an Update Query for one field in all my rows for an Access table .. I want to call this field SalesReceipt

Another field on the same row called 'OrderID' will be the field to 'test' against (this field value is populated via a CSV import)

I need the check/logic to be along the lines of.....

If the OrderID value of this row is exactly the same as OrderID value of the row above, then don't auto increment the SaleReceipt number ....if the Order ID of this row is different to the 'OrderID ' value in the preceding row...then increment the SalesReceipt number.

How can I go about this? Essentially I need to test against the value of a field one row above against the same field in the present row....can this be done in access (perhaps using the primary key as a way of looking up the row above...becuase the primary keys will always be one number apart between rows?)

The way I've been approaching such sales receipt number generation up until now, in Excel the formula I used was this....

=IF(A3="","",IF(A3=A2,B2,B2+1)) Assuming data to be tested against started in A2, then In B2 type '1' (this being the first sales receipt number). In B3 past the formula & drag down.

But I really need to achieve the same via Access...

View 14 Replies View Related

Modules & VBA :: Increment Date By Six?

Jan 24, 2014

I have the following code that updates the end date to be the same as the start date after the user tabs out of the start date. Instead of making it the same date, I want it to be six days later. How do I add that?

For example, user enters 11/10/13 in the start date. I want the end date to automatically update to 11/16/2013.

Code:
Private Sub StartDate_AfterUpdate()
Dim datDate As Date
If Nz(Me!StartDate.Value, #1/1/1900#) = #1/1/1900# Then
'The user removed the START data as criterion, so remove the END date.
Me!EndDate.Value = Null

[Code] ....

View 2 Replies View Related

Automatically Generate Number And Increment

Mar 13, 2007

Hi There

Looking to see if there is an easy solution to this problem I have.

I have a table which holds order information, the primary key for this table is orderID which is an autonumber.

The field in question is order number, which has a default value of "JJO"00000, which I have started manually at JJO10001, is there anyway i can get my database to automatically increment this number to save me from keep checking the last order number produced.

Lost count of the number of times I have gave the same order number to numerous customers.

Thanks

View 1 Replies View Related

Resetting Auto Count/Increment

Jul 3, 2007

I'm redeveloping a DB for a new project, so have removed all previous records from relevant tables (to start a-fresh)

One snub, the ID fields in the tables are auto increasing from where they left off, rather than from 1

If anyone knows how to reset the auto counters, it would be very much appreciated

Thanks in advance

View 4 Replies View Related

Primary Key-Auto Number - Increment

Feb 12, 2008

Hello ALL,

I want to know how to reset (to start again from 1) my primary key field. As an example i have the "Customers" table with the field "customerID" as PK now i entered many values (20) then i deleted them but i want to start again and the "customerID" value starts with 21 i understand that but i want a fresh DB (i was testing things) and i want to start from 1 can i? how? i even deleted the relation between this table and the other table but no success!

Some help please?

THX in advance

View 4 Replies View Related

Create Auto-increment In The Query

Sep 15, 2005

Hi: does anybody knows how to create auto-increment in the query?
eg.
id
1
2
3
4
...
create auto-increment in the query, Not in the table? Thanks.

View 2 Replies View Related

Need To Run A Update Query And Increment Field By 1

Dec 6, 2007

I have a table called tblProducts. Within the table I have a field called versionNumber. I want to update the version number to increment by one each time I run a query.

So if the version number is 1 after running the query it becomes 2. So far I have done the following:

CurrentDb.Execute "UPDATE tblProducts
SET tblProducts.versionNumber = "Need to increment by one, don't know how to"
FROM tblProducts;"

Please advice me how to implenent the Update query.

Thanks,
Ket

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved