By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,719 Members | 1,811 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,719 IT Pros & Developers. It's quick & easy.

The specified field could refer to more than one table listed in the FROM clause

P: 8
I am getting this error after running my query

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

my contractnum is a one to many to another table.

how do i fix this?
Jul 2 '07 #1
Share this Question
Share on Google+
4 Replies


NeoPa
Expert Mod 15k+
P: 31,606
You have (accidentally) posted this question in the Access Articles section. This is NOT an article.
I'm moving this to the main Access questions forum.

MODERATOR.
Jul 2 '07 #2

NeoPa
Expert Mod 15k+
P: 31,606
I suspect this is quite straightforward, but posting a copy of the SQL that is triggering the error message would prove helpful.
From there we can tell you precisely what to fix.
Jul 2 '07 #3

nico5038
Expert 2.5K+
P: 3,072
You'll have to "qualify" the field [contractnum] as it's apperently part of two tables.
Let's assume you have the tables tblA and tblB, then use e.g.:
[tblA].[contractnum]
When it's tblA having the needed value. For a full (INNER) join the table won't matter, but when you have an outer (LEFT or RIGHT) join (there's an arrow displayed instead of a line), then make sure you use the "master" table to qualify the field

Nic;o)
Jul 3 '07 #4

P: 5
I get the same error message (the Datasheet View works). My SQL is

SELECT CONTRACT_ITEMS.EXP_DATE, Format([CONTRACT_ITEMS.EXP_DATE],"mmmm") AS Expr1, Format([CONTRACT_ITEMS.EXP_DATE],"mm") AS Expr2, Trim([ENTITY_1.FIRST_NAME]) & " " & Trim([ENTITY_1.LAST_NAME]) AS Expr3, CONTRACT_BUDGETS.FISCAL_YEAR, CONTRACT_BUDGETS.FYR_NOTES, ENTITY.LAST_NAME, CONTRACT_SPACE.CONTRACT_DESC, CONTRACT_SPACE.CONTRACT_TYPE, CONTRACT_SPACE.RENEWAL_DATE, CONTRACT_BUDGETS.FYR_BUDGET, CONTRACT_SPACE.EXP_DATE, CONTRACT_BUDGETS.FYR_SPENT, ENTITY_1.FIRST_NAME, ENTITY_1.LAST_NAME, CONTRACT_ITEMS.COMMENT
FROM (ENTITY AS ENTITY_1 RIGHT JOIN ((ENTITY INNER JOIN (CONTRACT_SPACE LEFT JOIN CONTRACT_ITEMS ON CONTRACT_SPACE.CONTRACT_ID = CONTRACT_ITEMS.CONTRACT_ID) ON ENTITY.ID = CONTRACT_SPACE.VENDOR_ID) LEFT JOIN CONTRACT_OWNERS ON CONTRACT_SPACE.CONTRACT_ID = CONTRACT_OWNERS.CONTRACT_ID) ON ENTITY_1.ID = CONTRACT_OWNERS.OWNER_ID) LEFT JOIN CONTRACT_BUDGETS ON CONTRACT_SPACE.CONTRACT_ID = CONTRACT_BUDGETS.CONTRACT_ID
WHERE (((Format([CONTRACT_ITEMS.EXP_DATE],"mmmm"))>=Format(Now(),"mmmm")) AND ((Format([CONTRACT_ITEMS.EXP_DATE],"mm"))>=Format(Now(),"mm")-2) AND ((CONTRACT_BUDGETS.FISCAL_YEAR)=Year(Now())));

Any help would be appreciated.
Dec 2 '09 #5

Post your reply

Sign in to post your reply or Sign up for a free account.