Design - Soil\water sample database

[Kickpuncher82]

Hi guys,



I'm trying to design a database to store soil\water sample information. My current needs require fields for:

ID - Text or Number, would like it to display like 13-1112 (13=year, 1112=sample number of that year). the sample number can be from 1 to 99999. Not sure how to custom format this field.
SampleName - Text
SampleDate - Date

after those 3 fields, I would also like to store what elements the sample was analyzed for and it's value.

Not every sample will have the same list of analytes. Will I have to create a separate field for all the elements that "could" have a value or is there an easier (dynamic) way?

Thanks and please ask if you need clarification!
Scott

[June7]

Here is how I generate custom unique ID like you describe: http://forums.aspfree.com/microsoft-...ta-403208.html

The 'dynamic' way is a child table of analytes associated with sample. A record for each analyte found and its value for each sample.