Customized Primary Field of the table ?

[Nitin]

Hi All

I am new to access to my requirement can be very silly.

I have a table where I want combination of LabID and SubID to be
primary field. LabID should be auto number but still we should be able
to edit that if required. LabID should be made up of date and number(
for example YYYY-MM-####) where #### represent a 4 digit number, which
will increment with every new entry of the month.

Why SubID is required? Because somewhere down the line there can be
requirement of having multiple records for same LabID number. So I have
to enter the same old LabID number (editable) but different SubID to
make it a different record yet associated with the original record.

Any Other Suggestion Regarding Design are most welcome.

Thanks
Nitin

 

[John Vinson]

Hi All

I am new to access to my requirement can be very silly.

I have a table where I want combination of LabID and SubID to be
primary field. LabID should be auto number but still we should be able
to edit that if required.

Then it cannot be an Autonumber. Autonumbers can never be edited. You
can have an automatically assigned key if you wish.

LabID should be made up of date and number(
for example YYYY-MM-####) where #### represent a 4 digit number, which
will increment with every new entry of the month.

Storing multiple pieces of information - a date and a sequential
number - in one field is Bad Design and really not recommended!

If you MUST have this format (for compatibility with a
long-established manual system, for instance) then use a Date/Time and
an Integer field in your table as a joint primary key. You can
concateneate them in a query for display purposes. You can put VBA
code on the Form you use to enter data into the table (yes, you must
use a form, tables have no usable events) like

Private Sub Form_BeforeInsert(Cancel as Integer)
Dim iNext As Integer
iNext = NZ(DMax("[Seqno]", "[YourTable]", "LabDate = #" _
& DateSerial(Year(Date()), Month(Date()), 1) & "#")) + 1
If iNext > 9999 Then
MsgBox "You've done too much work this month. Go home.", vbOKOnly
Cancel = True
End If
Me!txtSeqno = iNext
End Sub

Why SubID is required? Because somewhere down the line there can be
requirement of having multiple records for same LabID number. So I have
to enter the same old LabID number (editable) but different SubID to
make it a different record yet associated with the original record.

Either a LabID refers to one instance of one entity - or it doesn't.
It really sounds like you need a second table in a one to many
relationship to this table. What Entity - real-life person, thing, or
event - does a LabID model? What would it mean to have two of them?



John W. Vinson[MVP]

 

[Nitin]

Thanks John for the response

Let me restate the requirement. I know my design is not good but I
don't know the limits of Access.

As soon as a sample comes for analysis in from any of the client a
LabID number is assigned to the sample which is a unique number. Now
after the analysis we come to know that the sample has 3 subsamples and
these should be analized separately and reported.

So we need three line items in the report eventually for the same
LabID. That's why I decided to put a SubID which will not be there by
default but if subsamples are there we will have A,B,C..... And in
final report the display will be 2006-08-0036A 2006-08-0036B ....

Thanks
Nitin

Hi All

I am new to access to my requirement can be very silly.

I have a table where I want combination of LabID and SubID to be
primary field. LabID should be auto number but still we should be able
to edit that if required.

Then it cannot be an Autonumber. Autonumbers can never be edited. You
can have an automatically assigned key if you wish.

LabID should be made up of date and number(
for example YYYY-MM-####) where #### represent a 4 digit number, which
will increment with every new entry of the month.

Storing multiple pieces of information - a date and a sequential
number - in one field is Bad Design and really not recommended!

If you MUST have this format (for compatibility with a
long-established manual system, for instance) then use a Date/Time and
an Integer field in your table as a joint primary key. You can
concateneate them in a query for display purposes. You can put VBA
code on the Form you use to enter data into the table (yes, you must
use a form, tables have no usable events) like

Private Sub Form_BeforeInsert(Cancel as Integer)
Dim iNext As Integer
iNext = NZ(DMax("[Seqno]", "[YourTable]", "LabDate = #" _
& DateSerial(Year(Date()), Month(Date()), 1) & "#")) + 1
If iNext > 9999 Then
MsgBox "You've done too much work this month. Go home.", vbOKOnly
Cancel = True
End If
Me!txtSeqno = iNext
End Sub

Why SubID is required? Because somewhere down the line there can be
requirement of having multiple records for same LabID number. So I have
to enter the same old LabID number (editable) but different SubID to
make it a different record yet associated with the original record.

Either a LabID refers to one instance of one entity - or it doesn't.
It really sounds like you need a second table in a one to many
relationship to this table. What Entity - real-life person, thing, or
event - does a LabID model? What would it mean to have two of them?



John W. Vinson[MVP]

 

[John Vinson]

Thanks John for the response

Let me restate the requirement. I know my design is not good but I
don't know the limits of Access.

What limits???

As soon as a sample comes for analysis in from any of the client a
LabID number is assigned to the sample which is a unique number. Now
after the analysis we come to know that the sample has 3 subsamples and
these should be analized separately and reported.

Then you should - I'd almost say must! - have TWO TABLES: a table of
samples related one to many to a table of subsamples. It may well be
that many samples will have only one subsample ("the sample") but I'd
really recommend using two tables nonetheless.

So we need three line items in the report eventually for the same
LabID. That's why I decided to put a SubID which will not be there by
default but if subsamples are there we will have A,B,C..... And in
final report the display will be 2006-08-0036A 2006-08-0036B ....

Again... DON'T store multiple pieces of information in one field. It
honestly, truly does NOT help - it makes your work more difficult, not
simpler!

You can store the same information in *THREE* fields - a date, a
sequential sample number, and a sequential subsample letter or number
- and construct this composite field on the fly for display purposes.
But it is simply *not a good idea* to store it all in one field.

John W. Vinson[MVP]

 

[Nitin]

Thanks John

I will try to implement that.

Regards
Nitin