Preventing duplicate entry across multiple fields? 1

[ulteriormotif]

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?

 

[PHV]

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

 

[ulteriormotif]

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?

 

[ulteriormotif]

heh.. found it. Amazing the information that's out there when you get the terminology.

Thanks PHV!

 

[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

 

[ulteriormotif]

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!