Re: What's wrong with this query?

Tech-Archive recommends: Fix windows errors by optimizing your registry

Because your subquery in the where clause only returns the Max date for the entire table.

You might be better off building a query to give you the Max date for every client and adding that to your query. You could also try using a coordinated subquery- this could be fairly slow.

SELECT tblClient_Income_History_DateV1.ClientID, qryClientNameV3.ClientName,
Sum(tblClient_Income_HistoryV1.Cl_Mo_Inc_Source_Amt) AS
SumOfCl_Mo_Inc_Source_Amt
FROM (tblClient_Income_History_DateV1 INNER JOIN tblClient_Income_HistoryV1
ON tblClient_Income_History_DateV1.ClMoIncDateID =
tblClient_Income_HistoryV1.ClMoIncDateID) INNER JOIN qryClientNameV3 ON
tblClient_Income_History_DateV1.ClientID = qryClientNameV3.ClientID

WHERE (((tblClient_Income_History_DateV1.Cl_Mo_Inc_Date)=
(Select Max(Cl_Mo_Inc_Date)
From tblClient_Income_History_DateV1 as T3
WHERE T3.ClientID = tblClient_Income_History_DateV1.ClientID )))

GROUP BY tblClient_Income_History_DateV1.ClientID, qryClientNameV3.ClientName;







'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


shep wrote:
ACCESS 2003
I want to create list of all clients with date of last income change. I am getting only the client with last date rather than all clients. Why?

SELECT tblClient_Income_History_DateV1.ClientID, qryClientNameV3.ClientName, Sum(tblClient_Income_HistoryV1.Cl_Mo_Inc_Source_Amt) AS SumOfCl_Mo_Inc_Source_Amt
FROM (tblClient_Income_History_DateV1 INNER JOIN tblClient_Income_HistoryV1 ON tblClient_Income_History_DateV1.ClMoIncDateID = tblClient_Income_HistoryV1.ClMoIncDateID) INNER JOIN qryClientNameV3 ON tblClient_Income_History_DateV1.ClientID = qryClientNameV3.ClientID
WHERE (((tblClient_Income_History_DateV1.Cl_Mo_Inc_Date)=(Select Max(Cl_Mo_Inc_Date) From tblClient_Income_History_DateV1)))
GROUP BY tblClient_Income_History_DateV1.ClientID, qryClientNameV3.ClientName;

Thanks for help


.

Relevant Pages

  • Re: Delete Query Does Not
    ... Did you try the method of changing the query in the Design View, ... INNER JOIN meals ... Cannot group on fields selected with '*' (client) ...
    (microsoft.public.access.queries)
  • Re: Whats wrong with this query?
    ... I used the coordinated subquery you suggested and it appears to ... If that can be done in this query, ... tblClient_Income_HistoryV1.ClMoIncDateID) INNER JOIN qryClientNameV3 ON ... getting only the client with last date rather than all clients. ...
    (microsoft.public.access.queries)
  • Show last records by date?
    ... I am having some trouble with a query. ... each client. ... FROM (Contacts INNER JOIN Events ON Contacts.ClientID = Events.ClientID) ... INNER JOIN Tracking ON Contacts.ClientID = Tracking.ClientID ...
    (microsoft.public.access.queries)
  • Re: Delete Query Does Not
    ... INNER JOIN meals ... The meals table contains the client master ID, a meals master ID, (those ... I have presented this as a SELECT query not a DELETE. ...
    (microsoft.public.access.queries)
  • Re: Whats wrong with this query?
    ... * .06, 0) as Fee, ... FROM ((tblClient_Income_History_DateV1 INNER JOIN tblClient_Income_HistoryV1 ... tblClient_Income_HistoryV1.ClMoIncDateID) INNER JOIN qryClientNameV3 ON ... You might be better off building a query to give you the Max date for every client and adding that to your query. ...
    (microsoft.public.access.queries)