Generating A Unique ID

Nov 2, 2004

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

View Replies


ADVERTISEMENT

Generating Unique ID From Combobox

Nov 2, 2007

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.

View 14 Replies View Related

Queries :: Find A Way For Access To Find Unique Dates And Unique Names?

Aug 1, 2014

I have been working on a simple data base for some time now (beginner level) and am still trying to improve it. I would like to do something but before that I would like to have your opinion to know if it is even possible?I have a query QryMainReport:

Start Date/Time
End Date/Time
Employee

At the moment this is what the format of my report looks like (I removed other unnecessary fields):

StartTime----------EndTime---------------Employee
12/06/2014 01:00--12/06/2014 03:00------John Smith
12/06/2014 04:00--12/06/2014 06:00------Jane Doe
13/06/2014 02:00--13/06/2014 05:00------John Smith
13/06/2014 08:00--13/06/2014 08:00------Jane Doe

I would like to do as a report. (Dates would always be from Sunday to Saturday). I am not sure it is possible to do that. I suppose first it would mean:I would have to do a query to separate the times from the dates?I would have to find a way for Access to find the unique dates and unique names?Does it mean I have to use cross tab queries?

View 2 Replies View Related

Generating Numbers

May 5, 2005

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

View 1 Replies View Related

Generating Primary Key

Jan 17, 2007

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 Related

Generating Records

Nov 28, 2005

Hey 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.

View 1 Replies View Related

Generating Records

Jan 26, 2006

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

View 6 Replies View Related

Generating Randoms

Dec 30, 2004

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 Related

Generating A Letter From MS Access

Jun 20, 2006

I 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

View 9 Replies View Related

Generating Order Numbers

Dec 12, 2006

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. :)

View 13 Replies View Related

Generating Invoice Number

Jan 15, 2007

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.

View 10 Replies View Related

Generating Serial Numbers

Jan 16, 2007

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

View 14 Replies View Related

Generating Random Numbers

Apr 26, 2007

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?

View 3 Replies View Related

Automaticly Generating Prim Key

Apr 18, 2008

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 Related

Generating Same Records Only With Different Date

Apr 22, 2007

HiI 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 Related

Problems Generating Table W/ SQL

Aug 8, 2007

im 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.

View 7 Replies View Related

Generating A Service Number Using VBA

Aug 18, 2005

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.

View 2 Replies View Related

Generating Special ID For New Record

Jan 6, 2006

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

View 12 Replies View Related

Generating A Query Result

Feb 9, 2006

I want to know how to generate a result from a query in a control of a form? For exampel I have made a SQL count function in a query and I want to use this result i a control of a form.

View 1 Replies View Related

Generating And Loading Report

Jun 27, 2005

Hi every body. I wonder how i can generate and load a report when a button is clicked on access 2000 form. I be happy if some one show me an example.Thanks

View 1 Replies View Related

Generating A History In Access

Feb 19, 2008

Is there any way to leave a trail or history everytime a record is changed? We have kids in our database that sometimes go to follow-along when our service will not work for them at the present time. These kids go in and out sometimes several times. Since there is no absolute way of knowing, we have to create a new record everytime they come back to our service. I would like to be able to see a history of status changes along with some dates. Is there anyway to do this? If anyone knows, let me know. If not, oh well. Thanks guys.

View 6 Replies View Related

Query Generating Different Results

Jun 3, 2014

If I use this query I get the results what I want

SELECT tblMainTWTTPSheet.txtRootCause, Count(tblMainTWTTPSheet.txtRootCause) AS CountOftxtRootCause
FROM tblMainTWTTPSheet
GROUP BY tblMainTWTTPSheet.txtRootCause
HAVING (((tblMainTWTTPSheet.txtRootCause) Is Not Null) AND ((Count(tblMainTWTTPSheet.txtRootCause)) Is Not Null))
ORDER BY Count(tblMainTWTTPSheet.txtRootCause) DESC;

But What I am trying to do is on a form select dates and generate data in this query. which doesnot happen even if I use the following query.

SELECT tblMainTWTTPSheet.txtRootCause, Count(tblMainTWTTPSheet.txtRootCause) AS CountOftxtRootCause
FROM tblMainTWTTPSheet
WHERE (((tblMainTWTTPSheet.date) Between [Forms]![frmStratificationOfRootCauses]![startDate] And [Forms]![frmStratificationOfRootCauses]![endDate]))
GROUP BY tblMainTWTTPSheet.txtRootCause
HAVING (((tblMainTWTTPSheet.txtRootCause) Is Not Null) AND ((Count(tblMainTWTTPSheet.txtRootCause)) Is Not Null));

View 4 Replies View Related

Automatically Generating Order Data?

Aug 2, 2005

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

View 2 Replies View Related

Help Required In Generating Report From Database

Feb 13, 2006

Hello folks

am working on report generation on performance of my department. I need help to develop in Access. If anyone can help me in developing it? i had started with tables. If u have some spare time, please mail me at nrmarthi@hotmail.ca.

Thank you
Marthi

View 1 Replies View Related

Generating Car Show Entry Numbers

Feb 3, 2007

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. =)

View 2 Replies View Related

Generating Hyperlink From Text In Access

Mar 3, 2007

I have a table with one field set as a hyperlink. I have to generate the hyperlink according to what else is going on in the record, I do this by assembling a text string which I can write to a text field in the same record. I have no problem doing this but when I try to copy that string into the Hyperlink field I get type mismatch. Incidentally if I put two text boxes on a form and bind one to the text and one to the hyperlink field the I can 'cut and paste' it OK so how come I can't do it with Access basic?

Dim stGunlib As String
Dim stGunlib2 As String
stGunlib2 = [Stock number]
stGunlib = "www.shooting.uk.com/Gunlibrary/" + stGunlib2 + ".jpg"
[Link] = stGunlib
[Picture] = stGunlib ([Picture] is a hyperlink field) it goes wrong here


I am using Access 2002

Thanks Dennis

View 6 Replies View Related







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