Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Quandry (2003)

    I'm working on a web interface pulling data from Oracle. The parts table I'm accessing has multiple entries for the same part number because of price structures.
    Sometimes both prices are present, sometimes their not depending on the part number.
    I built a table in access with the same fields I'll be working with to try to figure out a way to do this.
    I can't seem to come up with a way to show one line for the price for one part number.
    The criteria would be show the price for "NSP SP CST" if present. If not, show the "NSP DL CST" price. The "NSP SP CST" is always a lower number.
    This query is used in an asp search page so I want to have only one line show up.
    This is what the data looks like(attachment)

  2. #2
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Quandry (2003)

    Hans,
    That works in Access but I don't know if I can use 2 queries like that with recordsets in asp. Is there a way to combine the two into one(use a subquery?)
    Thats what I was playing with but I haven't written many of them.
    Thanks

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

    Re: Query Quandry (2003)

    1. Create a totals query that groups on Item_number and takes the max of Cost_Type:

    SELECT Item_Number, Max(Cost_Type) AS MaxCost_Type FROM Parts GROUP BY Item_Number

    2. Create a query based on Parts and the query you just made, joined on Item_Number and on Cost_Type vs MaxCost_Type.
    Return all fields from Parts you need.

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

    Re: Query Quandry (2003)

    I don't do ASP, but can't you open a recordset on a stored query?

    Otherwise, try this SQL string:
    <code>
    SELECT Parts.* FROM Parts INNER JOIN [SELECT Item_Number, Max(Cost_Type) AS MaxCost_Type FROM Parts GROUP BY Item_Number]. As q ON (Parts.Item_Number = q.Item_Number) AND (Parts.Cost_Type = q.MaxCost_Type)
    </code>
    The part between <code>[</code> and <code>].</code> is the subquery.

  5. #5
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Quandry (2003)

    Hans,
    I don't know enough about ASP either to answer that. I think your solution will work.
    Thanks,
    Scott

Posting Permissions

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