Preventing duplicate entries

[AndySuk]

Hi

I'm using Access 2007 and have a table for currency rates. The currency is picked up from the currency table and the date is entered manually which is then followed by manual entry of the rate.

What I have realised is that it would be possible to input the same date with the same currency and yet enter two different rates - on two different rows of course. I can't limit duplication of the currency as the table will hold the rates for that currency for different dates. I can't limit the duplication of the date as there are numerous currencies with different rates for the same date.

I wondered if anyone would have any idea about how to prevent a duplication of the same currency at the same date with varying rates?

Many thanks

Andy

 

[KenHigg]

You can set the currency field to 'Indexed (No Duplicates)' in the table. Will this work?

 

[AndySuk]

Ah thanks Ken but I don't think so. By doing that it would restrict the currency field to not being repeated within the table however the table will store the currency (as a currency ID) many times over but for different dates. I suppose I'm looking for restricting a duplicate record but ONLY when both the Currency and date fields are the same.

Thanks again for the thought

A

 

[boblarson]

If I might interject here - you could use a MULTI-FIELD INDEX to do what you want, although I would probably use a DCount in my Before Update event on my form.

 

[KenHigg]

I misunderstood. I would look at doing what Bob suggest. I have always heard them called composite primary keys.

 

[boblarson]

I misunderstood. I would look at doing what Bob suggest. I have always heard them called composite primary keys.

Good point Ken. It can be a bit confusing because you CAN have a composite key but you can, as an alternative method, use a surrogate key (like an autonumber) but then set a multi-field index which basically does the same job as a composite key (in the one table) but it lets you not have the complexity of having to store all of the fields in the foreign tables, which you would have to do if you used a composite key.

 

[AndySuk]

Awesome.

Thanks you both very much and for the link Bob.

A

 

[KenHigg]

... but it lets you not have the complexity of having to store all of the fields in the foreign tables, which you would have to do if you used a composite key.

I have no idea what you're talking about. Why would you have to store the values of the composite key fields a different table?

 

[boblarson]

I have no idea what you're talking about. Why would you have to store the values of the composite key fields a different table?

Okay, maybe this will make sense:

Surrogate Key
TABLE1
TableID - Autonumber (PK)
Information - Text
DateField - Date

TABLE2
Table2ID - Autonumber(PK)
Table1ID - Long Integer (FK)
Table2Info - Whatever


Composite Key
TABLE1
TableID - Text(CompositePK)
Information - Text
DateField - Date (CompositePK)

TABLE2
Table2ID - Number (PK)
Table1ID - Text(CompositeFK)
Table1DateField - Date (CompositeFK)
Table2Info - Whatever

So if you have a composite key on table1 where the id and the date make up a composite key, you have to store that ID and date in table 2 - so 2 fields instead of 1. Now imagine that you had a 4 field composite key in Table1. In order to store a value (FK) in Table2 from Table1, you would need to store the 4 fields that make up that composite key.

But, if you have

This set up:

Surrogate Key with Multi-field Index
TABLE1
TableID - Autonumber (PK)
Information - Text (MF index)
DateField - Date (MF Index)

TABLE2
Table2ID - Autonumber(PK)
Table1ID - Long Integer (FK)
Table2Info - Whatever

You won't be able to store duplicates of the text and date in table one but still use a simple, single field PK for table 2.

I hope that clears it up.

 

[KenHigg]

Sounds good. Wonder what his pk is then...