Primary Key Problem

[AndeanWayne]

My database has a table tblCombinedPropInvest which is a temporary table. Four of the fields in this table are Tax year, State, County and PIN Number. These fields are entered in a data entry form. When all entry is made and append query calculates a PropertyID field and the records are appended to a table named tblCombinedPropInvestComplete. Then the records in the temporary field are deleted. The PropertyID field (Taxyear+state+county+PIN) is the Key field in the complete table. My problem is if an error is made in data entry on the temp table it is possible to create a duplicate PropertyID when the append query runs. If this happens the record is simply dropped and the daata entry person never knows it happened. If I remove the key field I can have a report show there are duplicate PropertyIDs but then there is no Primary Key field in the complete table. Help.

 

[CJ_London]

sounds like you need to modify your data entry form to prevent the user from entering a duplicate.

Probably the easiest way is to put some code in your form before update event to check for the existence of the key and warn the user before updating- something like this psuedo code

dim rst as dao.recordset
set rst=currentdb.openrecordset("SELECT * FROM myTable WHERE ...key = key")
if not rst.eof then
    'this key already exists
    warn user and give them opportunity to correct the key
    cancel update
end if
set rst=nothing

 

[ButtonMoon]

This isn't exactly a table design problem, it's more a question of user interface design. Your form ought to retain the values until the record is successfully saved.

As far as table design goes, it isn't necessary to concatenate the values into one PropertyID value just to implement the uniqueness constraint. You can put the uniqueness constraint on multiple columns - ie make a composite key of Taxyear, state, county, PIN. Generally speaking a composite key is probably a better approach than calculating PropertyID every time one of those values changes.

 

[The_Doc_Man]

I think I support ButtonMoon's answer. Further, if you keep the constrain as a multi-column index, but keep the columns separate, correcting an error is FAR easier - and in that case, Access would track the updates of the index "hidden" table very easily.

I would also suggest that the more you put into a key, the bigger it is. Once the key gets big enough, it starts to lose value because you have to repeat that compound key in every table where a relationship exists with your main table. Not that you can't do it, but it seems like a cumbersome key choice.