Need help tweaking a query...

I need to find a way to not get duplicate items returned in a query. I have an inventory table, a transaction table and a few other tables that the inventory table uses.

I have 3 tables... category, make & model. These tables just store an ID number and a description for the category, make or model.

CATEGORY                            MAKE                  MODEL
===================      ================      ================
CatID      CatName            MakeID      MakeName        ModelID      ModelName
0      Circular Saw      0      Dewalt            0      7 1/4 inch
1      Drill            1      Bosch            1      18v Cordless
2      Jigsaw            2      Makita            2      Var Speed

My inventory table keeps track of each item we have, each row indicates an item we have and stores a company defined number, the CatID, MakeID and ModelID as well as other things such as purchase date, etc. It also stores the status of the item, as to whether it's in our stock room or out on a job.

INVENTORY
==============================================================
Item      CatID      MakeID      ModelID      Serial      PurchaseDate      Status
SGI001      1      0      1      ABC123      2005-01-01      Stock
SGI002      0      2      0      MAK123      2004-06-01      Out
SGI003      2      1      2      123456      2004-08-19      Out

I then have a transaction table which stores the date something is borrowed, the item number, who is taking it and where it is going.

TRANSACTION
================================================================
Item      DateOut            DateIn            Employee                      Job
SGI001      2005-06-30      2005-07-01      Bob            R112
SGI002      2005-06-30      2005-07-04      Peter            C430
SGI001      2005-07-01      2005-07-04      Peter            C430
SGI003      2005-07-01      2005-07-04      Peter            C430
SGI001      2005-07-05                  Bob            R113
SGI002      2005-07-05                  Bob            R113
SGI003      2005-07-05      2005-07-06      Peter            C431

When an entry is made in the transaction table, the status of the item in the inventory table is changed, ovbiously set to Out when an item is taken, and set to Stock when an item is returned.

What I need to be able to do is run a query where I can see what all items are in our stock room without seeing every instance where the tool has returned.

The query I have been trying to get working is this...

SELECT INVENTORY.Item,CATEGORY.CatName,Make.MakeName,MODEL.ModelName,TRANSACTION.DateOut,TRANSACTION.DateIn INNER JOIN CATEGORY on CATEGORY.CatID=INVENTORY.CatID INNER JOIN MAKE on MAKE.MakeID=INVENTORY.MakeID INNER JOIN MODEL on MODEL.ModelID=INVENTORY.ModelID where INVENTORY.Status="Stock" ORDER BY INVENTORY.Item;

When I run this query I get all the transactions for each Item listed in the TRANSACTION table, whereas I want only the last transaction for each item. Is there a way to use DISTINCT or MAX() with GROUP BY to accomplish this?

I have a ASP based form running on IIS6 that generates the SQL query based on a few parameters the user picks and then the query is run against MySQL on a Linux server. The communication all works fine, I just can't tweak my query quite right.

Thanks,
Corey