year and autonumber field to create a primary key??? (1 Viewer)

[Emma]

I have a database with three fields: year, autonumber field and object type. My client wants me to create a unique record number to be used as the primary key using the year field and the autonumber field, so for example:

year autonumber object primarykey
2001 1 pot 20011
2001 2 flint 20012
2000 3 pot 20003

Now, I know everyone will say "but you have a primary key in the autonumber!", which I do, however other external systems which this system will link into uses the date and the autonumber as a primary key. The primary key therefore has to be made up of the year the record is entered and the autonumber field.

I am at a bit of a loss with this, I tried to create a query that took the date field (whose format is "yyyy" and input mask is 9999), and the autonumber field (access autoincrementing) and concatonated them. Unfortunately the results of my query were:

date autonumber object primarykey
2000 1 pot 16/01/011

Access took the date NOT the year the record was entered!! Also, as the SQL query which created the primary key number was written as the data source for the form (so that when a new record was created the primary key was filled in on screen), the 16/01/011 data wasnt saved to the table.

I am currently feeling somewhat confused by all this, and desparate for a tidy solution to my problem. Unfortunately, much as I would like to only use the autonumber as my primary key, and as far as database design - this would be much easier, the needs of the client must be meet.

Also, should I be able to sort saving the correct information into my field (as for example 20011) can I then have other related tables using this value in the database? So for example, I may have a table of jobs that this object has been related to, so need to record:

jobno objectno
1000 20011
1002 20011

etc.

I am frustrated and confused by what I though should be a fairly easy problem to solve... Any thoughts/ideas (apart from telling the client that they cant have their database in this way ...) would be very gratefully recieved.

Regards
Emma

p.s I have also posted this on the VBA and Forms boards in case anyone there has any ideas!

 

[helena]

See my reply in tables section...

 

[Talismanic]

The other mentioned thread is here.

Emma, here is an example that I put together that does something similar to what you want to do. All you would have to do is adjust the date format to get it to work for you.

txtUniqueNumber = Number & Format(Date, "ddmmyy") & ID

txtUniqueNumber is the control (text box) where you want the unique number to appear
Number is the box where you put any number
Date is where you put the current date (the format will make it appear like 11601 instead of 1/16/01)
ID is the number that is generated by the autonumber

In my example where number = F1, date = 1/16/00, and the id=1 i get this result F11601001

I believe you can arrange the mm dd yy in any order to suite your need. If you remove the YY you will just get the ddmm.

Also be careful posting the same topic in different forums because it upsets people when they take the time to answer a thread in one forum to find it has already been answered in another.

Good luck!

[This message has been edited by Talismanic (edited 01-16-2001).]

 

[Emma]

Cheers Talismanic! With a bit of amending, I got the code working and now have a nice number system!

Can I also ask a further question relating to the problem of using a year and autonumbered field to generate a unique identifier?

Is there any way that on each new year the autonumbering returns to 1 , so for example:

year autonumber object primarykey
2000 1 pot 20001
2000 101 pot 2000101
2001 1 flint 20011

Will I need to create my own autonumber field using the year as part of the index?
(Or am I getting a bit adventurous?!) It occured to me that just appending the autonumber to the end of the year may end up with some huge numbers in about five years time (bearing in mind I think that about a thousand records a year are entered!).

I would be very grateful for any further comments - I am still gradually learing VBA mainly as I go, and may sometimes have ideas beyond my ability!!!

Apologies also for also posting on all the different boards, sometimes I am not sure which board to post on so go for all, however I shall make sure I put my questions to one only!!

Many thanks for all your help

Emma



[This message has been edited by Emma (edited 01-17-2001).]