asked on

Access report: how to see only the highest value

I have a library database that contains two tables. Master table with library contents and a usage table listing each time something is used.

I want to create a report that lists all library entries and only the last time it was used. Some library entries have not been used but I want to show them on the list anyway.

Book 1 used 1/1/11, 1/15/11 and 3/20/11
Book 2 never used
Book 3 used 10/1/11, 9/15/11 and 7/23/11

Desired report:
Book 1 3/20/11
Book 2
Book 3 10/1/11

I can get a complete listing with all usage dates. But I only want to see the most recent one. I've tried creating a query on the usage table listing the library entry ascending and usage date descending -- then tried to create a bound control on the report with a Dlookup -- but that didn't work.

Thanks for your help,
je

Dale Fye

Assuming that your table structure looks something like:

tbl_Items
ItemID
Book
Author
Version

tbl_ItemUses
ItemID
UseDate

Then the query might look like:

SELECT tbl_Items.Book, LatestUse.UseDate
FROM tbl_Items
LEFT JOIN
(SELECT tbl_ItemUses.ItemID, MAX(tbl_ItemUses.UseDate) as MaxDate
FROM tbl_ItemUses
GROUP BY tbl_ItemUses.ItemID) as LatestUse
ON tbl_Items.ItemID = LatestUse.ItemID

This query uses a subquery to identify the most recent use for each item, and then uses the LEFT JOIN syntax from your Items table so that all items will be returned in your result set, even if they don't have a UseDate in tbl_ItemUses.

HTH,

Dale

aeolianje

ASKER

Dale,

Thanks for the quick response. I'm having some trouble translating to my setup.

tbl_items = Handbell Music Major Table
tbl_ItemUses = Handbell Performance Record

I wasn't sure what your LatestUse table was. Below is how I tranlated it. I'm getting some syntax errors.

Thanks again for your help - je

SELECT [Handbell Music Major Table].*, LatestUse.UseDate
FROM [Handbell Music Major Table]
LEFT JOIN
(SELECT [Handbell Performance Record].TitleID, MAX([Handbell Performance Record].[Date]) as MaxDate
FROM [Handbell Performance Record]
GROUP BY [Handbell Performance Record].TitleID) as LatestUse
ON [Handbell Music Major Table].TitleID = LatestUse.TitleID

ASKER CERTIFIED SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

aeolianje

ASKER

Thanks -- worked perfectly!!!