[AccessD] Query Help

Dean, I'm not sure if somebody has already answered this or not.

I just found it in my Gmail spam folder.

What you can do is save the query without tblClients.

Create a new query which joins this new, saved query to tbleProjects and
tblClients.

You could do it all with a sub query, but I think it will read a little
cleaner as a separate query.

HTH
David


On Fri, Aug 29, 2014 at 11:23 AM, Dean Davids <dnod at aol.com> wrote:

> 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
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>