how to make query to get Latest info 1

[rgao]

Hi,everyone
I have problem in making a query, I need help
In our company's database ,there is a table stored the information of the products that we want to purchase, supplier's price,the date the supplier provide their price...., the data in table is like this:

PRODUCT SUPPLIER PRICE Price-ISSUEDDATE
p01 A 100 01/12/01
p01 A 105 01/01/02
p01 C 99 11/12/01
p02 B 70 02/01/02
p02 B 80 12/11/01
........
now,we want to get every supplier's Latest price for the products, the result of query should like this:

PRODUCT SUPPLIER PRICE Price-ISSUEDDATE
p01 A 105 01/01/02
p01 C 99 11/12/01
p02 B 70 02/01/02
........
I don't know how to make this query,if someone have any idea,Please advise.Thank you for any suggestion and hints

 

[mac318]

Use the min function. Here is an example directly out of Access Help:

This example uses the Orders table to return the lowest and highest freight charges for orders shipped to the United Kingdom.
This example calls the EnumFields procedure, which you can find in the SELECT statement example.

Sub MinMaxX()

Dim dbs As Database, rst As Recordset

' Modify this line to include the path to Northwind
' on your computer.
Set dbs = OpenDatabase("Northwind.mdb&quot

' Return the lowest and highest freight charges for
' orders shipped to the United Kingdom.
Set rst = dbs.OpenRecordset("SELECT " _
& "Min(Freight) AS [Low Freight], " _
& "Max(Freight)AS [High Freight] " _
& "FROM Orders WHERE ShipCountry = 'UK';&quot

' Populate the Recordset.
rst.MoveLast

' Call EnumFields to print the contents of the
' Recordset. Pass the Recordset object and desired
' field width.
EnumFields rst, 12

dbs.Close

End Sub

mac

 

[HoleInTheFoot]

Hi rgoa,

You can get the desired result as follows I am assuming your data is stored in a single table i.e. Table1 then use the following queries, query 2 will give u the answer :

1) SELECT Table1.PRODUCT, Table1.SUPPLIER, Max(Table1.Price_ISSUEDDATE) AS MaxOfPrice_ISSUEDDATE
FROM Table1
GROUP BY Table1.PRODUCT, Table1.SUPPLIER;

2) SELECT Table1.PRODUCT, Table1.SUPPLIER, Table1.PRICE, Table1.Price_ISSUEDDATE
FROM Table1 INNER JOIN Query1 ON (Table1.Price_ISSUEDDATE = Query1.MaxOfPrice_ISSUEDDATE) AND (Table1.SUPPLIER = Query1.SUPPLIER) AND (Table1.PRODUCT = Query1.PRODUCT);

If you have any queries I'll try and answer them.

HoleInTheFoot

 

[mac318]

Excellent code rgoa. Have a kudo.

mac

 

[rgao]

Hi,mac,HoleInTheFoot

Thank you very much,following your suggestion,I made it.Thanks again!by the way people want to see a report
including all this latest information,and also have the lowest price marked for each product(sometimes the price is not the only factor we have to consider, we have to compare something also,so people want to see all informaton with marked lowest price).Do you have any idea?