MS Access sql query for forum tables

If you will find this question to simple for the forum - blame it on me and I will apologize, because I am no more than just a novice to this issues.

I have this two tables in MS Access:

table Items and table Comments connected by Comments.parent ID = Items.ID.

I need a query that will displayed last 10 records from Items table, each record containing

Item.id 
Item.title
Item.text
Item,date_modified
count of Comments [if any]  for the Item 
last Comments[if any] guestName
last Comments[if any] date_modified

So far I have got something like this:

SELECT TOP 10  t4.id, t4.* FROM
(
SELECT Items.id AS item_id , Items.*,  t3.guestName , t3.modified AS comment_date  
,(SELECT count(*) FROM Comments where parentid = Items.id) as comentscount 
FROM Items 
,( SELECT  t2.id as commentID, t2.guestName , t2.modified, t2.parentid  FROM Comments as t2 ORDER BY t2.modified DESC  ) as t3 
WHERE 
(Items.开发者_Go百科id = t2.parentid  AND  t3.commentID = (SELECT max(id) FROM Comments where parentid = Items.id)) 
ORDER BY Items.modified DESC 

UNION

SELECT Items.id AS item_id, Items.* ,  null AS guestName,  null AS comment_date, 
(SELECT count(*) FROM Comments where parentid = Items.id) as comentscount 
FROM Items
WHERE (SELECT count(*) FROM Comments where parentid = Items.id)  = 0 
) AS t4

Well. It is working but I keep asking myself if this could be done in a more simple way.

Any suggestions will be more then welcome.

Thank you in advance.

SELECT TOP 10
Item.id,
Item.title,
Item.text,
Item.modified,
COUNT(Comment.id) AS count_of_comments,
(SELECT TOP 1 Comment.guest_name FROM Comment WHERE Comment.parentid = Item.id ORDER BY Comment.modified DESC) AS last_guest_name,
MAX(Comment.modified) AS last_comment_date
FROM Item
LEFT JOIN Comment ON Comment.parentid = Item.id
GROUP BY Item.id
ORDER BY Item.modified DESC

I'm not sure if the inner SELECT statement could also be integrated more appropriately. Maybe we could also ORDER BY Comments.modified DESC and then just "select" guest_name rejecting the nested SELECT statement, but I'm not sure.

(I have not tested it.)

How about something on the lines of :

SELECT TOP 10
       Item.id,
       Item.title,
       Item.text,
       Item.date_modified,
       c.CommentCount,
       c.LastComment,
       c.LastGuestName,
       c.LastModDate
FROM Item
LEFT JOIN
(SELECT ParentID, 
       Count(ParentID) As CommentCount, 
       Last(Comment) As LastComment, 
       Last(guestName) As LastGuestName,
       Last(date_modified) As LastModDate
FROM Comments
GROUP BY ParentID
ORDER BY date_modified DESC) c
ON Item.ItemID=c.ParentID
ORDER BY item.date_modified