By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,928 Members | 1,210 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,928 IT Pros & Developers. It's quick & easy.

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

P: 14
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
  1.  SELECT  CarSales.Car_Reg,MAX (Purchased_Price) AS Highest_Sale
  2. FROM CarSales
  3. WHERE  Purchased_Price=(SELECT MIN(Purchased_Price)AS Smallest_Sale
  4.                                                                                         FROM CarSales)=
  5.  (((Month([Date_Of_Purchase]))=IIf(Month(Now())=1,12,Month(Now()))))
  6. GROUP BY [CarSales].[Car_Reg] ;
  7.  
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.
Dec 6 '06 #1
Share this Question
Share on Google+
5 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Try this ...

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT  CarSales.Car_Reg, MAX (Purchased_Price) AS Highest_Sale, MIN(Purchased_Price) AS Smallest_Sale
  3. FROM CarSales
  4. WHERE (((Month([Date_Of_Purchase]))=IIf(Month(Now())=1,12,Month(Now()))))
  5. GROUP BY [CarSales].[Car_Reg] ;
  6.  
Dec 6 '06 #2

P: 14
owz
thanx very much, i didnt think it was that simple.
Dec 6 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
thanx very much, i didnt think it was that simple.
No problem.

Mary
Dec 6 '06 #4

NeoPa
Expert Mod 15k+
P: 31,566
Expand|Select|Wrap|Line Numbers
  1.  SELECT  CarSales.Car_Reg,MAX (Purchased_Price) AS Highest_Sale
  2. FROM CarSales
  3. WHERE  Purchased_Price=(SELECT MIN(Purchased_Price)AS Smallest_Sale
  4.                                                                                         FROM CarSales)=
  5.  (((Month([Date_Of_Purchase]))=IIf(Month(Now())=1,12,Month(Now()))))
  6. GROUP BY [CarSales].[Car_Reg] ;
Did you mean to exclude January completely from your query?
Dec 6 '06 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
Did you mean to exclude January completely from your query?
Ade right it should be:

Expand|Select|Wrap|Line Numbers
  1. WHERE (((Month([Date_Of_Purchase]))=IIf(Month(Now())=1,12,Month(Now())-1)))

Mary
Dec 7 '06 #6

Post your reply

Sign in to post your reply or Sign up for a free account.