No Current Record Error - Query

[jjlogan]

I have not seen this error before when trying to execute a query based on three tables:
The Left table is joined to the Right tables with a Join-Type of "Select all records from Left table and only those from Right tables that contain the joined field value." The beginning records in one of the query's Right tables DO NOT match the Left table and I knew this but did not think it would create an Error. If I add a Criteria to my query that says do not consider those records in the Right table, then the query executes fine and I get a large recordset.
Can anyone shed light on why the query cannot be executed without Criteria and just based on the join type I'm using?
Thanks.
Jeff

 

[jjlogan]

I think I found my problem, but still don't quite understand the reason for the error. My query in SQL is: SELECT PriorityCd.NCRESP, TotalTrans.Reviewed, Sum(TotalTrans.AMOUNT) AS SumOfAMOUNT
FROM (PriorityCd LEFT JOIN TotalTrans ON PriorityCd.NCRESP = TotalTrans.NCRESP) LEFT JOIN NCLookupWithSummary ON PriorityCd.NC = NCLookupWithSummary.NC
GROUP BY PriorityCd.NCRESP, TotalTrans.Reviewed
ORDER BY PriorityCd.NCRESP;

This is a Sum-Select query and I am Grouping on a field in the Left table (that's fine) BUT I am also Grouping on a field in a Right table that has some records that do NOT match with Left table. NOW ... If I change the Join type to "Only records that match in Both tables," then the query executes fine.
Jeff

 

[PHV]

SELECT PriorityCd.NCRESP, TotalTrans.Reviewed, Sum(TotalTrans.AMOUNT) AS SumOfAMOUNT
FROM (PriorityCd LEFT JOIN TotalTrans ON PriorityCd.NCRESP = TotalTrans.NCRESP) LEFT JOIN NCLookupWithSummary ON PriorityCd.NC = NCLookupWithSummary.NC
GROUP BY PriorityCd.NCRESP, TotalTrans.Reviewed
ORDER BY PriorityCd.NCRESP;
Why 3 tables as you select nothing from NCLookupWithSummary ?
Have you tried something like this ?
SELECT PriorityCd.NCRESP, Nz(TotalTrans.Reviewed) As Reviewed, Sum(Nz(TotalTrans.AMOUNT,0)) AS SumOfAMOUNT
FROM PriorityCd LEFT JOIN TotalTrans ON PriorityCd.NCRESP = TotalTrans.NCRESP
GROUP BY PriorityCd.NCRESP, Nz(TotalTrans.Reviewed)
ORDER BY PriorityCd.NCRESP;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244