databasems-accessdatabase-design

Creating a relationship with a calculated field

I am trying to create a relationship between 2 tables in Access. In one table I have customers, in another I have areas by postal region. In the customers table I have all the usual address information and a simple calculated field which is just the country code and the first 2 characters of a post code. For example United Kingdom, Birmingham 1 would be "UKB1" and United Kingdom Hull would be "UKHU". In the other table we have these 4 character codes (not calculated like the customer table) alongside a regional number and member of staff.

Access is stating that it cannot create the relationship due to it coming from a calculated field.

How do I get around this without having to manually enter the data into the customer table?

Solution

  • I would just use code to maintain the extra column. So, in the form(s) that allow edit of that customer, just put in the post code or whatever field a after update event for that control on that form that "shoves" into that country code column the first 2 characters.

    For example, like this:

    Private Sub PostalCode_AfterUpdate()

    Me!CountryCode = Left(Me.PostalCode, 2)


End Sub

    Now, above will automatically take care of any edits, or new records you enter.

    However, that does not address existing records. For that, you want to run an update query once to "fill out" this new column for existing data.

    So, fire up query builder, add that table, and change query type to "update query".

    You get/have something like this:

    enter image description here

    Of course, you could also I suppose hit Ctrl-G to get to command (immediate) window, and type in this:

    currentdb.Execute "UPDATE tblHotels SET CountryCode = Left([postalCode],2)"

    Immediate window, or query builder. You simple add that new column, and fill it out.

    As noted, with the simple after update event attached to the post-code in your edit form, then the value will be thus maintained without any extra efforts from that point forward.

    And, now that you have a plain column, then you are free to build queries etc. that will allow join of that 2nd PostalRegion table.

    I suspect (doubt) that you need to enforce RI, but regardless, with a real living column of country code, then you are free to join in that "region" table for any query you build.