Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Jan 2005
    Location
    Wichita, Kansas, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Multiple criteria to determine category (2000-SR1)

    To place orders for gowns, the data is collected on a paper form with height given in feet and inches, and weight in pounds. There are 27 different gown size choices; the first six are shown in this table:
    4'10"-5'0" (1) <160 (2) 160-219 (3) >220
    5'1"-5'3" (4) <180 (5) 180-239 (6) >240

    So, if the person is 5

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Multiple criteria to determine category (2000-

    Have a look at the attached demo.

    It uses a table to hold the various sizes , then retrieves the right size using a lookup from a query based on the table. The values in the table are all 1 more than the thresholds.

    This avoids having all the complicated iff statements.

    My demo just uses one text box for height (in inches) , but it makes sense to have one for feet and one for inches and calculate the height in inches from them.

    I am also triggering the lookup with a command button, but you could use an after update event. You just need to think through how to trigger it so it does not try to do a lookup until you have all the data you need.
    Attached Files Attached Files
    Regards
    John



  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Multiple criteria to determine category (2000-SR1)

    You could create a function whose input is height and weight, and which returns the gown size. You wouldn't even have to store the gown size, you could easily calculate it whenever you needed it. For example, on your dataentry form, you'd have a textbox for GownSize, whose controlsource would be something like: =GetGownSize(PersonHeight,PersonWeight).
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    2 Star Lounger
    Join Date
    Jan 2005
    Location
    Wichita, Kansas, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple criteria to determine category (2000-

    I was thinking of storing the gown size in a query that will be used for several different reports, so that would minimize the times I would need to specify that? I have the formula in a query to convert the "Feet and Inches" to inches...
    In your example, "GetGownSize" is the name of the function? Pardon my ignorance...where or how does the function look up the gown size? (I have created a table with the gown size criteria listed; sample records below:
    Size SizeCode Height Weight
    Regular 1 4'10"-5'0" <160
    Weight*1 2 4'10"-5'0" 160-219
    Weight*2 3 4'10"-5'0" >220
    Regular 4 5'1"-5'3" <180
    Weight*1 5 5'1"-5'3" 180-239
    Weight*2 6 5'1"-5'3" >240

    Thanks for whatever help is offered!

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Multiple criteria to determine category (2000-

    If you were to follow Mark's suggestion, it is entirely up to you how the function GetGownSize would do its job.
    You would have to write it from scratch.
    You could use a lookup, or write a whole lot of if statements, or whatever else you can think of that works.
    Regards
    John



  6. #6
    2 Star Lounger
    Join Date
    Jan 2005
    Location
    Wichita, Kansas, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple criteria to determine category (2000-

    I'm still not visualizing how this would look and work. I wasn't sure if Mark's suggestion was a modification of yours, or what. As you can tell, I am pretty green with a lot of this.
    To write a function myself would require 27 different statements, I think. I'll see what I can figure out from the lookup angle.
    Thanks

  7. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Multiple criteria to determine category (2000-

    There is overlap and difference between my suggestion and Mark's.

    One of the general principles is that you don't store a piece if information if you can calculate it from other stored data. So Mark is saying that gown size can be calculated from heght and weight, so should not be stored in a table. To make the process of calculating it easy in practice, put the calculation process into a function.

    One of the reasons for not storing anything that can be calculated is that you have to be sure that you update the stored value whenever any of the inputs change.
    Sometimes the calculation is lengthy, and the inputs rarely change, so it makes sense to store the result for performance reasons.

    In this case you could do it either way.
    Regards
    John



  8. #8
    2 Star Lounger
    Join Date
    Jan 2005
    Location
    Wichita, Kansas, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple criteria to determine category (2000-

    Got it working now...thanks for the help.
    Warren

Posting Permissions

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