473,324 Members | 1,856 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 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 5395
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,556 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 the hightest price. I have all of the prices in...
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 have it pull the value from some gold market...
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... Caveat: I haven't gone over the following code in...
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 to 4321
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 input. We aren't at arrays yet so that's out of the...
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 the least expensive piece in one query. <code> ...
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 be interested in our product . thanks a lot! Our...
23
by: lionelm | last post by:
Item (upc, title, type, category, company, year, sellPrice) Purchase (receiptId, date, cid, name, card#, expire, expectedDate, deliveredDate) PurchaseItem (receiptId, upc, quantity) Stored (name,...
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 suppliers who can supply at the lowest price for each...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.