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.

Expand|Select|Wrap|Line Numbers

  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.

msquared

Try this ...

Expand|Select|Wrap|Line Numbers

  
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.

msquared

Quote:

Originally Posted by owz

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

No problem.

Mary

NeoPa

Quote:

Originally Posted by owz

Expand|Select|Wrap|Line Numbers

  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?

msquared

Quote:

Originally Posted by NeoPa

Did you mean to exclude January completely from your query?

Ade right it should be:

Expand|Select|Wrap|Line Numbers

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

Mary

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

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

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

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

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

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

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/about