Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Atrribute a value to field equally (2000)

    Hi,

    I have a table called tblProperty, which has a field

    VManageSalesID

    I have 144 records that do not have a value for this field and need to attribute it a value to these records based on the following numbers (that link a primary key for another table) 3, 4, 8, 13 and 21. I want to distribute the the numbers equally so each person has a similar amount.

    How can I achieve this with a Query or code?
    Best Regards,

    Luke

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

    Re: Atrribute a value to field equally (2000)

    Do you want to assign each record one of these numbers at random?

  3. #3
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Atrribute a value to field equally (2000)

    Hans,

    Yes please
    Best Regards,

    Luke

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

    Re: Atrribute a value to field equally (2000)

    Test the following on a copy of the database.

    Create a query in design view based on tblProperty.
    Add the VManageSalesID field to the query grid.
    Enter Is Null in the Criteria line to select only records for which VManageSalesID is blank.
    Select Query | Update Query.
    Enter the following in the 'Update to' line:
    <code>
    Choose(Int(5*Rnd([PropertyID])+1),3,4,8,13,21)
    </code>
    where PropertyID is the name of a numeric ID field in the table (for example an autonumber field, if present).
    Select Query | Run and confirm.

  5. #5
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Atrribute a value to field equally (2000)

    Thanks, worked a treat!
    Best Regards,

    Luke

Posting Permissions

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