472,334 Members | 1,483 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,334 software developers and data experts.

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 5269
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,511 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

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

Similar topics

2
by: Steven | last post by:
Hi there, I have a list of values that I am displaying in a table (Not using a loop) I want to be able to put a graphic next to the item with...
12
by: | last post by:
Does anyone know an easy way to put a current gold price on your own website? I've seen the graphic methods that Kitco.com offers, but I'd rather...
10
by: Frederick Gotham | last post by:
Anyone want to give me a hand to write efficient algorithms for Highest Common Factor and Lowest Common Multiple? This is what I have so far... ...
17
by: rhitz1218 | last post by:
Hi, I'm trying to create a function that will sort a number's digits from highest to lowest. For example 1000 - will become 0001 or 1234...
13
by: td0g03 | last post by:
Hello again guys. I have a question. I'm working on a program for my class and I'm stuck on how to find the lowest and highest number of a user...
2
by: gizelle2101 | last post by:
I've read the articles, but nothing is working. My database just lists jewelry by type and gems. I want to pull out the most expensive piece and...
0
by: 1 | last post by:
Dear friend, we sell all kinds of laptops and guitars . our product is a quantity best, the price is the lowest in the world, i think you will...
23
by: lionelm | last post by:
Item (upc, title, type, category, company, year, sellPrice) Purchase (receiptId, date, cid, name, card#, expire, expectedDate, deliveredDate)...
6
by: Patrick Fisher | last post by:
Hi I have tables from 12 suppliers each of whom can supply the same part, I need to be able to create a table or query containing a list of...
0
better678
by: better678 | last post by:
Question: Discuss your understanding of the Java platform. Is the statement "Java is interpreted" correct? Answer: Java is an object-oriented...
0
by: teenabhardwaj | last post by:
How would one discover a valid source for learning news, comfort, and help for engineering designs? Covering through piles of books takes a lot of...
0
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and...
0
by: CD Tom | last post by:
This happens in runtime 2013 and 2016. When a report is run and then closed a toolbar shows up and the only way to get it to go away is to right...
0
by: CD Tom | last post by:
This only shows up in access runtime. When a user select a report from my report menu when they close the report they get a menu I've called Add-ins...
0
jalbright99669
by: jalbright99669 | last post by:
Am having a bit of a time with URL Rewrite. I need to incorporate http to https redirect with a reverse proxy. I have the URL Rewrite rules made...
0
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was...
0
by: Matthew3360 | last post by:
Hi there. I have been struggling to find out how to use a variable as my location in my header redirect function. Here is my code. ...
0
by: AndyPSV | last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.