Solved

Get the products with the highest price

Posted on 2014-11-23
4
128 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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

863 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now