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!
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!
