Why wont my query Show Owner Name

[Bob V]

My query works fine shows OwnerID(Number) PaidAmount(Last) BillDate(Last)
but if I try to get the name of OwnerID it will not work,tblAccountStatus on
has the OwnerID (Number) tblOwnerInfo has LastName,FirstName
Thanks if you can help, I have got this far ....Bob
SELECT rh.OwnerID, rh.PaidAmount, rh.BillDate
FROM [SELECT max(Billdate) as maxdate, OwnerID FROM tblAccountStatus
GROUP BY OwnerID]. AS maxresults, tblAccountStatus AS rh
WHERE (((rh.OwnerID)=[maxresults].[OwnerID]) AND
((rh.BillDate)=[maxresults].[maxdate]));

 

[Tom Wickerath]

Hi Bob,

Try this, instead:

SELECT [OwnerLastName]+(", " & [OwnerFirstName]) AS Owner,
rh.PaidAmount, rh.BillDate
FROM (tblAccountStatus AS rh
INNER JOIN
[SELECT max(Billdate) as maxdate, OwnerID
FROM tblAccountStatus
GROUP BY OwnerID]. AS maxresults
ON (rh.BillDate = maxresults.maxdate)
AND (rh.OwnerID = maxresults.OwnerID))
INNER JOIN tblOwnerInfo ON rh.OwnerID = tblOwnerInfo.OwnerID
ORDER BY tblOwnerInfo.OwnerLastName, tblOwnerInfo.OwnerFirstName;


By the way, the SQL statement you presented is an excellent example of a
query that the Name Autocorrupt feature will break for you, if you accidently
turn it on in the future.

Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

My query works fine shows OwnerID(Number) PaidAmount(Last) BillDate(Last)
but if I try to get the name of OwnerID it will not work,tblAccountStatus on
has the OwnerID (Number) tblOwnerInfo has LastName,FirstName
Thanks if you can help, I have got this far ....Bob

SELECT rh.OwnerID, rh.PaidAmount, rh.BillDate
FROM [SELECT max(Billdate) as maxdate, OwnerID FROM tblAccountStatus
GROUP BY OwnerID]. AS maxresults, tblAccountStatus AS rh
WHERE (((rh.OwnerID)=[maxresults].[OwnerID]) AND
((rh.BillDate)=[maxresults].[maxdate]));

 

[Bob V]

Thanks Tom your script is giving me the same error as I have been getting
using different scenarios. When trying to form an association with
tblAccountStatus.OwnerID
and tblOwnerInfo.OwnerID to get their name
Error is:
The Microsoft Jet database engine cannot find the input table or query
Select max(Billdate) as maxdate,OwnerID From tblAccountStatus Group by
OwnerID
Make sure is existsand that the name is spell correctly
Thanks for your help....Bob

Hi Bob,

Try this, instead:

SELECT [OwnerLastName]+(", " & [OwnerFirstName]) AS Owner,
rh.PaidAmount, rh.BillDate
FROM (tblAccountStatus AS rh
INNER JOIN
[SELECT max(Billdate) as maxdate, OwnerID
FROM tblAccountStatus
GROUP BY OwnerID]. AS maxresults
ON (rh.BillDate = maxresults.maxdate)
AND (rh.OwnerID = maxresults.OwnerID))
INNER JOIN tblOwnerInfo ON rh.OwnerID = tblOwnerInfo.OwnerID
ORDER BY tblOwnerInfo.OwnerLastName, tblOwnerInfo.OwnerFirstName;


By the way, the SQL statement you presented is an excellent example of a
query that the Name Autocorrupt feature will break for you, if you
accidently
turn it on in the future.

Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

My query works fine shows OwnerID(Number) PaidAmount(Last) BillDate(Last)
but if I try to get the name of OwnerID it will not work,tblAccountStatus
on
has the OwnerID (Number) tblOwnerInfo has LastName,FirstName
Thanks if you can help, I have got this far ....Bob

SELECT rh.OwnerID, rh.PaidAmount, rh.BillDate
FROM [SELECT max(Billdate) as maxdate, OwnerID FROM tblAccountStatus
GROUP BY OwnerID]. AS maxresults, tblAccountStatus AS rh
WHERE (((rh.OwnerID)=[maxresults].[OwnerID]) AND
((rh.BillDate)=[maxresults].[maxdate]));

 

[Tom Wickerath]

Hi Bob,

Then you must have changed something in your database, because I used the
last copy that you sent to me by private e-mail some time ago, to test the
SQL statement before posting it. My copy of your database is several months
old now, but if you care to send me an updated copy, I'll try to determine
what is going on. Include a copy of the query I volunteered, and let me know
the name that you assigned to this query.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________