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. - 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.
5 5395
Try this ... -
-
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] ;
-
thanx very much, i didnt think it was that simple.
thanx very much, i didnt think it was that simple.
No problem.
Mary
NeoPa 32,556
Expert Mod 16PB - 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?
Did you mean to exclude January completely from your query?
Ade right it should be: -
WHERE (((Month([Date_Of_Purchase]))=IIf(Month(Now())=1,12,Month(Now())-1)))
Mary
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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
|
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...
|
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> ...
|
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...
|
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,...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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
|
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...
| |