Hi - I've set up a simple database for some survey work. Data tables are linked by a primary key consisting of a 10 to 12 digit location code (picked up from a drop-down list) and a 2 digit survey number - all stored as text. Rather than use compound relationships I would like to combine these into a single primary key.
I know calculated fields in tables are wicked...! but how can do I do this automatically in ACCESS 2007. I can combine the two easilly in a calculated control in a form.... but not directly into the primary field. Although the result does appear in the control - I get an error message telling me I can't have a null entry in the primary field. Do I need to set up an event procedure???? Thoughts much appreciated
A fourteen character text field is a poor choice of Primary Key. Integers should always be stored as integers unless they are too long.
The location code should be stored as a long integer and if necessary a lookup to the text location code. The survey number should be a separate field also sotred as an integer with a lookup if required.
Some would suggest a separate autonumber PK but I would use a composite key based on these two fields.
Many thanks for the good advice. There are forty subdatasheets below this table (also using a few join tables). Rather than using a composite relationship on the LocationCode and SurveyNumber - I would rather have them combined in a single field to keep things simple for data entry. Any idea how I would do this automatically in 2007? There is now an option to specify calculated fields as a data type in ACCESS 2010 - but most of those using the database won't have this version. From what seen elsewhere - I may need to put some VBA code in as an event procedure - maybe in the 'After Update' property of the fields in question... just not sure and I'm no VBA specialist.....
For a single field I would advocate for an autonumber (which Galaxiom doesn't seem to like but has worked well for me for 14 years). And then you can use the two fields as well but you don't need to use those two fields to tie things together. Let the SYSTEM do it using the autonumber (by the way having so many subdatasheets in use instead of just using subforms for each table will potentially slow your database down immensely in a networked environment).
That is my suggestion. I hate composite keys as I don't like having to keep more than one field repeated in each of my tables. So, an autonumber works well. And I let the system manage the primary/foreign keys to maintain the relationships and referential integrity (use of a multi-field index can take care of not allowing dups of your input number combinations).
There are forty subdatasheets below this table (also using a few join tables).
Sounds like the design might be able to be simplified with a better data structure.
Rather than using a composite relationship on the LocationCode and SurveyNumber - I would rather have them combined in a single field to keep things simple for data entry.
There is no point whatsoever to using the combined values as the key and good reasons not to. If you don't want to use the composite key then use an Autonumber. It will work a lot better and not require code to create it.
I have nothing against Autonumber. I just don't think they are always the most appropriate solution and certainly not the universal panacea advocated by some. Some people simply prefer the first way they learnt to do something and unless they actually contemplate the pros and cons of different strategies they are unlikely to ever change.
I hate composite keys as I don't like having to keep more than one field repeated in each of my tables. So, an autonumber works well.
But you would happily add an extra field that has no meaning. Just because something works does not mean another technique would not be better.
In this particular case, including both the LocationCode and SurveyNumber fields in a related table would allow that table to be queried by either of those fields without reference to the main table. This can sometimes be useful.
Both tables would need to be included in the query when using the relationship with the synthetic key (unless of course you also repeated the fields in addition to the key.)
And I let the system manage the primary/foreign keys to maintain the relationships and referential integrity (use of a multi-field index can take care of not allowing dups of your input number combinations).
There is nothing that needs to be managed with a natural primary key, even a composite one. The occasional problems that can and do happen with autonumbering are completely avoided.
Foreign Keys, Relationships and Referential Integrity work exactly the same on composite keys as they do with a single field key. The Master and Child Link fields automatically insert values into both fields on the new record of a subform.
Moreover once you have applied a multi-field index you might as well use that as the key because the extra work for the system to check for duplicates is trivial. Indeed, maintaining both the multi-field index and the index on the synthetic primary key would be slower than using the composite primary key.
Thanks for the suggestions - there are a few more constraints I didn't mention. The database will be used in Asian development context with poor internet connectivity i.e. a non-networked environment. In practice this means there will four independent databases that will have to combined into a single database once entry is complete. It also means autonumbers are not an option.
My goal is to minimise data entry steps as far as possible, to speed data entry but mainly to reduce potential for mistakes. LocationCD itself consists of multiple two digit codes corresponding to administrative level tiers - already coded against LocationCD in a seperate table (therefore making LocationCD queriable on its constituent elements without need for repeated entry in the main survey datatables). The two-digit pair retain leading zeros so as to maintain a consistent number of digits in LocationCD record- as does the farmer No. This simplifies data checking - one more reason for the compromise use of the Text Data Format - even if it sacrifices a little performance.
I could just use a 'multi-index field' on LocationCD and FarmNo - and take your point about automatic insertion into sub-forms - but I have other reasons for wanting to retain a single primary field: let's call it SurveyCD, combining LocationCD and FarmNo. So back to the original question - any idea how SurveyCD might be generated automatically for use as the Table primary key?
As you have noted the distributed locations are not suited to autonumbering. This is a good example of why autonumber is not always appropriate. The composite key is ideal in this situation.
Unfortunately everything else you have said shows you are heading in the wrong direction and your proposed strategy resoundingly conflicts with fundamental principles of database design.
The two-digit pair retain leading zeros so as to maintain a consistent number of digits in LocationCD record- as does the farmer No. This simplifies data checking - one more reason for the compromise use of the Text Data Format - even if it sacrifices a little performance.
..... but I have other reasons for wanting to retain a single primary field: let's call it SurveyCD, combining LocationCD and FarmNo.
You have confused the concepts of data display and recording. Display should be managed in the forms and reports. The table designs should be considered for efficient storage and retrieval rather than display.
I would store the numbers as integers in the table and show the leading zeros in the field and controls by using the Format property of these objects. For example the Format property 0000 will format the displayed value to four digits by padding with leading zeros.
This will make entry both more efficient and reliable as the leading zeros can be either included or omitted by the operator and they will always display. I will admit that this aspect is a matter of developer preference and the use of text to store the value with leading zeros is acceptable.
However the SurveyCD is a derived value which should be generated on the forms and reports. Your proposal to store it is a breach of normalization and is objectively wrong by database design standards.
This is entirely aside from the fact that long text fields make poor primary keys.
Assuming four digits for each the SurveyCD should be generated by setting the control source property of a control on forms and reports to:
It also appears you have already generated the PK of the Location table by concatenating the information from the fields. This is wrong for the same reasons.
Although it does make sense to condense the multiple field values into a single value in this situation the LocationPK should be a short numeric value. It need not have any meaning to the database users. It is number used to relate the surveys to the locations by the database. It should be optimised for the database not the users. A twelve character text primary key is not a good choice.
By all means have a location code incorporating all the information displayed on the forms and reports. But remember this is a derived value, concatenated from the underlying data on demand. It should not be stored because the information is already held in the other fields. Breaches of normalization should always be avoided.
So back to the original question - any idea how SurveyCD might be generated automatically for use as the Table primary key?
It can be done but it is messy and as I have repeatedly emphasised at great length, it is entirely the wrong strategy. However I have relucantly included the following advice.
Use either the AfterUpdate events of both the LocationCD and FarmNo controls or the BeforeUpdate event of the form to concatenate the values and write them to the either the SurveyCD field in the form's recordset or a control bound to it.
The control AfterUpdate technique would show the value as soon as the control is exited while the Form BeforeUpdate will only show the combined value after the record has been saved.
Setting up the correctly normalized tables using a composite key with a derived control source for the display only SurveyCD control requires no code at all. I will say it just once again, your proposed solution is a mistake.
