Show the highest and lowest price item sold this month.(SQL)

[owz]

Hi again had some more problems, all help welcome. Access 2000, SQL


My problem is as stated in the title. I want 2 display the highest and lowest priced car sold for this month. This is what I have.

 SELECT  CarSales.Car_Reg,MAX (Purchased_Price) AS Highest_Sale
FROM CarSales
WHERE  Purchased_Price=(SELECT MIN(Purchased_Price)AS Smallest_Sale
                                                                                        FROM CarSales)=
 (((Month([Date_Of_Purchase]))=IIf(Month(Now())=1,12,Month(Now()))))
GROUP BY [CarSales].[Car_Reg] ;
 

Problem with the code is that it displays the highest price 2 the lowest price ordered by date, but they are all in 1 column.

I want it 2 display just the car reg, highest and lowest price all in separate columns.


CarReg Highest Price Lowest Price

cf63gd7 £8,000 £1500



All help welcome.

[MMcCarthy]

Try this ...

 
SELECT  CarSales.Car_Reg, MAX (Purchased_Price) AS Highest_Sale, MIN(Purchased_Price) AS Smallest_Sale
FROM CarSales
WHERE (((Month([Date_Of_Purchase]))=IIf(Month(Now())=1,12,Month(Now()))))
GROUP BY [CarSales].[Car_Reg] ;
 

[owz]

thanx very much, i didnt think it was that simple.

[MMcCarthy]

thanx very much, i didnt think it was that simple.

No problem.

Mary

[NeoPa]

 SELECT  CarSales.Car_Reg,MAX (Purchased_Price) AS Highest_Sale
FROM CarSales
WHERE  Purchased_Price=(SELECT MIN(Purchased_Price)AS Smallest_Sale
                                                                                        FROM CarSales)=
 (((Month([Date_Of_Purchase]))=IIf(Month(Now())=1,12,Month(Now()))))
GROUP BY [CarSales].[Car_Reg] ;

Did you mean to exclude January completely from your query?

[MMcCarthy]

Did you mean to exclude January completely from your query?

Ade right it should be:

WHERE (((Month([Date_Of_Purchase]))=IIf(Month(Now())=1,12,Month(Now())-1)))

Mary