I have been trying to incorporate an access DB in to our business for sometime, but keep coming up against the same issue. We have a numbering system for the jobs we do, which was invented many years ago and cannot be changed. It is in the format "month/Year/unique 3 digits" the 3 digits are sequential, i.e. 02/07/123 ... 02/07/124... etc. I will need this all in one field.
So far I have managed to get the month and year (easy enough) with the expression "=Month(Now()) & "/" & Right(Year(Now()),2) & "/"" Now I need to get the sequential 3 digits at the end, however, I am not sure of any way to do this and it has stumped many people. I have a field that is autonumbered, but don't seem to be able to add this to the end of the expression.
If any of you have any suggestions I would be greatful - bear in mind I am only a begginer at this.
Anyone know how to create a combo box that has unique month and year entries from a table (month in one column and year in another - i.e. 2 columns)?
I've been able to get the textbox of the combobox to output the correct format using a custom format but it does not affect the combobox data. Also, I'm not sure how to separate this by two columns...
I'm creating a database using existing data from an excel file full of contact details. What I need to add is a queue type system where each contact in the database has a "Place in Queue" number which is unique obviously.
Lets say Alan is number 1, Bob is 2 and Chris is 3. They have these corresponding numbers in the queue field for their entries.
Now what I need to have, through use of a form, is a way of changing Chris from number 3 in the queue to number 1 and thus have Alan automatically shift down to number 2 and Bob to 3.
I have 2 page tabs on a form, and I want an incremental number assigned to each new record on that page.
Been doing some research and this is what I came up with:
Private Sub Form_Current() If Me!tabSupplier.NewRecord = True Then Me!tabSupplier!DocumentNo = Nz(DMax("DocumentNo", "Product"), 1000) + 1 End If End Sub
The page nane is: Supplier Page Index is:0 The control to display the incremental number isocumentNo (tblProducts)
it does not work and I am getting no error message.
I am trying to create a form that creates an automatic identifier with a value one greater than the previous identifier. I was able to create something that updates in batches of two: That is, I end up with identifiers that go 1, 2, 2, 3, 3, 4, 4, etc. How do I get it to create consecutive identifiers (1, 2, 3, 4, etc.)?
I am very new to db programing so please forgive for such a simple program.
Here is the deal. I have a db for about 500 students.
I would like the table [Students] to create a unique ID "number" for each student. I would like the number to be based on information from the same table. For example if the student's name is John Doe and his number is 555-1212 then I would like to the id to be JD1212. I plan on using more than name and phone number but this was just to show you, what i am trying to get done.
I can do this using a form and a calculated control, but I can't get the table and form to kiss and make up. I was also told you can't save data from a calculated control in a form to a table and it is gernerally not a good idea, so I was told.
Got my job database going quite well, at the moment, it generates a job number form the first 4 letters of the customers name, and the ID (autonumber), so for example mr smith's job number would be SMIT0012 (assuming the 12th entry in the database) Now this works fine, tables are linked and the database access the tables via a mapped network drive for other pc's (2) to use. But the problem is, if the database user were to be at a remote location with a laptop, how could this work? I could use a local copy of the tables but then of course the ID's wouldnt match! Can anyone offer any ideas on this?
i was wondering how to go about doing this, i currently have a query which returns all the payments due in the next month, going to a report which acts as an invoice. I was wondering how to create a unique id for each invoice and store the last id so that i can automatically itterate it later
I'm working on a database that has to be done in Access and in coming up with a unique ID for each worker I want to take the first letter of the first and last name and the last 4 digits of their social to create the UID for the table. So for FIRST_NAME:Homer LAST_NAME:Simpson with SOCIAL:123-45-6789 his UID would be HS6789. Is there a function within Access that would allow me to do that, or is that something that's just too advanced?
What I have is a small POS system I'm trying to create a unique invoice number for each transaction (multiple records)
I have two tables, one named "CurrentTransactions" and one named "Past Transactions".
I have everything working except the invoice number my system works by adding pre defined records into the table "CurrentTransactions" and once the sale has been finalised it transfers the data to the "PastTransactions" table.
take the last number from the "PastTransactions"."InvoiceNumber" to create an invoice number in the "CurrentTransactions", and I want to be able to do it using an expression in the default value property as opposed to in the form ( I've tried the form way but using Dmax would require me to almost rebuild my entire POS system )
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.
I have a query that groups the data by customer. How can I create a "Transaction Number" field where I assign a sequential number to each invoice, starting at 1 with the oldest invoice and increasing the sequential number by 1 with each invoice. I need to create this field in a query without code.
How I can create a sequential number in a field that looks like this:
Year-XXX-00
(Example: 15-100-00 -- the next sequential number would be 15-101-00)
I don't want to hard code the year, and the last 2 numbers must be entered manually.
It gets even more exciting --> this number has to be able to be duplicated in a table. Those last 2 numbers is a revision number. We might have a 15-100-00, 15-100-01, 15-100-02, etc.
I have a TV Episode database. In the database I want to number each episode of a show. How do I autonumber a show's episodes when sorted by season then episode and have the autonumber restart at 1 when it hits a new show title?
The situation is that I have a table named patient and a form named [patient1] where it holds a textboxs named "MFC_Seq_no" and another to hold [date], now, I manged to do a manual auto numbering for "MFC_Seq_no" using the following VB :
Private Sub Form_Current()
If Me.NewRecord Then On Error Resume Next Me!MFC_Seq_no.DefaultValue = Nz(DMax("[MFC_Seq_no]", "Patient"), 0) + 1 End If
Whenever user create new record, the system will automatically store the next number in the new record.
Now, I've be requested to code that system to start again from 1 as value for "MFC_Seq_no" whenever there are changes in date field (i.e. next day in the date field)
Is this doable and how?
Q2. how to duplicate an entire record with a subform, from another table, in it?
One of my forms has a text box which is bound to a field called teenumber. This form is set up that "on current" has code
If Me.teenumber = 0 Or IsNull(Me.teenumber) Then Me.teenumber = Nz(DMax("teenumber", "tblteeofftimesshotgun"), 0) + 1
This enables the text box teenumber to be auto filled with sequential numbers growing by 1 for each record. This database is for a golf tournament and this form enables user to set up tee times for shotgun start for the players.
When the user gets to tee number 18 or whatever the last hole on the course is the teenumber field needs to be reset to 1. With the above code I a unable to do this.
Creating a form against a table with the following fields
EC Project Originator Title Full description Effectivity Etc....
Easy enough to create the Project combo box, problem comes with trying to systematically assign the next EC number.
When I select a Project from a combo box I want an EC assigned with format ECXXXX-### where XXXX is the project number from the combo box and ### is a sequential number. ECs should start at -001 for all projects.
Do I need to add an EC suffix field to hold just the ###? Then what?
I have created quite a substantial and effective database for a small gliding club with all the major data tables being linked. I need to be able to re-index tables periodically so that running totals, which are needed to calculate statement balances, works correctly - all well and good. The only problem is that the process of re-indexing requires the data in the main table to be stored temporarily and the original data deleted. When the temporary data is appended to the main table the auto-numbering just keeps clocking up.how to reset the auto-numbering in a local table.
Is there a way to format the random auto number to just be 4 digits long? Or does anyone know how I can programatically create an auto unique field? This number has to be stamped on a key so it can't be longer than 4 characters but it has to be unique. Any help would be greatly appreciated.
Hi all I have been discussing DB table design with a colleague and we have a difference of opinion with regards to primary key ID fields .
My argument is that they should not be actual data but separate, that is an unique record identifier typically an autonumber type. I'm sure I picked this up from reading Access manuals.
My colleague's argument is: if the data contains a unique field, e.g. a unique project number in a table of projects (no project should be listed twice) then use the project number as the primary key. Her second argument is the byte size of an autonumber is 8 bytes whereas you can use a smaller data type to hold the project number thus saving space.
My opinion is that her first reason is flawed as it assumes no change to how projects are identified and that her second argument is irrelevant in today's Gb and Tb systems.
Without trawling through my Access books and the internet for opinions, I would like to know what your opinions are on this subject. I would appreciate good reasons which ever side of the fence you are on. I'm quite willing to be proved wrong and change my ways. Cheers Imperator
Two tables, in one there are Items and services, in the other purchases.
1st table: ID - autonumber primary key Product Name - Text Quantity - Number Barcode - Text
2ndt table: ID - autonumber primary key Product Name - Text Quantity - Number Barcode - Text Client - Text Puchase Date - Date
How can I enter data to my 2nd table using unique fields (barcode, Product name actually ID in 1st table?) Problem Is Access alows only 1 unique key field in table and that is undertandable. Is there any way out in my situation.
barcode will be entered using barcode scanner it can be only text field but information like product name must fill in automatically, Product name must be drop down box if one wan't to select it manually then barcode should fill in automatically.
Please Attach your DB if you have time to look into this issue.
Hi, I have two tables: tblOutTransmittals and tblTransmittedDocs. A document is sent with a transmittal document which the recipient signs to confirm that they received it. I've created a query that will show all the drawings/documents and the information about the associated transmittals.
What I want to do is show display records with a unique CCNum and preferably the one with the most recent TransmittalDate.
I've attached a screenshot and here is the sql: SELECT tblOutTransmittals.CCNum, tblTransmittedDocs.DrawingNum, tblTransmittedDocs.RevisionNum,tblOutTransmittals. * FROM tblOutTransmittals INNER JOIN tblTransmittedDocs ON tblOutTransmittals.DocID = tblTransmittedDocs.outTransmittalID WHERE tblTransmittedDocs.DrawingNum="32-35554"
I've tried it using the DISTINCT keyword but that would only work if I'm returning one field. I tried Group By as well but couldn't get that to work.
I need to ask Access to calculate a total number of records but only includes duplicate entries once.
For example: If the field entries were A, A, A, B, B, C, C, D
It would return a total of 4 (counting A, B, C & D only once).
Can anyone advise on how I can do this? My only alternative is to print the database out and cross out duplicates. Not the most interesting way to spend my afternoon.
What I want to do is create a new field that automatically populates based on what is entered into the above fields.
Essentially creating a single field with unique data generated by multiple fields.
For Example if I entered the following informaiton: EmployeeID: 1 (AutoNumber) FirstName: John MInitial: P LastName: Doe Company: FederalGov Department:Test
The new field can be generated as followed: JohnPDoeFederalgovTest
What i plan on doing with this is making this "generated" field an index so no new duplicated records can be added.
