Filtering records with the most current dates/maximum Price

[Ned]

Hi All
I have given below an example below of the records in my database in
Access 2003 and looking for someone to help me filter based on either
of the 2 options described below using separate queries or other
methods:

Date;OrderNo;ItemNo;Price
15/06/07;001;A0001;$12.50
23/08/07;002;A0002;$25.75
23/08/07;002;A0008;$50.00
25/09/08;006;A0001;$15.00
25/09/08;006;A0002;$23.75
07/10/08;007;A0008;$55.00
11/01/09;010;A0001;$17.00
17/02/09;011;A0002;$22.00
07/03/09;012;A0008;$53.00

I would like to filter the records in 2 ways
1st Option (with the most current date for the items) From the above
records:
11/01/09;010;A0001;$17.00
17/02/09;011;A0002;$22.00
07/03/09;012;A0008;$53.00

2nd Option (with the highest price irrespective of Date or Order) From
the above records:
11/01/09;010;A0001;$17.00
23/08/07;002A0002;$25.75
07/10/08;007;A0008;$55.00

Thanks for the great support as usual

Regards, Ed

 

[Allen Browne]

You will need to use a Totals query (GROUP BY) to get the combination you
desire (by date or by price), and then use that as the source for another
query. Alternatively, you can combine the 2 in one if you use a subquery.

Here's an introduction to subqueries:
http://allenbrowne.com/subquery-01.html

Here's an example of returning the other fields after a GroupBy:
http://www.mvps.org/access/queries/qry0020.htm

 

[golfinray]

You need to use the Max function. In your query design grid, simply push the
little "E" which causes all fields to group by. Then on those two fields,
change the group by to Max.

 

[Edward Soares]

You will need to use a Totals query (GROUP BY) to get the combination you
desire (by date or by price), and then use that as the source for another
query. Alternatively, you can combine the 2 in one if you use a subquery.

Here's an introduction to subqueries:
   http://allenbrowne.com/subquery-01.html

Here's an example of returning the other fields after a GroupBy:
   http://www.mvps.org/access/queries/qry0020.htm

--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.









- Show quoted text -

Thanks Allen your solution worked. Thankyou for your great support as
usual

Regards, Ed