Results 1 to 3 of 3
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Populate Rows in a table (Excel 2003)

    Hi

    Is there any way to populate rows in a table? (162,000 rows)

    Example I have Column named ABCClassification and a Column named Total2005

    If Total2005 is <2,000 then ABCClassification should be "D" if between 2,000 and 20,000 "C" If between 20,000 and 100,000 "B" if > 100000 then "A"

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

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

    Re: Populate Rows in a table (Excel 2003)

    Braddy,

    One of the rules of relational database design is that you shouldn't store derived information in a table unless it is absolutely necessary. The reasons for this rule are:
    - Storing derived information takes up unnecessary storage space.
    - If Total2005 gets updated, the classification may be incorrect, since it doesn't get updated automatically too.

    You can calculate ABCClassification from Total2005 in a query, and use the query as the basis for other queries and for forms and reports. The ABCClassification will always be up-to-date, since it is calculated on the fly when the query, form or report is opened.

    To calculate ABCClassification, you'd remove the field from the table, and create a column in a query based on the table:

    ABCClassification: IIf([Total2005]<2000,"D",IIf([Total2005]<20000,"C",IIf([Total2005]<100000,"B","A")))

    You'll have to decide which, if any, of the < (less than) should be <= (less than or equal to).

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Populate Rows in a table (Excel 2003)

    HI Hans

    Thanks very much for your instruction, the IIf function will be very useful for me to adapt for other uses. now I can see how it works.

    Much appreciated.

    Braddy
    If you are a fool at forty, you will always be a fool

Posting Permissions

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