Max query (Mr thicky pants)

[CJBIRKIN]

Hi

I'm having a thick day today.

I'm doing a project tracking db and i need help on a query for an update display. Basically i have 4 tables

Project: ProjectID ,Desc ....
ProjectModule: ModuleID, ProjectIDfk,Desc....
ProjectItem: ItemID,ProjectModuleIDfk,DateID,TimeID,UserID...
User: UserID,UserName....

What i need is a query to select each project and the last entrymade in ProjectItem and the UserName

I can use a group on Project.ProjectID and the max function on the ProjectItem.ItemID - as it's an autonumber i.e the last entry is the newest date - and it works. But when i add the userID it splits the data again showing all users. I know this is how it works but what i need is to return just 1 row per project

projectID,ProjectDesc,DateID,TimeID,UserID,UserName


SELECT TBL_Project.ProjectDesc, Max(TBL_ProjectItem.ItemID) AS MaxOfItemID, TBL_USER.UserName
FROM TBL_USER INNER JOIN ((TBL_Project INNER JOIN TBL_ProjectModule ON TBL_Project.ProjectID = TBL_ProjectModule.ProjectIDFK) INNER JOIN TBL_ProjectItem ON TBL_ProjectModule.ModuleID = TBL_ProjectItem.ProjectModuleIDFK) ON TBL_USER.UserID = TBL_ProjectItem.UserIDFK
GROUP BY TBL_Project.ProjectDesc, TBL_USER.UserName
ORDER BY Max(TBL_ProjectItem.ItemID);





Any Clues?

Cheers

Chris

 

[CJBIRKIN]

Hi


Solved it here I think


Chris