Return only lowest value

[ZPBJ]

I am writing a query and want it to return the lowest cost and the associated company by product. I have the costs in one table and the products in another. The tables are linkable on productID. Is there a way to do this in a simple, non-crosstab, query. I'd like to be able to further manipulate the data if necessary.

__________________________________
Remember that time when I took the box? - Peter

 

[lespaul]

Is there a way to do this in a simple, non-crosstab, query

yes.

There are several ways to accomplish this. Here's how to start:

Create & save a query that returns the lowest cost for each product:

SELECT productid, min(cost) from CostTable

Now, you can write your main query and join into this query to get the correct cost and product ID. I'm not sure where you are keeping the company (in product or cost?) Do you only get each product from one company?

anyway, this should at least point you in the right direction.

HTH

Leslie

Leslie