Query Quandry (2003)

Topic

New Reply

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)

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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

Otherwise, try this SQL string:

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)

The part between [ and ]. is the subquery.

Reply | Quote

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

Reply | Quote