# HELP!!!!!! Access Error 3079



## Loope

The specified field <field> could refer to more than one table listed in the FROM clause of your SQL statement. (Error 3079)

OK so I have no clue what I'm doin when it comes to SQL statements. I'm trying to make a report from this query, but everytime I try it gives me this message. Everything in the database needs to refer to [Week Of].[Week Of] everything goes by the week range. If anyone could tell me what to do I'd really appreciate it!


----------



## mcwilliamc

I am Also experiencing a very similar Problem, however I believe that my SQL Query is much simpler. I'm also trying to run a report from this query, again everytime I try it gives me this message.

But note the query itself works in generating an output to a form which is executed with a macro.

The database uses a table - tbl All Reports ([Company Name],[ASX Code],[Size],[Horwath Rank],[Horwath Stars],[Report Year],[Grif Group Code]

QryFirstYear as follows
SELECT [tbl All Reports].[Company Name], [tbl All Reports].[ASX Code], [tbl All Reports].Size, [tbl All Reports].[Horwath Rank], [tbl All Reports].[Horwath Stars], [tbl All Reports].[Report Year], [tbl All Reports].[Grif Group Code]
FROM [tbl All Reports]
WHERE ((([tbl All Reports].[Report Year])=[Forms]![H frm BaseYear]![txtFirstYear]));

QrySecondYear as follows:
SELECT [tbl All Reports].[Company Name], [tbl All Reports].[ASX Code], [tbl All Reports].Size, [tbl All Reports].[Horwath Rank], [tbl All Reports].[Horwath Stars], [tbl All Reports].[Report Year], [tbl All Reports].[Grif Group Code]
FROM qryFirstYear LEFT JOIN [tbl All Reports] ON qryFirstYear.[Grif Group Code]=[tbl All Reports].[Grif Group Code]
WHERE ((([tbl All Reports].[Report Year])=Forms![H frm SecondYear]!txtSecondYear));

QryThirdYear as follows:
SELECT [tbl All Reports].[Company Name], [tbl All Reports].[ASX Code], [tbl All Reports].Size, [tbl All Reports].[Horwath Rank], [tbl All Reports].[Horwath Stars], [tbl All Reports].[Report Year], [tbl All Reports].[Grif Group Code]
FROM qryFirstYear LEFT JOIN [tbl All Reports] ON qryFirstYear.[Grif Group Code] = [tbl All Reports].[Grif Group Code]
WHERE ((([tbl All Reports].[Report Year])=[Forms]![H frm ThirdYear]![txtThirdYear]));

The Following Query combines the results of each of these three queries using 'Left Joins':
SELECT qryFirstYear.[Company Name], qryFirstYear.[ASX Code], qryFirstYear.Size, qryFirstYear.[Horwath Rank], qryFirstYear.[Horwath Stars], qrySecondYear.[ASX Code], qrySecondYear.Size, qrySecondYear.[Horwath Rank], qrySecondYear.[Horwath Stars], qryThirdYear.[ASX Code], qryThirdYear.Size, qryThirdYear.[Horwath Rank], qryThirdYear.[Horwath Stars]
FROM (qryFirstYear LEFT JOIN qrySecondYear ON qryFirstYear.[Grif Group Code]=qrySecondYear.[Grif Group Code]) LEFT JOIN qryThirdYear ON qryFirstYear.[Grif Group Code]=qryThirdYear.[Grif Group Code];


But why does the Form function but I cannot use the same query to produce a report?

Anyone with an answer


----------



## mcwilliamc

In the words of my previous workplace mentor ray:

"The problem is with your field names. Don't use the same field names as outputs from a query & you will not have ambiguous join errors.
eg. Where you have ASX field names for first, second & third years, rename them as ASX1, ASX2 & ASX3. Similar for all other repeated field names.
You do this in the allYears query by using labels (or aliases) as follows: replace "ASX" with "ASX1: ASX" , where ASX1: is the label & ASX is the original field name.
The report will work now without inner confusion "

Thankyou Trevor


----------

