Could refer to more than one table

Z

zufie

I am trying to fix my report.

I receive the following error when I attempt to run my report:

The specified field qryHSFTageEdu.[SumOfQ1 14 Years] could refer to
more than one table listed in the FROM clause of your SQL statement.

Here is the SQL code behind my MS Access query:

SELECT Sum([Tbl HSFT age].[Q1 <=12 Years]) AS [SumOfQ1 <=12 Years], Sum
([Tbl HSFT age].[Q1 13 Years]) AS [SumOfQ1 13 Years], Sum([Tbl HSFT
age].[Q1 14 Years]) AS [SumOfQ1 14 Years], Sum([Tbl HSFT age].[Q1 >=15
years]) AS [SumOfQ1 >=15 years], Sum([Tbl HSFT age].[Q1 Other]) AS
[SumOfQ1 Other], Sum([Tbl HSFT age].[Q1 No Response]) AS [SumOfQ1 No
Response], Sum([Tbl HSFT age].[Q2 <=12 Years]) AS [SumOfQ2 <=12
Years], Sum([Tbl HSFT age].[Q2 13 Years]) AS [SumOfQ2 13 Years], Sum
([Tbl HSFT age].[Q2 14 Years]) AS [SumOfQ2 14 Years], Sum([Tbl HSFT
age].[Q2 >=15 years]) AS [SumOfQ2 >=15 years], Sum([Tbl HSFT age].[Q2
Other]) AS [SumOfQ2 Other], Sum([Tbl HSFT age].[Q2 No Response]) AS
[SumOfQ2 No Response], Sum([Tbl HSFT age].[Q3 <=12 Years]) AS [SumOfQ3
<=12 Years], Sum([Tbl HSFT age].[Q3 13 Years]) AS [SumOfQ3 13 Years],
Sum([Tbl HSFT age].[Q3 14 Years]) AS [SumOfQ3 14 Years], Sum([Tbl HSFT
age].[Q3 >=15 years]) AS [SumOfQ3 >=15 years], Sum([Tbl HSFT age].[Q3
Other]) AS [SumOfQ3 Other1], Sum([Tbl HSFT age].[Q3 No Response]) AS
[SumOfQ3 No Response], Sum([Tbl HSFT age].[Q4 <=12 Years]) AS [SumOfQ4
<=12 Years], Sum([Tbl HSFT age].[Q4 13 Years]) AS [SumOfQ4 13 Years],
Sum([Tbl HSFT age].[Q4 14 Years]) AS [SumOfQ4 14 Years], Sum([Tbl HSFT
age].[Q4 >=15 years]) AS [SumOfQ4 >=15 years], Sum([Tbl HSFT age].[Q4
Other]) AS [SumOfQ4 Other], Sum([Tbl HSFT age].[Q4 No Response]) AS
[SumOfQ4 No Response], Sum([Tbl HSFT Education].[Q1 <=8th Grade]) AS
[SumOfQ1 <=8th Grade], Sum([Tbl HSFT Education].[Q1 9th-12th Grade])
AS [SumOfQ1 9th-12th Grade], Sum([Tbl HSFT Education].[Q1 Other]) AS
[SumOfQ1 Other1], Sum([Tbl HSFT Education].[Q1 No Response]) AS
[SumOfQ1 No Response1], Sum([Tbl HSFT Education].[Q2 <=8th Grade]) AS
[SumOfQ2 <=8th Grade], Sum([Tbl HSFT Education].[Q2 9th-12th Grade])
AS [SumOfQ2 9th-12th Grade], Sum([Tbl HSFT Education].[Q2 Other]) AS
[SumOfQ2 Other1], Sum([Tbl HSFT Education].[Q2 No Response]) AS
[SumOfQ2 No Response1], Sum([Tbl HSFT Education].[Q3 <=8th Grade]) AS
[SumOfQ3 <=8th Grade], Sum([Tbl HSFT Education].[Q3 9th-12th Grade])
AS [SumOfQ3 9th-12th Grade], Sum([Tbl HSFT Education].[Q3 Other]) AS
[SumOfQ3 Other], Sum([Tbl HSFT Education].[Q3 No Response]) AS
[SumOfQ3 No Response1], Sum([Tbl HSFT Education].[Q4 <=8th Grade]) AS
[SumOfQ4 <=8th Grade], Sum([Tbl HSFT Education].[Q4 9th-12th Grade])
AS [SumOfQ4 9th-12th Grade], [Tbl HSFT Education].[Q4 Other], Sum([Tbl
HSFT Education].[Q4 No Response]) AS [SumOfQ4 No Response1]
FROM [Tbl HSFT age] INNER JOIN [Tbl HSFT Education] ON [Tbl HSFT
age].ID = [Tbl HSFT Education].ID
GROUP BY [Tbl HSFT Education].[Q4 Other];

Thanks,

John
 
J

Jerry Whittle

Does the query run OK by itself? If not, how does it run if not a Totals
query?

There's a very good chance that the problem has nothing to do with the field
in question. As the data isn't normalized, a problem could creep in anywhere
such as a null value or a non-number. Are all those fields set as a number
data type and have at least a 0 in each record for all those fields?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


zufie said:
I am trying to fix my report.

I receive the following error when I attempt to run my report:

The specified field qryHSFTageEdu.[SumOfQ1 14 Years] could refer to
more than one table listed in the FROM clause of your SQL statement.

Here is the SQL code behind my MS Access query:

SELECT Sum([Tbl HSFT age].[Q1 <=12 Years]) AS [SumOfQ1 <=12 Years], Sum
([Tbl HSFT age].[Q1 13 Years]) AS [SumOfQ1 13 Years], Sum([Tbl HSFT
age].[Q1 14 Years]) AS [SumOfQ1 14 Years], Sum([Tbl HSFT age].[Q1 >=15
years]) AS [SumOfQ1 >=15 years], Sum([Tbl HSFT age].[Q1 Other]) AS
[SumOfQ1 Other], Sum([Tbl HSFT age].[Q1 No Response]) AS [SumOfQ1 No
Response], Sum([Tbl HSFT age].[Q2 <=12 Years]) AS [SumOfQ2 <=12
Years], Sum([Tbl HSFT age].[Q2 13 Years]) AS [SumOfQ2 13 Years], Sum
([Tbl HSFT age].[Q2 14 Years]) AS [SumOfQ2 14 Years], Sum([Tbl HSFT
age].[Q2 >=15 years]) AS [SumOfQ2 >=15 years], Sum([Tbl HSFT age].[Q2
Other]) AS [SumOfQ2 Other], Sum([Tbl HSFT age].[Q2 No Response]) AS
[SumOfQ2 No Response], Sum([Tbl HSFT age].[Q3 <=12 Years]) AS [SumOfQ3
<=12 Years], Sum([Tbl HSFT age].[Q3 13 Years]) AS [SumOfQ3 13 Years],
Sum([Tbl HSFT age].[Q3 14 Years]) AS [SumOfQ3 14 Years], Sum([Tbl HSFT
age].[Q3 >=15 years]) AS [SumOfQ3 >=15 years], Sum([Tbl HSFT age].[Q3
Other]) AS [SumOfQ3 Other1], Sum([Tbl HSFT age].[Q3 No Response]) AS
[SumOfQ3 No Response], Sum([Tbl HSFT age].[Q4 <=12 Years]) AS [SumOfQ4
<=12 Years], Sum([Tbl HSFT age].[Q4 13 Years]) AS [SumOfQ4 13 Years],
Sum([Tbl HSFT age].[Q4 14 Years]) AS [SumOfQ4 14 Years], Sum([Tbl HSFT
age].[Q4 >=15 years]) AS [SumOfQ4 >=15 years], Sum([Tbl HSFT age].[Q4
Other]) AS [SumOfQ4 Other], Sum([Tbl HSFT age].[Q4 No Response]) AS
[SumOfQ4 No Response], Sum([Tbl HSFT Education].[Q1 <=8th Grade]) AS
[SumOfQ1 <=8th Grade], Sum([Tbl HSFT Education].[Q1 9th-12th Grade])
AS [SumOfQ1 9th-12th Grade], Sum([Tbl HSFT Education].[Q1 Other]) AS
[SumOfQ1 Other1], Sum([Tbl HSFT Education].[Q1 No Response]) AS
[SumOfQ1 No Response1], Sum([Tbl HSFT Education].[Q2 <=8th Grade]) AS
[SumOfQ2 <=8th Grade], Sum([Tbl HSFT Education].[Q2 9th-12th Grade])
AS [SumOfQ2 9th-12th Grade], Sum([Tbl HSFT Education].[Q2 Other]) AS
[SumOfQ2 Other1], Sum([Tbl HSFT Education].[Q2 No Response]) AS
[SumOfQ2 No Response1], Sum([Tbl HSFT Education].[Q3 <=8th Grade]) AS
[SumOfQ3 <=8th Grade], Sum([Tbl HSFT Education].[Q3 9th-12th Grade])
AS [SumOfQ3 9th-12th Grade], Sum([Tbl HSFT Education].[Q3 Other]) AS
[SumOfQ3 Other], Sum([Tbl HSFT Education].[Q3 No Response]) AS
[SumOfQ3 No Response1], Sum([Tbl HSFT Education].[Q4 <=8th Grade]) AS
[SumOfQ4 <=8th Grade], Sum([Tbl HSFT Education].[Q4 9th-12th Grade])
AS [SumOfQ4 9th-12th Grade], [Tbl HSFT Education].[Q4 Other], Sum([Tbl
HSFT Education].[Q4 No Response]) AS [SumOfQ4 No Response1]
FROM [Tbl HSFT age] INNER JOIN [Tbl HSFT Education] ON [Tbl HSFT
age].ID = [Tbl HSFT Education].ID
GROUP BY [Tbl HSFT Education].[Q4 Other];

Thanks,

John
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Report prob 3
Reports are only totaling the fields for the first record 2
Column Number as parameter in Query to select data 1
Apparently Bogus "could refer to more than one. 9
Feild could refer to more than one table? 2
Best approach to report results by quarter 4
Lookup Table Used More than Once in a Query 5
specified field [equipment] could refer to more than one table listed in FROM clause of SQL statemen 1

Top