467,150 Members | 1,123 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,150 developers. It's quick & easy.

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

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
  • viewed: 11722
Share:
4 Replies
NeoPa
Expert Mod 16PB
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 16PB
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 2GB
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
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.

Similar topics

5 posts views Thread by Colleyville Alan | last post: by
38 posts views Thread by sopranos2@gmail.com | last post: by
3 posts views Thread by =?Utf-8?B?UGF1bCBQcmV3ZXR0?= | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.