Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2009
    Posts
    3

    Unanswered: need help with this scenario

    hi i am newbie with access database. i have two tables that i want to work on, length and quantity tables.

    length qty

    35.95 16
    17.86 43
    75.98 37
    11.67 36
    34.55 22


    i want to make a new table that would process something like this, getting the nearest value for each length from the quantity table.

    length qty

    35.95 37
    17.86 22
    75.98 - (null since no qty would fit in into the length stated)
    11.67 22
    34.55 36

    Can anybody help me figure out this problem? Thanks

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    A different way of stating the problem is that you need the minimum value from all those quantities which are greater than the length.

    What's your table structure regarding the fields in question?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Feb 2009
    Posts
    3
    yes correct I want to get the minimum values from the quantities which are greater than the length

    table structure

    Desciption Length Quantity (nearest possible or equal)

    I have come up with this but it is still wrong, it still gives me the quantity that have been used up already. Quantity allocated already for a Length should be counted out from the list.

    I am making something like a cutting plan, where I want to allocate the excess material that was cut from a previous allocation.

    example if I have 10 meters for each LENGTH, and initially I have used or cut 6 meters from the initial cutting, I want to allocate or reuse the remaining 4 meters to other requirement requiring equal or less than 4 meter

    SELECT TblNeed.Description, TblNeeded.ShtNo, TblNeed.Length, DMin("[Excess1]","TblCut","[Excess1] >=" & [Length]) AS Alloc
    FROM TblNeed
    ORDER BY TblNeed.Description;

    Sorry I have a hard time understanding english since I am not a native speaker of english. I hope I answered your question correctly.

    thanks for your reply

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    If all the data is in one table, you are going to need to subquery it...

    Unfortunately, I'm strapped for time atm, perhaps one of the SQL gods here can whip something up?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Feb 2009
    Posts
    3
    thanks...

    no data is not on one table only...

Posting Permissions

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