Multiples of same record show up when paging through records

[1953hogan]

I have an Inventory database with 2 tables. Here is a simplified version of both tables:

First Table is Inventory:
Fields are
ID
Description
PackSize
PackCost

Second table is PriceHistory:
Fields are
ID
InventoryID
Date
PackCost

Fields are joined, one to many (one inventory record can have many prices in its history). ID in the inventory table is joined to InventoryID in the PriceHistory table.

The problem comes when I am browsing records in FORM VIEW of the inventory table:



As I page through the inventory records, I often see the same record multiple times (once for each entry in the pricehistory table).

For example, if the item POTATOES in the Inventory table has had it's price updated 3 times (3 entries in the PriceHistory) table, then I see the Potatoes record 3 times as I am browsing.

I only want to see this record once (with the latest price) when I am browsing the Inventory table in form view. Can somebody help me fix this? Thanks very much.

[orange]

Date is a reserved word in Access. You should either change the name eg mDate, or enclose it in [Date].
If you only want the latest Price for an item based on the [Date], then you will need to use a constraint to get the Max([Date]) associated with the Item.


'something like this (untested)
SELECT * FROM Inventory INNER JOIN PriceHistory on
Inventory.id = PriceHistory.InventoryID
WHERE PriceHistory.Date = (Select Max([Date]) FROM PriceHistory B where B.InventoryId = PriceHistory.InventoryID)