Access can't represent the join expression

[Guest]

Hi all,

working in MSAccess2003 & XP.
I create joins between 2 or more tables in a query, I close and save this
query, and when I want to open it again, I get the msg "Access can't
represent the join expression [table1].[field]=[table2].[field]".

That's weird as I know these fields and tables exist and could be linked
without any problem in the past.

Any idea on what to do ?
Thx in advance

 

[Ken Snell \(MVP\)]

You can get this error if the tables are in a backend file and the front end
is not currently linked to the tables in that backend.

 

[Guest]

Hi Ken,

thx for your feeback, but I don't really understand what you mean with
"backend file"... Do you mean a table linked to a file outside the DB ?
Actually, both tables are in the DB.

Nicodemus

You can get this error if the tables are in a backend file and the front end
is not currently linked to the tables in that backend.

--

Ken Snell
<MS ACCESS MVP>

Hi all,

working in MSAccess2003 & XP.
I create joins between 2 or more tables in a query, I close and save this
query, and when I want to open it again, I get the msg "Access can't
represent the join expression [table1].[field]=[table2].[field]".

That's weird as I know these fields and tables exist and could be linked
without any problem in the past.

Any idea on what to do ?
Thx in advance

 

[Ken Snell \(MVP\)]

Post the SQL statement of one of the queries that is giving you this
problem. Often, this results from (1) the inability to link to the tables in
a backend file (another database file) that I mentioned earlier; or (2)
using a "non-equijoin" (which it doesn't appear you're doing), or (3) an
error in the name of a table and/or a field in a table, or (4) you renamed a
table or field name and didn't revise the query to those new names, or (5)
not sure why you're getting this error.

--

Ken Snell
<MS ACCESS MVP>

Hi Ken,

thx for your feeback, but I don't really understand what you mean with
"backend file"... Do you mean a table linked to a file outside the DB ?
Actually, both tables are in the DB.

Nicodemus

You can get this error if the tables are in a backend file and the front
end
is not currently linked to the tables in that backend.

--

Ken Snell
<MS ACCESS MVP>

Hi all,

working in MSAccess2003 & XP.
I create joins between 2 or more tables in a query, I close and save
this
query, and when I want to open it again, I get the msg "Access can't
represent the join expression [table1].[field]=[table2].[field]".

That's weird as I know these fields and tables exist and could be
linked
without any problem in the past.

Any idea on what to do ?
Thx in advance

 

[Guest]

ok, here is the SQL statment just before saving the query:

UPDATE (RDW INNER JOIN S_Zones ON (S_Zones.Prod_code = RDW.Product_Code) AND
(RDW.[Dest Station] = S_Zones.Station)) INNER JOIN Customer_Accounts ON
(RDW.[Shipper Account] = Customer_Accounts.Account_Number) AND
(S_Zones.Bill_Cntry = Customer_Accounts.Country) SET RDW.[Dest Zone] =
[Zone_Outb];

After saving, closing and opening the query, I get the msg "...can't
represent join expression S_Zones.prod=RDW.Product_code..."

UPDATE (RDW INNER JOIN Sony_Zones ON RDW.[Dest Station] =
Sony_Zones.Station) INNER JOIN Customer_Accounts ON (Sony_Zones.Bill_Cntry =
Customer_Accounts.Country) AND (RDW.[Shipper Account] =
Customer_Accounts.Account_Number) SET RDW.[Dest Zone] = [Zone_Outb];

Thx for your help already. I have the feeling that adding 3 tables in the
query and then linking them together creates the prob.
Nicodemus

Post the SQL statement of one of the queries that is giving you this
problem. Often, this results from (1) the inability to link to the tables in
a backend file (another database file) that I mentioned earlier; or (2)
using a "non-equijoin" (which it doesn't appear you're doing), or (3) an
error in the name of a table and/or a field in a table, or (4) you renamed a
table or field name and didn't revise the query to those new names, or (5)
not sure why you're getting this error.

--

Ken Snell
<MS ACCESS MVP>

Hi Ken,

thx for your feeback, but I don't really understand what you mean with
"backend file"... Do you mean a table linked to a file outside the DB ?
Actually, both tables are in the DB.

Nicodemus

You can get this error if the tables are in a backend file and the front
end
is not currently linked to the tables in that backend.

--

Ken Snell
<MS ACCESS MVP>

Hi all,

working in MSAccess2003 & XP.
I create joins between 2 or more tables in a query, I close and save
this
query, and when I want to open it again, I get the msg "Access can't
represent the join expression [table1].[field]=[table2].[field]".

That's weird as I know these fields and tables exist and could be
linked
without any problem in the past.

Any idea on what to do ?
Thx in advance

 

[Ken Snell \(MVP\)]

My first inclination is that you've misspelled the name of a field in either
the "S_Zones" (or is it Sony_Zones?) table or the RDW table for that join?

Did you try building a select query for the three tables first, then change
it to an update query?

--

Ken Snell
<MS ACCESS MVP>

ok, here is the SQL statment just before saving the query:

UPDATE (RDW INNER JOIN S_Zones ON (S_Zones.Prod_code = RDW.Product_Code)
AND
(RDW.[Dest Station] = S_Zones.Station)) INNER JOIN Customer_Accounts ON
(RDW.[Shipper Account] = Customer_Accounts.Account_Number) AND
(S_Zones.Bill_Cntry = Customer_Accounts.Country) SET RDW.[Dest Zone] =
[Zone_Outb];

After saving, closing and opening the query, I get the msg "...can't
represent join expression S_Zones.prod=RDW.Product_code..."

UPDATE (RDW INNER JOIN Sony_Zones ON RDW.[Dest Station] =
Sony_Zones.Station) INNER JOIN Customer_Accounts ON (Sony_Zones.Bill_Cntry
=
Customer_Accounts.Country) AND (RDW.[Shipper Account] =
Customer_Accounts.Account_Number) SET RDW.[Dest Zone] = [Zone_Outb];

Thx for your help already. I have the feeling that adding 3 tables in the
query and then linking them together creates the prob.
Nicodemus

Post the SQL statement of one of the queries that is giving you this
problem. Often, this results from (1) the inability to link to the tables
in
a backend file (another database file) that I mentioned earlier; or (2)
using a "non-equijoin" (which it doesn't appear you're doing), or (3) an
error in the name of a table and/or a field in a table, or (4) you
renamed a
table or field name and didn't revise the query to those new names, or
(5)
not sure why you're getting this error.

--

Ken Snell
<MS ACCESS MVP>

Hi Ken,

thx for your feeback, but I don't really understand what you mean with
"backend file"... Do you mean a table linked to a file outside the DB ?
Actually, both tables are in the DB.

Nicodemus

:

You can get this error if the tables are in a backend file and the
front
end
is not currently linked to the tables in that backend.

--

Ken Snell
<MS ACCESS MVP>

Hi all,

working in MSAccess2003 & XP.
I create joins between 2 or more tables in a query, I close and save
this
query, and when I want to open it again, I get the msg "Access can't
represent the join expression [table1].[field]=[table2].[field]".

That's weird as I know these fields and tables exist and could be
linked
without any problem in the past.

Any idea on what to do ?
Thx in advance

 

[Guest]

indeed, "S_Zones" = "Sony_Zones". Actually I didn't want to post the real
field names..... well done, isn't it ?

yes, I first build a select query and thne convert it to an update query. Do
you think it might be the reason ?

My first inclination is that you've misspelled the name of a field in either
the "S_Zones" (or is it Sony_Zones?) table or the RDW table for that join?

Did you try building a select query for the three tables first, then change
it to an update query?

--

Ken Snell
<MS ACCESS MVP>

ok, here is the SQL statment just before saving the query:

UPDATE (RDW INNER JOIN S_Zones ON (S_Zones.Prod_code = RDW.Product_Code)
AND
(RDW.[Dest Station] = S_Zones.Station)) INNER JOIN Customer_Accounts ON
(RDW.[Shipper Account] = Customer_Accounts.Account_Number) AND
(S_Zones.Bill_Cntry = Customer_Accounts.Country) SET RDW.[Dest Zone] =
[Zone_Outb];

After saving, closing and opening the query, I get the msg "...can't
represent join expression S_Zones.prod=RDW.Product_code..."

UPDATE (RDW INNER JOIN Sony_Zones ON RDW.[Dest Station] =
Sony_Zones.Station) INNER JOIN Customer_Accounts ON (Sony_Zones.Bill_Cntry
=
Customer_Accounts.Country) AND (RDW.[Shipper Account] =
Customer_Accounts.Account_Number) SET RDW.[Dest Zone] = [Zone_Outb];

Thx for your help already. I have the feeling that adding 3 tables in the
query and then linking them together creates the prob.
Nicodemus

Post the SQL statement of one of the queries that is giving you this
problem. Often, this results from (1) the inability to link to the tables
in
a backend file (another database file) that I mentioned earlier; or (2)
using a "non-equijoin" (which it doesn't appear you're doing), or (3) an
error in the name of a table and/or a field in a table, or (4) you
renamed a
table or field name and didn't revise the query to those new names, or
(5)
not sure why you're getting this error.

--

Ken Snell
<MS ACCESS MVP>

Hi Ken,

thx for your feeback, but I don't really understand what you mean with
"backend file"... Do you mean a table linked to a file outside the DB ?
Actually, both tables are in the DB.

Nicodemus

:

You can get this error if the tables are in a backend file and the
front
end
is not currently linked to the tables in that backend.

--

Ken Snell
<MS ACCESS MVP>

Hi all,

working in MSAccess2003 & XP.
I create joins between 2 or more tables in a query, I close and save
this
query, and when I want to open it again, I get the msg "Access can't
represent the join expression [table1].[field]=[table2].[field]".

That's weird as I know these fields and tables exist and could be
linked
without any problem in the past.

Any idea on what to do ?
Thx in advance

 

[Ken Snell \(MVP\)]

That should work in most situations.

Just a wild thought... do you have the "Name AutoCorrect" feature turned on
in your database (Tools | Options | General)? Deselect the check in the
"Track name AutoCorrect info" box, then try your query activities again.
--

Ken Snell
<MS ACCESS MVP>

indeed, "S_Zones" = "Sony_Zones". Actually I didn't want to post the real
field names..... well done, isn't it ?

yes, I first build a select query and thne convert it to an update query.
Do
you think it might be the reason ?

My first inclination is that you've misspelled the name of a field in
either
the "S_Zones" (or is it Sony_Zones?) table or the RDW table for that
join?

Did you try building a select query for the three tables first, then
change
it to an update query?

--

Ken Snell
<MS ACCESS MVP>

ok, here is the SQL statment just before saving the query:

UPDATE (RDW INNER JOIN S_Zones ON (S_Zones.Prod_code =
RDW.Product_Code)
AND
(RDW.[Dest Station] = S_Zones.Station)) INNER JOIN Customer_Accounts ON
(RDW.[Shipper Account] = Customer_Accounts.Account_Number) AND
(S_Zones.Bill_Cntry = Customer_Accounts.Country) SET RDW.[Dest Zone] =
[Zone_Outb];

After saving, closing and opening the query, I get the msg "...can't
represent join expression S_Zones.prod=RDW.Product_code..."

UPDATE (RDW INNER JOIN Sony_Zones ON RDW.[Dest Station] =
Sony_Zones.Station) INNER JOIN Customer_Accounts ON
(Sony_Zones.Bill_Cntry
=
Customer_Accounts.Country) AND (RDW.[Shipper Account] =
Customer_Accounts.Account_Number) SET RDW.[Dest Zone] = [Zone_Outb];

Thx for your help already. I have the feeling that adding 3 tables in
the
query and then linking them together creates the prob.
Nicodemus

:

Post the SQL statement of one of the queries that is giving you this
problem. Often, this results from (1) the inability to link to the
tables
in
a backend file (another database file) that I mentioned earlier; or
(2)
using a "non-equijoin" (which it doesn't appear you're doing), or (3)
an
error in the name of a table and/or a field in a table, or (4) you
renamed a
table or field name and didn't revise the query to those new names, or
(5)
not sure why you're getting this error.

--

Ken Snell
<MS ACCESS MVP>

Hi Ken,

thx for your feeback, but I don't really understand what you mean
with
"backend file"... Do you mean a table linked to a file outside the
DB ?
Actually, both tables are in the DB.

Nicodemus

:

You can get this error if the tables are in a backend file and the
front
end
is not currently linked to the tables in that backend.

--

Ken Snell
<MS ACCESS MVP>

Hi all,

working in MSAccess2003 & XP.
I create joins between 2 or more tables in a query, I close and
save
this
query, and when I want to open it again, I get the msg "Access
can't
represent the join expression [table1].[field]=[table2].[field]".

That's weird as I know these fields and tables exist and could be
linked
without any problem in the past.

Any idea on what to do ?
Thx in advance

 

[Guest]

yes, this option was flagged !
I deselected it and got back to the queries, and the links are still alive !
It looks like this option somehow kills the wizard ?!

Thank you for your insight and you help.
Have a nice day,
Nicodemus

That should work in most situations.

Just a wild thought... do you have the "Name AutoCorrect" feature turned on
in your database (Tools | Options | General)? Deselect the check in the
"Track name AutoCorrect info" box, then try your query activities again.
--

Ken Snell
<MS ACCESS MVP>

indeed, "S_Zones" = "Sony_Zones". Actually I didn't want to post the real
field names..... well done, isn't it ?

yes, I first build a select query and thne convert it to an update query.
Do
you think it might be the reason ?

My first inclination is that you've misspelled the name of a field in
either
the "S_Zones" (or is it Sony_Zones?) table or the RDW table for that
join?

Did you try building a select query for the three tables first, then
change
it to an update query?

--

Ken Snell
<MS ACCESS MVP>

ok, here is the SQL statment just before saving the query:

UPDATE (RDW INNER JOIN S_Zones ON (S_Zones.Prod_code =
RDW.Product_Code)
AND
(RDW.[Dest Station] = S_Zones.Station)) INNER JOIN Customer_Accounts ON
(RDW.[Shipper Account] = Customer_Accounts.Account_Number) AND
(S_Zones.Bill_Cntry = Customer_Accounts.Country) SET RDW.[Dest Zone] =
[Zone_Outb];

After saving, closing and opening the query, I get the msg "...can't
represent join expression S_Zones.prod=RDW.Product_code..."

UPDATE (RDW INNER JOIN Sony_Zones ON RDW.[Dest Station] =
Sony_Zones.Station) INNER JOIN Customer_Accounts ON
(Sony_Zones.Bill_Cntry
=
Customer_Accounts.Country) AND (RDW.[Shipper Account] =
Customer_Accounts.Account_Number) SET RDW.[Dest Zone] = [Zone_Outb];

Thx for your help already. I have the feeling that adding 3 tables in
the
query and then linking them together creates the prob.
Nicodemus

:

Post the SQL statement of one of the queries that is giving you this
problem. Often, this results from (1) the inability to link to the
tables
in
a backend file (another database file) that I mentioned earlier; or
(2)
using a "non-equijoin" (which it doesn't appear you're doing), or (3)
an
error in the name of a table and/or a field in a table, or (4) you
renamed a
table or field name and didn't revise the query to those new names, or
(5)
not sure why you're getting this error.

--

Ken Snell
<MS ACCESS MVP>

Hi Ken,

thx for your feeback, but I don't really understand what you mean
with
"backend file"... Do you mean a table linked to a file outside the
DB ?
Actually, both tables are in the DB.

Nicodemus

:

You can get this error if the tables are in a backend file and the
front
end
is not currently linked to the tables in that backend.

--

Ken Snell
<MS ACCESS MVP>

Hi all,

working in MSAccess2003 & XP.
I create joins between 2 or more tables in a query, I close and
save
this
query, and when I want to open it again, I get the msg "Access
can't
represent the join expression [table1].[field]=[table2].[field]".

That's weird as I know these fields and tables exist and could be
linked
without any problem in the past.

Any idea on what to do ?
Thx in advance

 

[Ken Snell \(MVP\)]

Name AutoCorrect is notorious for causing all kinds of seemingly unrelated
problems. See Allen Browne's web page for a detailed listing:
http://allenbrowne.com/bug-03.html

 

[Guest]

thx for the info.
I hope this will help others having the same problem as mine