Design suggestions? 1

[utc13]

I have a table that logs in incoming samples. Each record represents 1 sample that has been logged in. It has around 10 fields that track different pieces of information regarding each sample. One of these fields tracks a bar code that is assigned to each sample as it comes in. The problem I have is that now each sample can have multiple bar codes. I want to be able to track and associate all bar codes with its corresponding sample record but I'm undecided on how to do it. I could just put them all into 1 field and separate them with a comma or something but that would get messy and it would require that I change the field type from numeric to text. Can anyone suggest a better way to design/handle this? Any advice is much appreciated.

 

[LittleSmudge]

This is a standard problem that is exactly what relational databases are for.


tblSamples ' Main table name
SamplesId ' Primary Key for Samples table
dataField1
dataField2
.. ..
..
DataField10


tblBarCodes ' Second table name
BarCodeId ' PrimaryKey for Barcode table
SamplesRef ' Foreign Key that points to the record in the sample table where the bar code belongs - Contains the value from the tblSamples!SamplesId field
BarCodeValue


( In this case - if you can Guarentee that the bar code number will ALWAYS be unique then the BarCodeValue could be the primaryKey for the tblBarCodes )


To get the data into tblBarCodes for all the existing data, create an append query that writes
tblSamples!SampleId >to> tblBarCodes!SamplesRef
tblSamples!BarCode >to> tblBarCodes!BarCodeValue
run the query and then you can delete the field BarCode from tblSamples.



'ope-that-'elps

G LS