469,330 Members | 1,329 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,330 developers. It's quick & easy.

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

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
5 4968
MMcCarthy
14,534 Expert Mod 8TB
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
owz
14
thanx very much, i didnt think it was that simple.
Dec 6 '06 #3
MMcCarthy
14,534 Expert Mod 8TB
thanx very much, i didnt think it was that simple.
No problem.

Mary
Dec 6 '06 #4
NeoPa
32,181 Expert Mod 16PB
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
14,534 Expert Mod 8TB
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.

Similar topics

2 posts views Thread by Steven | last post: by
10 posts views Thread by Frederick Gotham | last post: by
17 posts views Thread by rhitz1218 | last post: by
6 posts views Thread by Patrick Fisher | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by Purva khokhar | last post: by
reply views Thread by haryvincent176 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.