Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Wanet Telecoms Ltd on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Preventing duplicate entry across multiple fields? 1

Status
Not open for further replies.

ulteriormotif

Technical User
May 18, 2003
79
NZ
This may belong in the tables forum, or forms for that matter.

I have a table where data is entered for exporter indicative prices (horticultural industry - exporters release prices at the start of each season for various markets, fruits and growing methods).

Fields are:
Season
ExporterID
MarketID
VarietyID
GrowmethodID (organic, conventional)
Priceperkg

No one field is unique - it is the combination of Season, ExporterID, MarketID, VarietyID and GrowmethodID that makes the record unique.

Because the indicative prices are released sporadically by the exporters, and updated on occasion, this isn't a 'once a year' data entry job - it's constantly being updated and added to, and it is not clear to the user whether they have an initial price or an update in their hands.

I can use a duplicate query to produce a report and catch when duplicate records (ie, all fields except for price) have been entered, but ideally I would like to prevent them being entered in the first place.

Any suggestions?
 
You may consider create a unique composite index.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
 
unique composite index"

Sounds like exactly what I want. But I've never heard of it before - can you point me to any guidelines on setting them up?
 
heh.. found it. Amazing the information that's out there when you get the terminology.

Thanks PHV!
 
In the table design view display the index windows.
Another way is by action query:
CREATE UNIQUE INDEX indexname ON tablename (Season, ExporterID, MarketID, VarietyID, GrowmethodID);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
 
It's amazing you know - been building these things off and on for five years.. and never found this before now, and wouldn't have if not for this forum.

Joys of being self-taught.

:) Have spent the last half hour adding these to three databases. User problems no more!

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top