Hi all,
Any idea why I get this error when I go to create a report off of the query in question, but the query otherwise runs fine (ie, I can hit "run", and look at the datasheet view of the query, no error. Base a report off of that query, go to run the report, above error.
Copy the query, and just do a SELECT * into a new query, run the report off of that, no popup error, but an #Error message appears the field based off of the underlying subquery. Again, the query and subquery run fine on their own; the issue seems to be in using it for a report.
Any ideas?
Query is a mess, but see below:
SELECT Q.LastName AS [Last], Q.FirstName AS [First], Q1.LastName AS TrainerLastName, Q1.FirstName AS TrainerFirstName, qry1.Process, qry1.mostRecentDate, t1.trbpnotes, tblSOP.bpname AS ProcessName, tblSOP.bpnumber AS SOPNumber, (SELECT TOP 1 CD.crtlDocRev FROM tblMasterListControlledDocs AS CD LEFT JOIN tblSOP AS S on CD.[ctrlDocDoc#] = S.bpnumber WHERE CD.ctrlDocDate <= qry1.mostRecentDate AND S.bpid = qry1.Process) AS RevID
FROM (((qry1 INNER JOIN tblTrainingRecords AS t1 ON (qry1.Employee = t1.trbpEmp) AND (qry1.Process = t1.trbpnumber) AND (qry1.mostRecentDate = t1.trbpdate)) LEFT JOIN ForQData AS Q ON qry1.Employee = Q.ID) LEFT JOIN ForQData AS Q1 ON t1.trbpTrainer = Q1.ID) LEFT JOIN tblSOP ON qry1.Process = tblSOP.bpid
WHERE (((Q.ID)=[Forms]![frmEmpSelect]![Combo6]));
The issue is with:
(SELECT TOP 1 CD.crtlDocRev FROM tblMasterListControlledDocs AS CD LEFT JOIN tblSOP AS S on CD.[ctrlDocDoc#] = S.bpnumber WHERE CD.ctrlDocDate <= qry1.mostRecentDate AND S.bpid = qry1.Process) AS RevID
qry1 is:
SELECT t1.trbpEmp AS Employee, t1.trbpnumber AS Process, Max(t1.trbpdate) AS mostRecentDate
FROM tblTrainingRecords AS t1
GROUP BY t1.trbpEmp, t1.trbpnumber;
So it would appear that the issue is in this "nesting" of queries. Is there a way around this?
EDIT:
I think I've answered my own question. Allen Browne on point as ever:
The relevant passage for me:
"
I don't actually have any grouping or sorting going on at the moment (would like to do that in the future...) and I'd already tried pushing everything under a rug (eg, throwing it into another query),
BUT (and I'm adding all this because it's wasn't mentioned elsewhere that I could find--)
If you do ANY calculated expression in the header or footer, it throws the error. I had a =[First] & " " & [Last] textbox in the header. Deleted that, and no more error.
Access is such a shitshow.
Any idea why I get this error when I go to create a report off of the query in question, but the query otherwise runs fine (ie, I can hit "run", and look at the datasheet view of the query, no error. Base a report off of that query, go to run the report, above error.
Copy the query, and just do a SELECT * into a new query, run the report off of that, no popup error, but an #Error message appears the field based off of the underlying subquery. Again, the query and subquery run fine on their own; the issue seems to be in using it for a report.
Any ideas?
Query is a mess, but see below:
SELECT Q.LastName AS [Last], Q.FirstName AS [First], Q1.LastName AS TrainerLastName, Q1.FirstName AS TrainerFirstName, qry1.Process, qry1.mostRecentDate, t1.trbpnotes, tblSOP.bpname AS ProcessName, tblSOP.bpnumber AS SOPNumber, (SELECT TOP 1 CD.crtlDocRev FROM tblMasterListControlledDocs AS CD LEFT JOIN tblSOP AS S on CD.[ctrlDocDoc#] = S.bpnumber WHERE CD.ctrlDocDate <= qry1.mostRecentDate AND S.bpid = qry1.Process) AS RevID
FROM (((qry1 INNER JOIN tblTrainingRecords AS t1 ON (qry1.Employee = t1.trbpEmp) AND (qry1.Process = t1.trbpnumber) AND (qry1.mostRecentDate = t1.trbpdate)) LEFT JOIN ForQData AS Q ON qry1.Employee = Q.ID) LEFT JOIN ForQData AS Q1 ON t1.trbpTrainer = Q1.ID) LEFT JOIN tblSOP ON qry1.Process = tblSOP.bpid
WHERE (((Q.ID)=[Forms]![frmEmpSelect]![Combo6]));
The issue is with:
(SELECT TOP 1 CD.crtlDocRev FROM tblMasterListControlledDocs AS CD LEFT JOIN tblSOP AS S on CD.[ctrlDocDoc#] = S.bpnumber WHERE CD.ctrlDocDate <= qry1.mostRecentDate AND S.bpid = qry1.Process) AS RevID
qry1 is:
SELECT t1.trbpEmp AS Employee, t1.trbpnumber AS Process, Max(t1.trbpdate) AS mostRecentDate
FROM tblTrainingRecords AS t1
GROUP BY t1.trbpEmp, t1.trbpnumber;
So it would appear that the issue is in this "nesting" of queries. Is there a way around this?
EDIT:
I think I've answered my own question. Allen Browne on point as ever:
Microsoft Access tips: Surviving subqueries
Techniques to avoid 8 common errors, frustrations, limitations, and bugs when using subqueries in Microsoft Access.
allenbrowne.com
The relevant passage for me:
"
- In report design, remove everything form the Sorting and Grouping dialog, and do not try to sum anything in the Report Header or Report Footer. (In most cases this is not a practical solution.)
- In query design, uncheck the Show box under the subquery. (This solution is practical only if you do not need to show the results of the subquery in the report.)
- Create a separate query that handles the subquery. Use this query as a source "table" for the query the report is based on. Moving the subquery to the lower level query sometimes (not always) avoids the problem, even if the second query is as simple as
SELECT * FROM Query1; - Use a domain aggregate function such as DSum() instead of a subquery. While this is fine for small tables, performance will be unusable for large ones.
- If nothing else works, create a temporary table to hold the data for the report. You can convert your query into an Append query (Append on Query menu in query design) to populate the temporary table, and then base the report on the temporary table."
I don't actually have any grouping or sorting going on at the moment (would like to do that in the future...) and I'd already tried pushing everything under a rug (eg, throwing it into another query),
BUT (and I'm adding all this because it's wasn't mentioned elsewhere that I could find--)
If you do ANY calculated expression in the header or footer, it throws the error. I had a =[First] & " " & [Last] textbox in the header. Deleted that, and no more error.
Access is such a shitshow.
Attachments
Last edited: