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. :)
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!
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!
I want make one database about office register. I gave primary key for auto increment serial number . That is working fine. Now problem is some times I want delete client name in between table .That is maybe first row or 5th row or anywhere . at the time the row deleted which is i was selected but the primary key is not update . for example I want delete 8th row . after deleting I not seen 8 in primary key place i have seen only ....,5,6,7,9,10...... etc. how to give auto increment and decrement in the primary key......
I am new in ms access , I want make one database about office register. That is Have done . I gave primary key for auto increment serial number . That is working fine. Now problem is some times I want delete client name in between table .That is maybe first row or 5th row or anywhere . at the time the row deleted which is i was selected but the primary key is not update . for example I want delete 8th row . after deleting I not seen 8 in primary key place i have seen only ....,5,6,7,9,10...... etc. So how to give auto increment and decrement in the primary key......
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
Very simple question: I just moved a simple daily rainfall record spreadsheet to my Access gardening database. Fields are date, rainfall in mm, temperature etc. and a single record is added for each day. How do I get the date field to increment automatically to 'previous day's date +1'. (No, I cannot use the shortcut key [Cntrl-;] to insert today's date in the table because records are only entered periodically, several days at a time, from a notebook.) Advice appreciated.
tblShops have an ID, an adress, bussines name, owner name, etc every Shop can be inspected many times. A inspection can be futile if the store is closed. If the store is open, the inspection is made, and as result with can have “with irregularities” “without irregularities”. Two or more inspections can be made the same day (if the n – 1 time the store was close).
So, here is the basic select:
SELECT tblBusiness.idBusiness, tblBusiness.BussinessName, tblInspection.InspectionDate, tblnspeccion.InspeccionTime, tblInspection.Open, tblInspection.Irregularity FROM tblBusiness INNER JOIN tblInspection ON tblBusiness.idBusiness = tblInspection.idBusiness;
What I need is to get the LAST inspection where the bussines was open and with irregularies. One inspection per Business, but of all business. Ah, and if there ir a newer inspection but the business was close, it doesn´t count.
I tried “Group by MAX” with InspectionDate, but it just didn´t work. For example, it showed me two inspection of the same business, not “the last” of that bussiness (it was easy to filter the Open field and the Irregularity field).
I suspect that what I need to solve this is to use two or more SQL stament, two in one., something like that. But I´m in a hurry and I just can seem to get it right.
I hope you can help me with this. Thanks for reading anyway.
P.S.: the fact that there can be more than one inspection per day can be a bitch =(
but there is a "InspectionTime" that can save that". Mmm the problem is that one inspection can be made 1 july around 20:00, but the seccond on 2 july 01:00. But we store the two inspections in 1 july... so... that mens problem.
But that issue is not so important as the previous problem up there (well, in order to solve the seccond I need to solve the firts...)
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"
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?
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 ?
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.
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.
I have created a very simple Access database with a CompaniesTbl, ContactsTbl and CallsTbl. The database is used to record telesales contact with customers. The tables are set up like this:
The CompaniesTbl has an AutoNumber Primary Key field called CompanyID.
The ContactsTbl has an AutoNumber Primary Key field called ContactID and a foreign key called CompanyID.
The CallsTbl has an AutoNumber Primary Key field called CallID and foreign key called ContactID.
The relationships are set up in the relationships window and referential integrity imposed.
Twice now, while editing a contact record, an error has occured. Task Manager has had to be used to get out of it. The error is either a 'run-time error with a message that does not mean anything' or it says 'unrecognized database format' (!?).
If you open the database again the ContactID field in the Contacts Table is no longer a Primary Key field. If you look in the relationships window - the relationship between the Companies and Contacts tables no longer exists.
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
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.
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)
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?
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.
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...
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.
