What's wrong with this query?

G

Guest

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
 
R

Rick Brandt

shep said:
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?
Click to expand...

Your subquery is only selecting the max date so that is all you are going to
get.
 
J

John Spencer

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
'====================================================
 
G

Guest

Thanks. I used the coordinated subquery you suggested and it appears to
works great. I have tried to include an IIF expression as criteria in the
new field"Fee" to have negative results default to "0", but have not been
successful. If that can be done in this query, I would appreciate your help.
Here is current SQL

SELECT tblClient_Income_History_DateV1.ClientID, qryClientNameV3.ClientName,
Sum(([Cl_Mo_Inc_Source_Amt])) AS TotInc, (([TotInc])-466)*0.06 AS Fee,
tblClient_Income_History_DateV1.Cl_Mo_Inc_Date
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) INNER
JOIN tblClient_InfoV1 ON tblClient_Income_History_DateV1.ClientID =
tblClient_InfoV1.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, tblClient_InfoV1.Inactive,
tblClient_Income_History_DateV1.Cl_Mo_Inc_Date
HAVING (((tblClient_InfoV1.Inactive)=False));
 
J

John Spencer

You might have to redo the sum calculation for TotInc.

IIF(Sum([Cl_Mo_Inc_Source_Amt])>466,(Sum([Cl_Mo_Inc_Source_Amt])-466) *
..06, 0) as Fee

SELECT tblClient_Income_History_DateV1.ClientID,
qryClientNameV3.ClientName,
Sum(([Cl_Mo_Inc_Source_Amt])) AS TotInc
, IIF(Sum([Cl_Mo_Inc_Source_Amt])>466,(Sum([Cl_Mo_Inc_Source_Amt])-466)
* .06, 0) as Fee,
tblClient_Income_History_DateV1.Cl_Mo_Inc_Date
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) INNER
JOIN tblClient_InfoV1 ON tblClient_Income_History_DateV1.ClientID =
tblClient_InfoV1.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, tblClient_InfoV1.Inactive,
tblClient_Income_History_DateV1.Cl_Mo_Inc_Date
HAVING (((tblClient_InfoV1.Inactive)=False));





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

Thanks. I used the coordinated subquery you suggested and it appears to
works great. I have tried to include an IIF expression as criteria in the
new field"Fee" to have negative results default to "0", but have not been
successful. If that can be done in this query, I would appreciate your help.
Here is current SQL

SELECT tblClient_Income_History_DateV1.ClientID, qryClientNameV3.ClientName,
Sum(([Cl_Mo_Inc_Source_Amt])) AS TotInc, (([TotInc])-466)*0.06 AS Fee,
tblClient_Income_History_DateV1.Cl_Mo_Inc_Date
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) INNER
JOIN tblClient_InfoV1 ON tblClient_Income_History_DateV1.ClientID =
tblClient_InfoV1.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, tblClient_InfoV1.Inactive,
tblClient_Income_History_DateV1.Cl_Mo_Inc_Date
HAVING (((tblClient_InfoV1.Inactive)=False));



John Spencer said:
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
'====================================================
Click to expand...
Click to expand...
 
G

Guest

Yes sir, that works. I was trying to put the IFF expression in the criteria.

Thanks again for your help.

John Spencer said:
You might have to redo the sum calculation for TotInc.

IIF(Sum([Cl_Mo_Inc_Source_Amt])>466,(Sum([Cl_Mo_Inc_Source_Amt])-466) *
..06, 0) as Fee

SELECT tblClient_Income_History_DateV1.ClientID,
qryClientNameV3.ClientName,
Sum(([Cl_Mo_Inc_Source_Amt])) AS TotInc
, IIF(Sum([Cl_Mo_Inc_Source_Amt])>466,(Sum([Cl_Mo_Inc_Source_Amt])-466)
* .06, 0) as Fee,
tblClient_Income_History_DateV1.Cl_Mo_Inc_Date
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) INNER
JOIN tblClient_InfoV1 ON tblClient_Income_History_DateV1.ClientID =
tblClient_InfoV1.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, tblClient_InfoV1.Inactive,
tblClient_Income_History_DateV1.Cl_Mo_Inc_Date
HAVING (((tblClient_InfoV1.Inactive)=False));





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

Thanks. I used the coordinated subquery you suggested and it appears to
works great. I have tried to include an IIF expression as criteria in the
new field"Fee" to have negative results default to "0", but have not been
successful. If that can be done in this query, I would appreciate your help.
Here is current SQL

SELECT tblClient_Income_History_DateV1.ClientID, qryClientNameV3.ClientName,
Sum(([Cl_Mo_Inc_Source_Amt])) AS TotInc, (([TotInc])-466)*0.06 AS Fee,
tblClient_Income_History_DateV1.Cl_Mo_Inc_Date
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) INNER
JOIN tblClient_InfoV1 ON tblClient_Income_History_DateV1.ClientID =
tblClient_InfoV1.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, tblClient_InfoV1.Inactive,
tblClient_Income_History_DateV1.Cl_Mo_Inc_Date
HAVING (((tblClient_InfoV1.Inactive)=False));



John Spencer said:
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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Select Max, return unwanted rows. 4
simple query not working 4
Query returns wrong results 2
Name in TextBox - problem with ControlSource? 1
Customer Query 1
3 Tables and query not updateable 1
Updatable Query (trying to edit records) 8
First() won't work - subquery question 4

Top