Sorting postal codes in Access 2010

Question

I am creating a database that sorts Canadian postal codes (alphanumeric, eg. A9A 9A9) on the first three characters (eg. A9A). I want to be able to input a list of postal codes, and have them linked to a designated group based on the first three characters. For example, code A9A 9A9 and A9A 8B8 will be put into Group 1, and B1B 1B1 and B1B 2N3 will be in Group 2. 

I am a new Access user. I am relatively familiar with using Excel. Your help will be greatly appreciated!

Thanks.

Answer 1

WUPS! That was a mistake, I fear. Data should NOT be stored in table names. If you have one table for Group 1 and another table for Group 2, that's what you're doing! What you describe is reasonable for spreadsheets (a new sheet for each Group) but it's JUST FLAT WRONG for relational databases.

You have not said what your original table is, nor what you will be doing with these "groups" - but I think you need (for this part of the database) just TWO tables: the table you described with two fields, Group and Code, and the other table (whatever it is, I don't know) with the 7-8 character postcodes.

SQL is the language of Queries. A query IS SQL - the query design window is just a handy tool to build SQL, nothing else. Some queries though cannot be built in the grid and you must use the SQL window.

So... let's step back. First off, a point of jargon: in Access, a "DATABASE" is the .mdb or .accdb container file, in which you will have multiple Tables, Forms, Reports, and so on. The phrase "sorting a database" simply doesn't make sense - it's like saying "I'm going to sort my desk."

(looking at my desk maybe that's a bad example..:-{) )

So let's say you have a table of Customers, and one of the fields in this table is Postcode, with values like A1T 3M5

A1A 5G2

B1A 3K5

ok?

If so, create a new Query. Don't add ANY table to it yet. Just go directly into SQL view and you'll see a text window with just one word: "SELECT;"

Edit this text to instead read

SELECT Groups.Group, Customers.Postcode

FROM Customers INNER JOIN Groups

ON Groups.Code = Left(Customers.Postcode, 3);

Now open the query in datasheet view and you should see a column of Groups appropriate for the given Postcodes.

Is that getting to what you want?

Answer 2

I have also split my master table with all groups and codes into many smaller tables. Each group has it's own table and the field is the 3 alphanumeric code.

Answer 3

Thanks, John. I've quickly realized there's a lot to really understand in order to work Access properly. I read up a bit on the SQLs, but if you can provide a bit more guidance, that'd be great!

I have created a table with two fields: Field 1: Includes all Group names and Field 2: Includes the alphanumeric 3 letter codes. I have quite a few rows, as some 3codes belong to the same group. For example, I have written as follows:

Group 1     A1A

Group 1     A2A

Group 1     A3T

Group 2     A5A

Group 2     A9L

Group 3     A7M

...and so on.

So, my next step it to create a query to group all the A1A/A2A/A3T into Group 1, etc. Basically I want to create a database where if I punch in a postal code e.g A2A 3A4, it will tell me it belongs to Group 1.

Does this make more sense?

I'll keep reading up on SQLs in the meantime.

Cheers,

Kat

Answer 4

Will you be including more than prefix in the same group? For example, will A9A and A9B be in Group 1? If so, then you do what John said and create a table to identify what 3 letter code goes with what group.

However, if the 3 letter code is unique to a group, why bother?

Answer 5

Well, being skilled with Excel is both a benefit and a detriment - Access is a VERY different program with different assumptions! For one thing, in Excel you will work a lot with complex cell expressions and with Macros (VBA code); in Access, you'll do much more with Queries.

That said, I'd suggest creating a little Groups table with fields Group (Number, Long Integer) and Code3 (text):

1    A9A

2    B1B

Include rows for all of the codes that belong in each group (your example doesn't give enough info to know).

You can then create a Query joining the Groups table to your Postcode field: the SQL would be

SELECT yourtable.*, Groups.Group

FROM yourtable INNER JOIN Groups

ON Groups.Code3 = Left(yourtable,[PostCode], 3)