[AccessD] Query Help

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



More information about the AccessD mailing list