User generated primary keys

Hi Pasquale, thanks for your query.

Your query raises several issues. First of all, it is bad practice to create a primary key in the manner you're suggesting. Ideally, you should create a composite primary key by indicating the fields on the table which, when combined, create unique identifiers for each record. What you're doing is creating what is technically know as "derivative data" which can reflect upstream errors and therefore affect the functioning of the database.

If you attempted to create a primary key in the manner you suggest, you would need to parse the data (chop up the postcode by looping through it and cutting it when it sees a space), then concatenating it with only the first three letters of the family name (again by looping through each value) and bolting all that together with the remains of the postcode. While this is possible on a datasheet, it would make more sense to collect the information via a VBA user form, combine it and then populate the relevant fields on the series of tables in your database. As you can probably understand, a close description of how to do this is beyond the scope of this forum, so if you would like to pursue this with a trainer please do contact us via the telephone number of the website. We'd be only too happy to help you build the tool you need.

All the best,

Anthony