Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2006
    Posts
    65

    Unanswered: Postcode Areas SQL Query

    Hi,

    I am having difficulty creating a query in our access databases to cross examine postcodes. Each client provides a full postcode of their address. I am trying to list how many of our clients that belong to each postcode area. For example the post code area for st4 3hd is st4. I will be most grateful if anyone can help in creating a query which will give total No. of clients grouped by each postcode area.

    many thanks

  2. #2
    Join Date
    Aug 2002
    Location
    Northampton, England
    Posts
    266
    English postcodes are a bit of a pig to handle as they are not consistent in their length. W1 2AB is as valid as WC1X 4FG and G54 2SA. As you can see, the area part of the code can be 2,3 or 4 characters. Having said that, I would suggest splitting the postcodes into Area and Sector. Doing a group and count on the split. Actually, you only need the Area. This function will do the split.

    Public Function SplitCode(strPostal As String, booPart As Boolean) As String
    Dim intPosition As Integer
    Dim strCode As String
    strCode = Right(strPostal, 3)
    If booPart Then
    SplitCode = Left(strPostal, InStr(1, strPostal, strCode) - 1)
    Else
    SplitCode = strCode
    End If
    End Function

    Use the function in a query. Change the table and field name to suit.

    SELECT tblPostCodes.PostCodes, splitcode([postcodes],True) AS Area, splitcode([postcodes],False) AS sector
    FROM tblPostCodes;

    Next, run a query grouping and counting, using the split postcode query as the base.

    SELECT qrySplitPostCodes.Area, Count(qrySplitPostCodes.Area) AS CountOf
    FROM qrySplitPostCodes
    GROUP BY qrySplitPostCodes.Area;

    That should give you grouped postodes and a count.

  3. #3
    Join Date
    Apr 2006
    Posts
    65
    thats a clever solution, i will give it a try. Thank you!

  4. #4
    Join Date
    Apr 2006
    Posts
    65
    Hi,
    when i run the query: SELECT tblPostCodes.PostCodes, splitcode([postcodes],True) AS Area, splitcode([postcodes],False) AS sector
    FROM tblPostCodes;

    it returns an error message 'Undefined Function 'splitcode' in expression'.

    i have changed table name to tblPostCode and the field to postcodes, but still no luck, any suggestions?
    Last edited by waylander; 10-04-06 at 07:40.

  5. #5
    Join Date
    Aug 2002
    Location
    Northampton, England
    Posts
    266
    Copy and paste the code into a module.

  6. #6
    Join Date
    Apr 2006
    Posts
    65
    working fine many thanks

  7. #7
    Join Date
    Apr 2006
    Posts
    65
    hi it is now coming back with following error message: Ambigous name in query expression 'SplitCode([postcode_incode],True)'.

    what does this mean and how do i fix????

  8. #8
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178

    Smile

    Code:
    SELECT Left([Zip],InStr([zip]," ")-1) AS PCArea
    FROM YourTableName
    WHERE (((InStr(Trim([zip])," "))>0))
    GROUP BY Left([Zip],InStr([zip]," ")-1)
    ORDER BY Left([Zip],InStr([zip]," ")-1);
    Works like a charm, no matter how many characters are in the Area! I needed the WHERE clause because we have mostly 5-digit Zip Codes here in the US.

    Sam

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by Sam Landy
    Code:
    SELECT Left([Zip],InStr([zip]," ")-1) AS PCArea
    FROM YourTableName
    WHERE (((InStr(Trim([zip])," "))>0))
    GROUP BY Left([Zip],InStr([zip]," ")-1)
    ORDER BY Left([Zip],InStr([zip]," ")-1);
    Works like a charm, no matter how many characters are in the Area! I needed the WHERE clause because we have mostly 5-digit Zip Codes here in the US.

    Sam
    one potential issue I can see here is that you are callking the same function 3 times per row. May not be an issueif your dataset is quite small, but could becoem an issue if you have many K of postcodes...say 100K plus)

    also I'd prefer to see left$( used in place of left(...again for performance reasons.
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Quote Originally Posted by healdem
    one potential issue I can see here is that you are callking the same function 3 times per row. May not be an issueif your dataset is quite small, but could becoem an issue if you have many K of postcodes...say 100K plus)
    You're right, of course, but the workaround is potentially costly as well. My suggestion for a workaround is rather simple, actually. In the same table, have an Integer field that would calculate every time a Post Code is entered or changed, namely the position of the space, where
    Code:
    FieldName = InStr(fldPostCode," ")
    Then you wouldn't need the Left() and InStr() functions in triplicate in each row; just use the value of the field.

    Sam

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •