Link between Tables

[rob_andy]

I've got a table that contains the first letters of a postcode e.g. S or LN, also stored is city/town e.g. Sheffield or Lincoln and finally area. So basically its a very simple postcode lookup table. I want to link this to my customer files that have postcodes, however the postcodes in the customer files are in normal format e.g. LN4 3RN, so I only really need the first 1 or 2 characters. To link the fields.

Idea, create a new column in customer table and some how (the bit I need help with) automatically transfer the first letters of the full postcode into this column and apply to all those already in and future entries into the table. Thanks for any help, much appreciated. Rob

 

[Dugantrain]

When you look at what you're trying to do, you realize that you're basically trying to Calculate a field based off of pre-existing data. As almost always is the case with Access, calculated fields should be calculated in queries and not stored in the underlying tables. So create a query which will grab all of the fields that you need from your customer table. Add a new field in the query and as its name write:

Left([Your_Post_Code_Field], 2)

which will give you the first two characters of the postal code. You can then create another query which joins this query to the lookup post code table on the calculated post code field.