Anyone have have any suggestions on how to create personlised job numbers instead of relying on auto numbers?
At the moment I have to freetype them in when creating a record & I would like to work out how to automatically generate them.
maybe something like
1030:101
1030:102
1030:103
1030:104......and so on.
Any suggestions would be much appreciated
thanks. :)
hi,
I have a table that has results from races, eg.
Race No, Racer ID, Time taken.
i want to have a position field that will say which position the runner finished in. This is complecated as there are many different races.
At the moment the best thing ive been able to do is set up a query with a parameter on the race no. then the time taken is put in order and i have to manually put in the positions.
Manual is bad!
does any one have any idea about how i could make this automatic.
Thanx,
MAtt
I've searched through the forum looking for what i want to do with no luck so hopefully one of you kind people could help.
I'm setting up a DB that logs in a batch of parts to which i generate a batch no.
As the batch progressess through the works when a problem arises we generate fault records (I've set up another table for this and linked the batch no fields together). I want to put a serial no. on this form consisting of 'year' 'batch No.' and ?'Auto number'? From reading posts is it best to put the 3 items in seperate fields (less hassle)
My 2 questions are
1, how do i extract just the year from a date field?
2, This is the main problem. The auto number. If i use auto number to generate a unique number to log the record the number will be massive in no time at all. This is fine as the unique identifier for the record in a table but not for the serial number of the printed report. Idealy I want to have the batch number then a serial number that resets to 1 on a different batch No. For example
21098/1
21098/2 Batch No./serial No.
21098/3
22309/1
22309/2
22309/3
Just some more info this database is a multi user database split front and rear.
I hope i've explained this well enough, many thanks for your help in advance
cheers
EQ
Hi Guys,
In my table design view I've set my primary key to auto number and the 'New Values' to random. The problem is, sometimes the numbers generated are too long or negitive. Is there any way I can specify the limit of the numbers to be generated and keep them positive numbers?
Hi all,
I'm currently working on a simple inventory system that i keep trying to improve and making it much less simple :(
In my product table I have a min and max level. What I'm trying to achieve is to set up a form where i can select a Supplier, then hit a button (or another form of trigger) and it comes back with all the products for that supplier where we are low on stock ie
Product - Supplier - On Hand - Min - Max
Prod1 - Supp1 - 2 - 2 - 4
Prod2 - Supp1 - 4 - 2 - 4
Prod3 - Supp1 - 1 - 2 - 4
So if on my form I selected supplier Supp1 it would automatically fill in order detail for Prod1 & Prod 3 for 2 & 3 units respectively.
Now I'm pretty sure i could do this as a report as the formulas are relatively simple, however i want to pull the data on a form so it creates an actual order with order id etc.
Is there a way i can do this?
Any help would be appreciated, cheers
I'm building a database for my folks's volkswagen car show (www.texasvwclassic.com) if anyones interested ;)
So I've got a Registrations table and a Classes table. Each person that registers for the show is an entry in the Registrations table, and a field in that table is Class, with a relationship to the Classes table. That is all set up and working fine, but I want to add an EntryNumber field generated at time of checkin, so some VB function I can call when the checkbox is clicked in the form, or a query based off the value of the checkedIn field. I want the EntryNumber field to contain a number like 0503 (CCNN) where CC is the class number (class #5) and NN is a counter of how many people have checked in so far in that class (so 0503 would be the 3rd person to check in for class #5). I can get the class number fine, and I can prolly figure out how to stick em together into another field, but im not sure how to generate the NN part.
In the classes table, each class has a sub-table listing all the registrations that have entered that class. I need like an autonumber field in each of those subtables, but I dont understand how to add something like that.
I hope this makes sense, as always any help is appreciated. =)
How to generate random project numbers that reset automatically every month? I want to generate project numbers that look like this:
1234/06/12
where the 06/12 stands for the month and the year while the 1234 is a randomly generated number.
I want the random number to be four digits long.
I already have a number of projects with similarly generated projects from an existing database. Can I import these projects without changing their project number?
I have a column containing an id that consists of the first two letters of a weekday followed by an incrementing number. For example, for Monday, I have "MoA1" "MoA2" "MoA3" ... "MoA11".
The problem is that when I sort my list, it is ordering it: "MoA1" "MoA10" "MoA11" "MoA2" "MoA3" etc. Currently, my order by property is set to
MID(TABLENAME.SORTFIELDNAME, 3, LEN(TABLENAME.SORTFIELDNAME))
My autonumbers are not in order for tables.As in for example for a customer
customerid customer first name customer last name
17 john parker
18 james johnson
19 alex scott
This is right at the top of the table the first sets of data,i want it to be
customerid
1
2
3
4
5
,etc
Why does it have a random number like "17" when I DO NOT have the first 17 customers !
I am trying to come up with automatic line numbers for each specific order. So for example, I have job number 123456 that has ordered 3 items, what I would like is that item 1 has a field with a 1 in it automatically, and item 2 has a 2 in the field and so on. But the trick is that when order 123457 gets entered and has 5 items entered, I would like it to start over at 1 and go to 5. Does this make sense? Is it possible to do this in a table? Or can this happen on the form? That I assign a value to a field. I am using Access 2010, have been for a few years now, but I have lots to learn.
View 14 Replies View RelatedI'm having multiple problems with my database like things such as -
i'm currently working on the Query 2 - On the Phone database (ignore Query 1) and i want to search for multiple plot numbers preferably in one parameter prompt with a comma to seperate numbers. (this could be a multitude of numbers so i would like to be able to input as many as needed). Also when i do search on this query since the Criteria is a 'Between' Value i would expect everything between the 2 numbers input to show up - but a lot of numbers out of the range show up too - why is this? (The Numbers are like "69 to 136" and they will show up - but 1-69 and 136-170 would too
I would also like to implement the search results from Query 2 into the Form i currently have made but it just opens up a access table when the search is made?
i cannot link my database as it is too big for the server - But here are the Criteria for Query 2:
Plot No - (criteria = Between [Enter First Plot No:] And [Enter Last Plot No:])
Site - (criteria = Like "*" & [Enter Site:] & "*")
Product - (criteria = Like "*" & [Enter Product:] & "*"
The Query is the one im most concerned about , i can live without a form.
Ok so in excel I have some numbers that are stored as text. The reason being that they are zip codes and some begin with 0 and excel doesn't want numbers to start with 0....so when I import these into an access field that has an input mask for zip codes...will it convert these correctly since the field is a text with input mask?
View 1 Replies View RelatedI have 2 fields that I would like to automate if possible
One field is called "p/o number" and another field called "line no"
These fields are part of an ordering database
Let say I have 200 items to purchase form 10 suppliers
And form example 20 items from each supplier
What I do at present is put the order number on each line item and the line number
example
p/o number line no
1 1
1 2
1 3
2 1
2 2
2 3
2 4
What I want to do is just put the first po number in the required line . Put the first line number in i.e. "1" and the macro will complete all the p/o numbers and line numbers for me as per the ones marked in red.
Example
1 1
2 2
3 3
2 1
2 2
2 3
So I've read a good 100 postings or so so far on the subject of how generating a unique identifier through any other means than an autonumber can be dangerous. But I can't resist the opportunity to simply ask if there is a way to make this possible, if only through a calculated field...
In my single user database, I have an employment table with an autonumber [EmploymentID] field as its primary key. The employment table exists in a one-to-many relationship with it's child, the income table where each form of income is recorded as it is recieved so that one form of employment (or employer) can produce many unique forms of income (like weekly paycheques).
I would like to be able to generate an identifier for the income table that is a combination of the parent [EmploymentID] field and an increasing number to produce something that looks like this:
1006789-001 -|
1006789-002 -|--> Same EmploymentID
1006789-003 -|
1006790-001 ----> New EmploymentID with a reset numeric
Any ideas?
~Chad
I have three fields, One called Key, one called IS and one called BC. I want the table to automatically copy the number from the IS field to the Key field to be used as the primary key. However, this field does not always have a value, when this occurs I want it to take the value from the BC field. Is this possible?
View 3 Replies View RelatedHey yall,
Here's one to stump the band.
I must find a way to generate multiple records that consist of a ticket number as one field, and whether they have been used or not as another. The ticket numbers need to be sequential, with an increase in value of 1 over each previous record.
The ticket numbers will be seven digits long. Users, or their supervisors, must be able to generate a block of ticket numbers, by designating the first and last number, and thus they decide both the ticket number value range, and the number of ticket numbers to be generated at the time.
The difference between the first and last number might be a key to record generation, but then, can you use that to tell the system to add a specific number of records? And, other than appending pre-existing records,can you command the generation of records at all?
Thanks for any answers on this.
I am trying to copy a field multiple times based on a number in an unbound field on a form. As well attach a consecutive number base on the number enter in the unbound text box.
Like this
[Table1] . [Field 1]
A70-3886-01
A70-7003-01
copy this many times unbound text box on form in to 3
[table2] . [Field1]
Spool Number
A70-3886-01-01
A70-3886-01-02
A70-3886-01-03
A70-7003-01-01
A70-7003-01-02
A70-7003-01-03
I dont know what to call my problem but it's not much for experts, but too much for noobs like me. I've been trying to generate a random output in one of my forms which will get it's data from one of the tables that i made (Ex. frmForm1 will generate random data from Table1, and the fields are "Name", "Age", "Sex", etc.).. i've tried doing Randomize but it doesn't work. Pls advice, and thanks to whoever will give solution to this..
View 3 Replies View RelatedI need to generate a pre-formatted letter from within MS Access, where the name, address and other information will be pulled from the current record on an Access Form.
Basically it is like a welcome letter I could send to any new client I enter into the data base. Since I am doing one letter at the time, mail merge in MS Word is not an option
What would be the easiest way to do this?
Thanks
I have a small .mdb for invoicing. Till now I had to put invoice number, which had two parts, manually for the first part, for example 001 or 002 and automatically for the second part as Date part. So I had, for example 2006 or 2007. The complete number was, for example 001/2006 etc.
The problem was New Year. Now, all previous invoices from 2006 have 2007 extension and the first parts are not starting form 001 but they continue.
I would like to create a unique ID number (currently the Primary Key) for a table in Access 2000.
I am using 4 elements for the current manual entry, and want to automate it after I make one selection on the Combo box.
The elements are:
1: The species of the wood I am entering into the inventory.
2: The last two digits of the current year.
3: A ":" as a separator.
4: A sequential number unique for each species of wood as purchased in the current year. This would reset when the year changes.
For example, I can use the Combo Box to select either Ash, Maple, Myrtle or Elm. When I make the selection, I want the system to automatically generate the rest of the ID code.
If I had the sixth piece of Maple for 2007 I would select Maple in the box, then the generated code would be Maple07:006. The next piece of wood might be the fiftieth piece of Myrtle, so it would be Myrtle07:050. On Jan 1, 2008 the sequence numbers would all reset to 001 - so the first piece of Maple that year would be Maple08:001.
Can this be done? It would help me to avoid the problem of skipping numbers when I loose track of the last piece of the same species I entered. Can I do this with a simply formula macro, or would it need VBA?
And can this code ten be used in the Primary Key for that table - and will I need to re-enter all the 300+ pieces I already have in the table?
I had thought this could be done with a 3 field key - but I'd like to keep it as simple as possible.
i need to automaticly Generate a Primary key for RMA Numbers so i want Previous forms number + 1 and i want to start with Number 10832.
View 2 Replies View RelatedHiI have a form where I am entering lot of records where only the date is different . In order to shorten this I need a querry that will (activated with a button on the same form) generate records in some other table (or in the same) with those records entered in the form and will have a field where will be put single dates in timeframe of starting and end date that I can enter in the form instead of single date.Thanks in advance for your time!
View 2 Replies View Relatedim trying to fill a temporary table, with the following SQL. While it successfully builds the table, its not pulling in any values. which are the important part. Can anyone see any problems?
'Dim stDocName As String
'Dim stLinkCriteria As String
Dim strSQL As String
strSQL = "SELECT TLOG.keyID, TLOG.Analyst, TLOG.EntryStatus, TLOG.Value, TLOG.FundingStrings, TLOG.Description, TLOG.PurchaseOrder, TLOG.Vendor, TLOG.FiscalYear, LPO.[PO Title], TAMD.Amendment, TAMD.DOR, TAMD.BCD, TAMD.FCD, TAMD.SSD, TAMD.AmdComments, TAMD.CertifiedDocument, TMOD.Modification, TMOD.MSD, TMOD.MRD, TMOD.SLD, TMOD.SCD, TMOD.OSD, TMOD.ModComments, TMOD.ObligationDocument INTO [tblSummery]"
strSQL = strSQL & Chr(10) & "FROM ((([lkup PO] AS LPO INNER JOIN [tbl Log] AS TLOG ON LPO.[Purchase Order] = TLOG.PurchaseOrder) INNER JOIN [tbl SupDoc] AS TSUP ON TLOG.keyID = TSUP.keyID) INNER JOIN [tbl AMD] AS TAMD ON TLOG.keyID = TAMD.keyID) INNER JOIN [tbl Mod] AS TMOD ON TLOG.keyID = TMOD.keyID"
strSQL = strSQL & Chr(10) & "WHERE TLOG.keyID = " & Me.lstResults.Column(0)
DoCmd.SetWarnings False
If tableExists("tblSummery") Then CurrentDb.TableDefs.Delete "tblSummery"
DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings True
ps i learned my lesson about naming tables with spaces this morning.
Hi,
I'm trying to make a database where a Date and a Service Number is entered into a form.
I have 2 buttons, one to automatically enter the Current Date and the second to automatically enter the Service Number.
The service number is incrimented by 1 each time and reset for every day.
eg.
Date Service Number
January 1, 2005 1
January 1, 2005 2
January 1, 2005 3
January 2, 2005 1
January 2, 2005 2
January 5, 2005 1
January 5, 2005 2
I already have the button that enters the Current Date. However, I am having trouble with the second button.
How can I code the button so that it searches the table for the Current Date and finds the record with the greatest service number for that date? After this is done, I want to create code that will automatically enter the next service number into the Service Number Textbox on the form.
eg. Using the Example Table Above
The date January 5, 2005 is entered into the Date Text Box and the Search Next Service Number button is pressed. The number '3' should show up in the Service Number Textbox
Sorry if its hard to understand, but thanks anyways.
I just realized that I placed this in the wrong forum, Sorry for the inconvenience.
Ok here is the next step i need to figure out :)
I need to have unique ID for each of my record in my form, and it should be created automatically.It should look like this:
AB060106-1
AB060106-2
AB060107-1
AB060107-2
AB060107-3
AB060108-1
and so on.
Idea is to have two or three letters at the front that never change, then 6 numbers that represent current date (year,month,day), and then incrementing numbers for that day.Whenever day changes, this last number starts from 1 up to 999 and next day it resets to 1 again.
Any ideas where should i start from? Thanks