Change Field in SQL problem

  • Thread starter Thread starter Bob V
  • Start date Start date
B

Bob V

I have replaced QryOverdue.Payable with tblAccountStatus.PaidAmount, buy now
my query has gone from the correct 25 records to 256 records
Please any help..........Bob
**OLD**
SELECT QryOverdue.Name, Max(tblAccountStatus.BillDate) AS LastDate,
QryOverdue.Payable, tblOwnerInfo.OwnerFirstName, tblOwnerInfo.OwnerLastName,
tblAccountStatus.OwnerID
FROM (((tblAccountStatus INNER JOIN tblOwnerInfo ON tblAccountStatus.OwnerID
= tblOwnerInfo.OwnerID) INNER JOIN QryOverdue ON tblOwnerInfo.OwnerID =
QryOverdue.OwnerID) INNER JOIN qPaidAmountForPayment ON QryOverdue.OwnerID =
qPaidAmountForPayment.OwnerID) INNER JOIN qTotalDueForPayment ON
qPaidAmountForPayment.OwnerID = qTotalDueForPayment.OwnerID
GROUP BY QryOverdue.Name, QryOverdue.Payable, tblOwnerInfo.OwnerFirstName,
tblOwnerInfo.OwnerLastName, tblAccountStatus.OwnerID
ORDER BY QryOverdue.Name;

**NEW**
SELECT QryOverdue.Name, Max(tblAccountStatus.BillDate) AS LastDate,
tblAccountStatus.PaidAmount, tblOwnerInfo.OwnerFirstName,
tblOwnerInfo.OwnerLastName, tblAccountStatus.OwnerID
FROM (tblAccountStatus INNER JOIN tblOwnerInfo ON tblAccountStatus.OwnerID =
tblOwnerInfo.OwnerID) INNER JOIN QryOverdue ON tblOwnerInfo.OwnerID =
QryOverdue.OwnerID
GROUP BY QryOverdue.Name, tblAccountStatus.PaidAmount,
tblOwnerInfo.OwnerFirstName, tblOwnerInfo.OwnerLastName,
tblAccountStatus.OwnerID
ORDER BY QryOverdue.Name;
 
Got It :)...............Bob
SELECT QryOverdue.Name, Max(tblAccountStatus.BillDate) AS LastDate,
Last(tblAccountStatus.PaidAmount) AS LastOfPaidAmount,
tblOwnerInfo.OwnerFirstName, tblOwnerInfo.OwnerLastName,
tblAccountStatus.OwnerID
FROM (tblAccountStatus INNER JOIN tblOwnerInfo ON tblAccountStatus.OwnerID =
tblOwnerInfo.OwnerID) INNER JOIN QryOverdue ON tblOwnerInfo.OwnerID =
QryOverdue.OwnerID
GROUP BY QryOverdue.Name, tblOwnerInfo.OwnerFirstName,
tblOwnerInfo.OwnerLastName, tblAccountStatus.OwnerID
ORDER BY QryOverdue.Name;
 
Back
Top