Results 1 to 10 of 10
  1. #1
    Lounger
    Join Date
    Nov 2003
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Allocating records proportionately (Access 97)

    Hello from a coding virgin looking for an easy way out! I have a table with a field "Communities", which is usually filled by referral to a postcode. Thousands of records have empty post code fields. Each record also has a "Practice Code", which refers to GP practices. Each GP practice can have clients within many communities. We have extracted from the main table, the proportion of each complete record with Postcodes applicable to each GP Practice....... Now...... is there a way without coding that I can fill the empty "Communities" fields, to the correct proportion of the GP Practice simply by using queries, or will it have to be hard coded in some sort of loop?????? Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Allocating records proportionately (Access 97)

    Can you specify what you mean by "to the correct proportion of the GP Practice"? Please be as specific as possible.

  3. #3
    Lounger
    Join Date
    Nov 2003
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Allocating records proportionately (Access 97)

    Where there is no postcode, the record also has no communities. A statistician has taken a sub-set of the completed records which have postcodes/communities available and, where a practice has the ability to have multiple communities within its range (most have variously 3-15), calculated what proportion of the records should be allocated against that practice. We wish to apply the same proportion of records to those records with null postcodes/communities. Hope this is clear. Thanks! B

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Allocating records proportionately (Access 97)

    How do you know if a practice can have multiple communities in its range?
    How is the proportion determined? Do you have a table for this, or what?
    I repeat, please be specific.

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Allocating records proportionately (Access 97)

    With a null postal code, how would you determine whether a community was within a practice's range? It sounds like you might be better advised to spend your time filling in the postal codes.
    Charlotte

  6. #6
    Lounger
    Join Date
    Nov 2003
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Allocating records proportionately (Access 97)

    Yes, but we have quarter of a million of them (Postcodes) needing to be filled in!
    We have a table showing the practices, which communities they serve, and SOME of the postcodes. Sorry, I'm not allowed to transmit databases in either direction, or I'd send you a copy.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Allocating records proportionately (Access 97)

    >> or I'd send you a copy

    A quarter of a million postcodes? No thanks, even if you had been allowed to do that. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    If you want us to help you, you will have to provide much more information about the way the postcodes must be assigned. As it stands now, I don't have the slightest idea how it all fits together.

  8. #8
    Lounger
    Join Date
    Nov 2003
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Allocating records proportionately (Access 97)

    Ok, sorry Hans - will try and make myself more clear. We have a raw data table, ACPRaw, which has fields for a clients postcode, GP Practice and locality. The GPPractice field is always completed. Locality is best determined by Postcode, but, where the it is wrong, null or partial, we can identify general locality by the GP Practice code. Each GP Practice may cover multiple localities within a fixed geographical area. The aim of our current task is to complete as many of the localities entries as we can.

    Our statisticians have provided me with a table called Postcodes which has fields linking Postcodes, Locality and GPPractice, which has been run to update the Locality field within the Raw Data table where the Postcode is correct. Information regarding Locality and Postcode is by no means complete. They have also provided me with a table GPLocProp, which has fields GPPractice, Locality and Proportion (which I don't know how they calculated exactly), which is supposed to show, where the GPPractice is covering mutiple localities, which proportion of those records with locality and postcode details complete, are allocated against a particular practice. What the statisticians want, is for us then to run through those records with null localities and apportion localites are per the proportions given against the GPPractice code and locality in the GPLocProp table.

    I'm not sure if this is any clearer......! I think it is a bit overcomplex myself, but can't think of a quicker way of filling in these cells.....

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Allocating records proportionately (Access 97)

    Although it would be possible to do this in Access, it would require writing complicated VBA code. There may be existing code for this in Access, but I have never seen it (and a quick google search turned up nothing useful), so it would have to be written from scratch.

    I think it would be much better to do this in a statistical application. There are existing routines for completing missing data in many statistical applications. Let your statisticians do a Google search for imputation, the technical term for this. For instance Multiple Imputation Online.

    You could export the data to a format that the statistical application of choice can read, perform the imputation there, then import into Access again.

  10. #10
    Lounger
    Join Date
    Nov 2003
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Allocating records proportionately (Access 97)

    Ok thanks Hans

Posting Permissions

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