Add year to primary key number

[KateB]

I have created a database to record results provided by a local laboratory.
I was told that the Lab number on each report was unique, so decided that
should be the primary key. I have now discovered that I was mis-informed.
At the Lab they actually put the year before the Lab number (meaning these
numbers are 're-used'), but it is hidden on the forms sent to us.

This means I now need to amend the primary key number to reflect the year
the sample was reported (this date is recorded as a separate field) and to
automatically combine the year from the reported date (currently dd/mm/yyyy
format) with the Lab number. What's the best way to do this? I don't trust
the people inputting the data to type the correct year in - I've already had
to add in lots of masks and checks to prevent the errors they've already
made! - so would like to automate it.

Therefore, my basic question is, should I make a new primary key field which
concatenates the lab number (current primary key) and year from the reported
date field, or is there a better way? Will it be easy enough to update the
current records (approx. 1000). Can anyone help with how to do that please
and are there any protocols on format?!

Many thanks

 

[BruceM]

Simplest may be to add an autonumber field, which you do not need to see.
You can filter by year as needed, or combine the year with the current lab
number for display purposes. If there is a date field you could have:
=Format(Date(),"yyyy") & [LabNumber]
as the control source of an unbound text box. You could use the same
expession in a query.

 

[KateB]

Many thanks for your response. I think that is the best way forward, but I
still need to make sure that if a lab number already exists in the database
the user is forced to check that it is for a previous year and prevent
duplication - it is possible that we may have to reference back to the lab on
results provided so need to be 100% sure it's correct. Is there an easy way
to do that? My concern is that if they realise they can bypass and force the
duplication rather than checking back the paper copies they will. I could do
quality checks but would rather prevent it happening in the first place!

Simplest may be to add an autonumber field, which you do not need to see.
You can filter by year as needed, or combine the year with the current lab
number for display purposes. If there is a date field you could have:
=Format(Date(),"yyyy") & [LabNumber]
as the control source of an unbound text box. You could use the same
expession in a query.

I have created a database to record results provided by a local laboratory.
I was told that the Lab number on each report was unique, so decided that
should be the primary key. I have now discovered that I was mis-informed.
At the Lab they actually put the year before the Lab number (meaning these
numbers are 're-used'), but it is hidden on the forms sent to us.

This means I now need to amend the primary key number to reflect the year
the sample was reported (this date is recorded as a separate field) and to
automatically combine the year from the reported date (currently
dd/mm/yyyy
format) with the Lab number. What's the best way to do this? I don't
trust
the people inputting the data to type the correct year in - I've already
had
to add in lots of masks and checks to prevent the errors they've already
made! - so would like to automate it.

Therefore, my basic question is, should I make a new primary key field
which
concatenates the lab number (current primary key) and year from the
reported
date field, or is there a better way? Will it be easy enough to update
the
current records (approx. 1000). Can anyone help with how to do that
please
and are there any protocols on format?!

Many thanks

 

[Klatuu]

A primary key can be more than one field. I would add a year field and make
the primary key a combination of the Year and Lab Number.
Make the Year control's default value the current year:

=Year(Date)

As to correctly entering the reprorting date, you could validate it in the
Before Update event of the control to make sure it is a valid date. You
could also update the year control if it is not the same as the default if it
is a good date:

If IsDate(Me.txtReportDate) Then
Me.txtReportYear = Year(Me.txtReportDate)
Else
MsgBox("Invalid Date Entered")
Cancel = True
End If

 

[KateB]

Thanks for the suggestion but I'm having real problems now! I should've said
I'm a 'dabbler' in database design and learn as I go along (and by reading
other posts!) so please excuse my further questions.
I can't get the =Year(date) to work so I must be doing something wrong
there. I've tried various combinations of the field name and date with
brackets but I get #error, #name, or incorrect syntax. I've read that any
date field has to be a complete date, not partial - should it be a number
field?
I haven't checked out the validation yet because of the other errors, but I
should say that there's usually a week's delay between the test being done
and us receiving notification and getting it input - will the validation
prevent, for example, a date at the end of Dec06 being input in Jan07?
Many thanks for any help you can offer.

 

[KateB]

Have got the year to work (had to make it a number field), but can't get the
validation to work. I assume its because one field is date and the other
number, but I don't know how to solve that.

 

[Klatuu]

I think the correct syntax should be =Year(Date())

I am not sure what validation you are trying to do. Can you please remind me.

 

[KateB]

The validation was to make sure the year field matched the year of the date
received field, if that makes sense. For example, if a result received on
28th Dec 2006 is entered on 2nd Jan 2007 I need the year to be 2006. Is it
possible to override the default (which would be 2007)?