Problem referencing a field in a query

[Laurel]

When I try to specify child and master link fields in the source for a chart
(which is embedded in a form), I get this error.

"The specified field, qryLevelsAll.Student_ID could refer to more than one
table listed in the From clause of your SQL statement."

The child field is [qryLevelsAll.Student_id] (I've also tried
[qryLevelsAll].[Student_ID].

The row source for the chart is:

SELECT qryLevelsAll.Week_of, Avg(qryLevelsAll.Level) AS AvgOfLevel FROM
qryLevelsAll GROUP BY qryLevelsAll.Week_of ORDER BY qryLevelsAll.Week_of;

The SQL for the query qryLevelsAll is:
SELECT [first_name] & " " & [last_name] AS Full_Name,
fncWeekStartDate([level_date]) AS Week_of, tblStudents.Student_ID,
tblStudents.Class_Code,
fncLevelAsNumber([tblLevels.Student_ID],[Level_Date]) AS [Level],
tblLevels.*
FROM tblStudents INNER JOIN tblLevels ON tblStudents.Student_ID =
tblLevels.Student_ID
ORDER BY [first_name] & " " & [last_name], fncWeekStartDate([level_date])
DESC;

 

[Jeff Boyce]

Laurel

When I get that message, it usually means I have the field available from
more than one table/query source. I don't believe I've seen the xxxx.yyyy
reference before.

Can you confirm that there is only one copy of the query qryLevelsALL in
your source?

Jeff Boyce
<Access MVP>

 

[Laurel]

Laurel

When I get that message, it usually means I have the field available from
more than one table/query source. I don't believe I've seen the xxxx.yyyy
reference before.

Can you confirm that there is only one copy of the query qryLevelsALL in
your source?

I'm not sure what you mean if having included the row source for the chart
and the SQL for qryLevelsAll doesn't do it. How else would I confirm it?

The row source for the chart is:

SELECT qryLevelsAll.Week_of, Avg(qryLevelsAll.Level) AS AvgOfLevel FROM
qryLevelsAll GROUP BY qryLevelsAll.Week_of ORDER BY qryLevelsAll.Week_of;

Jeff Boyce
<Access MVP>

When I try to specify child and master link fields in the source for a chart
(which is embedded in a form), I get this error.

"The specified field, qryLevelsAll.Student_ID could refer to more than
one
table listed in the From clause of your SQL statement."

The child field is [qryLevelsAll.Student_id] (I've also tried
[qryLevelsAll].[Student_ID].

The row source for the chart is:

SELECT qryLevelsAll.Week_of, Avg(qryLevelsAll.Level) AS AvgOfLevel FROM
qryLevelsAll GROUP BY qryLevelsAll.Week_of ORDER BY qryLevelsAll.Week_of;

The SQL for the query qryLevelsAll is:
SELECT [first_name] & " " & [last_name] AS Full_Name,
fncWeekStartDate([level_date]) AS Week_of, tblStudents.Student_ID,
tblStudents.Class_Code,
fncLevelAsNumber([tblLevels.Student_ID],[Level_Date]) AS [Level],
tblLevels.*
FROM tblStudents INNER JOIN tblLevels ON tblStudents.Student_ID =
tblLevels.Student_ID
ORDER BY [first_name] & " " & [last_name], fncWeekStartDate([level_date])
DESC;

 

[Jeff Boyce]

Laurel

I only see one reference to a table in your SQL statement's FROM clause, so
I'm puzzled too.

When I run out of things to tweak/change in a query, and it still doesn't
work, I generally throw away the (non-working) query and create a new one,
just in case the old one was somehow subtly corrupted.

In (re-)building the new one, I generally only add one component at a time,
to ensure each portion is working the way I expect. When I get the data set
I expect, then I move on to forms, reports, charts, ...

Regards

Jeff Boyce
<Access MVP>

Laurel

When I get that message, it usually means I have the field available from
more than one table/query source. I don't believe I've seen the xxxx.yyyy
reference before.

Can you confirm that there is only one copy of the query qryLevelsALL in
your source?

I'm not sure what you mean if having included the row source for the chart
and the SQL for qryLevelsAll doesn't do it. How else would I confirm it?

The row source for the chart is:

SELECT qryLevelsAll.Week_of, Avg(qryLevelsAll.Level) AS AvgOfLevel FROM
qryLevelsAll GROUP BY qryLevelsAll.Week_of ORDER BY qryLevelsAll.Week_of;

Jeff Boyce
<Access MVP>

When I try to specify child and master link fields in the source for a chart
(which is embedded in a form), I get this error.

"The specified field, qryLevelsAll.Student_ID could refer to more than
one
table listed in the From clause of your SQL statement."

The child field is [qryLevelsAll.Student_id] (I've also tried
[qryLevelsAll].[Student_ID].

The row source for the chart is:

SELECT qryLevelsAll.Week_of, Avg(qryLevelsAll.Level) AS AvgOfLevel FROM
qryLevelsAll GROUP BY qryLevelsAll.Week_of ORDER BY qryLevelsAll.Week_of;

The SQL for the query qryLevelsAll is:
SELECT [first_name] & " " & [last_name] AS Full_Name,
fncWeekStartDate([level_date]) AS Week_of, tblStudents.Student_ID,
tblStudents.Class_Code,
fncLevelAsNumber([tblLevels.Student_ID],[Level_Date]) AS [Level],
tblLevels.*
FROM tblStudents INNER JOIN tblLevels ON tblStudents.Student_ID =
tblLevels.Student_ID
ORDER BY [first_name] & " " & [last_name], fncWeekStartDate([level_date])
DESC;