Making a QuoteID out of other fields.

[Mr_Si]

Hi there,

My company's quote numbers are a combination of a few letters (which correspond to the last name of the client, or their company's name), the EnquiryID and the month and the year.

For my quote table, I wish to utilise this but not quite sure how to go about it...

I already have the enquiryID field from the enquiry table, this links the quote to the enquiry via a 1 to many relationship.

I am probably going to do away with the few letters thing for now as this is not unique and kinda a PITA and slightly pointless

The bit I am not sure how pull the quote date into the quoteID field to make it part of the ID.

Or...

Should I just make the idsQuoteID a lookup to the lngzEnquiryID and have the dtmQuoteDate as a completely separate field in the quote table. Then Make the idsQuoteID & the dtmQuoteDate a Composite Key?


Hope this made some sort of sense, and I look forward to your responses.
(Thanks in advance)

Simon

 

[georgedwilkinson]

Agreed, with an amendment: don't bother making a smart key at all, they are expensive and cause nothing but greif and heartache.

Find all the real live fields that make a unique record and put a unique index on it. Then in your form(s), allow the user to search (i.e. filter) by elements of the unique ID.