Solved

Get the products with the highest price

Posted on 2014-11-23
4
130 Views
Last Modified: 2014-11-24
Hi
Everyday we enter buyers that put prices out for products.  We enter the prices for each product they list.

What I am trying to do is create a query that will show which buyer has the highest price for each product entered daily.

For example the data looks like this:

BuyerCode              CodePD               Price               Date
AA                                   H1                    $280              24/11/14
GH                                  H1                    $295              24/11/14
GG                                  H1                    $285              24/11/14
SG                                   H1                    $275              24/11/14
AA                                   CO                   $300               24/11/14
GG                                  CO                    $305               24/11/14
and so on and so on........

What I would like it to be able to list the highest price details:
BuyerCode             CodePD                Price                 Date
GH                                H1                     $295                24/11/14
GG                                CO                     $305                24/11/14
etc etc
If a buyer has the same price then that should show in the results.

I hope I have explained it properly....I can clarify if need be.

Thanks in advanced.

Ajae
0
Comment
Question by:ajaeclarke
  • 2
4 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40461373
try this query

select T.*
from TableX as T
inner join
(select max(t2.Price) as maxPrice, t2.CodePD, t2.[Date]
 from tableX as t2
 group by t2.CodePD, p2.[Date]) as t3
on  T.CodePD=t3.CodePD and t3.[Date]=t3.[Date] and T.Price=t3.maxPrice
0
 
LVL 24

Accepted Solution

by:
chaau earned 500 total points
ID: 40461378
you need to use the query similar to this:
Select
prices.BuyerCode, prices.CodePD, prices.Price, prices.Date
FROM prices INNER JOIN
(SELECT CodePD, Max(Price) AS MaxPrice, Date
 FROM prices GROUP BY CodePD, Date) AS m
ON prices.CodePD = m.CodePD AND prices.Date = m.Date
ORDER BY 3 DESC, 1, 2

Open in new window

Please note, that with the query above, when several buyers have the same highest price for the day they will be listed
0
 
LVL 1

Author Comment

by:ajaeclarke
ID: 40461446
Thanks for the replies.

Rey Obrero, I get a join expression not supported.

Chaau, with a bit of tweaking I got it working how I needed.

Thanks for you help!
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40461959
typo on the join,  t3.[Date]=t3.[Date]  should be T.[Date]=t3.[Date]


select T.*
from TableX as T
inner join
(select max(t2.Price) as maxPrice, t2.CodePD, t2.[Date]
 from tableX as t2
 group by t2.CodePD, p2.[Date]) as t3
on  T.CodePD=t3.CodePD and T.[Date]=t3.[Date] and T.Price=t3.maxPrice
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question