Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2006
    Posts
    6

    Unanswered: querying postcode/incode within a given date

    Hi,

    we have a client database and as part of reporting, we need to know how many clients we have over an inputted date from a particular postcode area. The query is referring to english postcodes which are tricky because they are not a fixed length. For example B8, ST1, IG16. I want the query to return a list of the postcode area and then give number of clients for each area when inputted a given date. Any help will be much appreciated, please be aware that i am a beginner.

  2. #2
    Join Date
    Mar 2004
    Location
    UK
    Posts
    82
    hi

    first thoughts are to use the Between function in the query builder to get your date paramiters.

    then you'll need to decide if it might be better to use counties or cities/towns rather than post code to get then number per discrict.

    if you decide to use post codes then you might be better setting the query criteria as [forms]![myform]![text1], and in the afterupdate event property of the forms text box have it requery in vba.

    your input for text1 ,might be b77* meaning anything begining with b77 or BS7* and so on.

    give it a try.

  3. #3
    Join Date
    Nov 2006
    Posts
    6
    The problem with your suggestion is that the database is hosted externally and i dont have access to the database design, we download csv files that are imported into Access for reporting purposes. The only client info we have is postcodes, not town/city

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    how far down the postcode treee do you want to report
    ie the first two blocks, or the round & district codes (ie do ytou want to analyse the first, first 2 codes, 1,2 %& 3 or all codes.

    ....one thing you could consider is forcing some rule on your data as it comes in. If you are using this data as read only (ie you dont use it for CRM, or any data capture purposes, so you could reformat M3 5ZX to something like "M__3 _5ZX". You could use an uopdate query to achieve this, personally I'd probably call a function to reformat the postcode

    It may make sense to split the postcode down into its 4 elements. (Bearing in mind if its a sort of data warehousing type application then violating normalistaion is not really an issue, getting quick access tot he data is an issue.

    ie reprocess the postcode into 2 or even 4 blocks, bearing in mind in the first block you have 2 elements and alpha & numeric component, the second block is reveresed to numeric then alpha. You will have to make some assumptions that there user(s) has put the spacing in correctly

    if all you are interested in is the first block then using the split fucntion may be of use.... the alternative is to do a crude chop using the left$() function, using say 4 digits.

    Depending on how big your dataset is it will be more efficient to use as few fucntions as possible in your SQL. If its only a few rows then the inefficiency will be virtually unnoticeable.

    One issue you are going to have is how you separate London postcodes form any and evrywhere else.

    If you make sure your data coming in is fairly tidty then you can do a fairly simple SQL aggregate query something like
    Select Left$(PostCode,4), count(OrderID) as NoOrders group by Left$(PostCode,4)

    beqar in mind if this is a sales type report, you could be idel and generate it as a report.... who cares about ISO 14001/2/3, and use access to trigger the reporting levels.

    appreciate you are a beginner, but I think you need to give some clear though as to what you expect to achieve, and how you think the information is required, then match that to waht you can actually achieve.

    have a try and come back if you have a problem

    have a look at this.... are you doing he same peice of coursework?
    HTH
    Last edited by healdem; 11-20-06 at 07:01.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Nov 2006
    Posts
    6
    thanks Healdem,

    I have tried the split code example in the link you provided:

    I created a module with the function SplitCode, when i try to run the qrysplitcodes query it says 'undefined function SplitCode in expression, what does that mean?

  6. #6
    Join Date
    Nov 2006
    Posts
    6
    I have worked it out with the following query:

    SELECT Count(T_client.client_id) AS CountOfclient_id, Left([postcode],4) AS area
    FROM T_client INNER JOIN T_episode ON T_client.client_id=T_episode.client_id
    WHERE (((T_episode.referral_date)>=[paramStartDate] And (T_episode.referral_date)<=[paramEndDate]))
    GROUP BY Left([postcode],4);

    didnt expect it to be simple as that

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    glad to hear its working...

    ..thats because your requirement is fairly simple, something which I dont think could be determined from your original requirement

    However I think you are still going to have a problem using left$(postcode,4)...
    Although this will differnetiate any 3 or 4 digit leading block ofthe postcode. Its perfectly feasible that you may have a leadign block that is 2 letters, so using a 4 digit element will differentiate L8 5XX from L8 6YY, whihc if all you are interested in is the first block fails your user requirement.

    I still think the best solution is probably
    to reformat the data on enterign the data from the CSV so that the second block always starts on character postion 6,
    OR....
    to use a function that returns the first block.
    Or.. to store the 4 elements as separate items

    But thats a design choice, and up to you.

    Im guessing who ever has designed this requirement may also be interested in, say the number of orders by sorting office


    Just a quick and dirty function to break down postcodes....
    Code:
    Public Function SplitCode(strPostCode As String, intRequiredItem As Integer) As String
    Dim strPostCodeElements() As String
    strPostCodeElements = Split(strPostCode, " ")
    Select Case intRequiredItem
    Case Is = 1 'return the fist block (post Town + area,,,usually town)
      SplitCode = strPostCodeElements(0)
    Case Is = 2 'return the secondblock (usually round + zone / micro area)
      SplitCode = strPostCodeElements(1)
    Case Is = 3 'return the post town only (the sorting office)
      'we need to check if the second digit is numeric
      If IsNumeric(Mid$(strPostCode, 2, 1)) = True Then
        SplitCode = Left$(strPostCode, 1)
      Else
        SplitCode = Left$(strPostCode, 2)
      End If
    '....can be expanded as required, or rewritten (which would be smarter)
    End Select
    End Function
    Last edited by healdem; 11-20-06 at 11:29.
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Nov 2006
    Posts
    6
    thats a bit advanced for me and i do see your point, luckily for me we dont have many leading with two digits. Those that are; a simple adding up of the values solves the problem. The monthly query returns about 100 records, so i can get away with my shortcut solution, however i will play about with code you have provided :-)

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you put the code into a module
    you can then call it from a report or query

    one thing that could be done is to extend the case / style to a method which automatically restate the postcode to AA11 22BB format

    if you are using reports then Id consider changing the whole approach, use the function to strip off the first block, or reformat it so it always appears as AA99, so add a space if the postcode is a single digit sorting office.That way round you know the first 2 letters will always be the sorting office, the next 2 the post town

    EG "L8" becomes "L_8", "SK10" remains "SK10", optionally you may consider to extend the second element so "L_8" becomes "L__8", or "L_08", and use group headers & footers to do you aggregation (that way round you could add in number of orders by sorting office, aswell as post town) The reason being that the report will group correctly eg L1,L2,L3,....L10,L11....Lxxxx, rather than L1,L10,L11,L12,L2,L20,L21.....Lxxx)

    eg
    L1:20 orders
    L2:5 orders
    -------------
    L:25 orders

    like most things in the systems world it comes down to what you are familiar with, and what you want to push the boundaries on.

    You could add in a lookup table which decodes the Sorting office... do a quick google on UK postcodes so you could extedn the report to

    L1:20 orders
    L2:5 orders
    -------------
    Liverpool:25 orders

    All Orders:xxxx
    ...if you really wanted to push the boat out you coudl extend that lookup even further and break donw salas by post town
    eg
    L1:20 orders
    L2:5 orders
    -------------
    Liverpool:25 orders

    alternative report format

    WA area
    ----------
    Altrincham XXX orders
    Frodsham XxX orders
    Knutsford XXx orders
    Warrington XXX orders
    ---------------------------
    Warrington Sorting office XXXX
    ...but that will require a lot mor ework, and understanding of Access & VB... perhaps somethign to pencil in for 6..12 months or so
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Nov 2006
    Posts
    6
    thanks for your advice healdem, i will certainly develop the solution further with reference to your ideas. I am not a database developer, in my organisation i am responsible for anything I.T. related. The reporting side of the database stuff is one of my duties. My role depends on general all round IT skills so i will look to further enhance the reporting side once i get the time.

    many thanks for your help

Posting Permissions

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