Dean Davids
dnod at aol.com
Fri Aug 29 13:23:41 CDT 2014
I am sure this is purely my ignorance for having not cracked open an Access db in some years. What seemed to be a simple query in my head has me chasing my tail. >From qbd Grid: SELECT tbleProjects.ProjectID, tbleProjects.ProjectName, Max(tblContracts.ContractAmount) AS MaxOfContractAmount, tblClients.CompanyName FROM tbleProjects INNER JOIN (tblClients RIGHT JOIN tblContracts ON tblClients.ClientID = tblContracts.ContractClientID) ON tbleProjects.ProjectID = tblContracts.CotractProjectID GROUP BY tbleProjects.ProjectID, tbleProjects.ProjectName, tblClients.CompanyName ORDER BY tbleProjects.ProjectName; tbleProjects is one to many tblContracts. tblClients is one to many tblContracts. I am trying to get the client of the highest value contract associated as the main client of the project in a query. With the above statement, I get duplicates for each project that has multiple contracts. If I leave out tblClients, I get no dupes and I get the max contract amount, which I need, but I also need the Client. Alternatively I'd take the PK from the max contract which I can then use to retrieve the client. Am I going about this wrong? Thank in advance for suggestions, Dean S. Davids Fort Lauderdale, FL