MS Access table. create new table using query based on partial contents of a stored field?

Question

I have a MS Access table contain all the UK postcodes. I want to extract and make a new table containing only 2 or 3 postcode areas. The query must look at the left two characters of the postcode field and see if it contains the two letters I want. Eg CW or ST or PB or whatever. Min of two max of 5. If it does then all the fields from that record needs to be written to a new table. Eg Address, house number.

Another ways would be that if postcode fields doesn't include the letters I want then the record needs to be deleted.

Cant do it manual as there are 1000'sof entries.

Hope someone can help

Thank you

Answer 1

I work with Access 2007, I don't know what you're using so I will show how I would do this.

Create a new Query. Choose Make Table as the type. Name the new tabl and choose the output location. Add all the fields I need. Set the Query Criteria under the postcodes field to be:

Like "*" & "CW" & "*"

on the Or Lines right below the first criteria line:

Like "*" & "ST" & "*"

Like "*" & "PB" & "*"

What this says is "Bring me all the records with these fields where the postcode beings with anything, has "CW" in it and ends with anything. * is a wildcard character but that is how that should be formatted. You can put anything you want inside the middle quotes to achieve this result.

Good Luck