Dean Davids
dnod at aol.com
Thu Sep 4 06:51:08 CDT 2014
David, I appreciate your suggestion. I did do something similar but I have not confirmed, as yet, that it is consistently correct. I made a separate query with just the tblContracts, ordered by contract amount descending. Then I group by the project FK and pull only the first contract of each group. As you say, I use this query which should have only the largest contract from each project in my main query. It seems to work properly but I have a nagging memory of the "First" function sometimes not giving the expected results, especially in subquery situations. Difficult to confirm as it is many records and not all of them have multiple contracts. So far, what I see appears accurate. Thank you for your input. Dean Davids Fort Lauderdale, FL On Sep 2, 2014, at 1:06 PM, David McAfee <davidmcafee at gmail.com> wrote: > 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 >> > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com