Type Mismatch Error depends on relation scheme appearance

[Mishanya]

1. I have a simple Add Query wich copies a data from tbl1 (wich is
linked-to-Excel table) to tbl2 (wich is local Access table). The tables are
related by Id field.
The query works fine, when I show only the updating table (tbl1) in the
design query view.
But when I show in the design query view the updated table as well (for
example, to check the relation type) and try to run the query, I get Mismath
Type error 3615.
The SQL gets this addition:
LEFT JOIN tbl2 ON tbl1.Id = tbl2.2
What's wrong?

2. If I change the relation between tables directly in the Query design
view, does it affect the main database table relation scheme and vice-versa -
if I change it in the main scheme, does it affect the existing query?

 

[Douglas J. Steele]

Assuming that tbl2 actually has fields named 1, 2, etc., try putting square
brackets around the field name:

LEFT JOIN tbl2 ON tbl1.Id = tbl2.[2]

 

[Mishanya]

Thank You, Douglas.
About the 2nd Question - I see that changing the relationship beetween the
tables directly from the Query Design View does not apply to the actual main
relationship scheme - is it build in pecularity or some misfunction?

Assuming that tbl2 actually has fields named 1, 2, etc., try putting square
brackets around the field name:

LEFT JOIN tbl2 ON tbl1.Id = tbl2.[2]


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

1. I have a simple Add Query wich copies a data from tbl1 (wich is
linked-to-Excel table) to tbl2 (wich is local Access table). The tables
are
related by Id field.
The query works fine, when I show only the updating table (tbl1) in the
design query view.
But when I show in the design query view the updated table as well (for
example, to check the relation type) and try to run the query, I get
Mismath
Type error 3615.
The SQL gets this addition:
LEFT JOIN tbl2 ON tbl1.Id = tbl2.2
What's wrong?

2. If I change the relation between tables directly in the Query design
view, does it affect the main database table relation scheme and
vice-versa -
if I change it in the main scheme, does it affect the existing query?

 

[cyrus05 via AccessMonster.com]

Changing the relation of tables in design queries does not have any effect in
the relationship of the tables of the database structure. Relationships of
the tables in the design veiw of query is part of query design, not the
database structure.

Thank You, Douglas.
About the 2nd Question - I see that changing the relationship beetween the
tables directly from the Query Design View does not apply to the actual main
relationship scheme - is it build in pecularity or some misfunction?

Assuming that tbl2 actually has fields named 1, 2, etc., try putting square
brackets around the field name:

[quoted text clipped - 19 lines]

 

[Mishanya]

Got it. Thanks, Syrus. Although, as I'm new to Access, it seems confusing: if
I set the relationship in the Query Design View, it applies only to this
particular query? So, it can work Left-join in the query, but Right-join or
no relation at all in the general relationships structure?

Changing the relation of tables in design queries does not have any effect in
the relationship of the tables of the database structure. Relationships of
the tables in the design veiw of query is part of query design, not the
database structure.

Thank You, Douglas.
About the 2nd Question - I see that changing the relationship beetween the
tables directly from the Query Design View does not apply to the actual main
relationship scheme - is it build in pecularity or some misfunction?

Assuming that tbl2 actually has fields named 1, 2, etc., try putting square
brackets around the field name:

[quoted text clipped - 19 lines]

vice-versa -
if I change it in the main scheme, does it affect the existing query?

 

[cyrus05 via AccessMonster.com]

Yes Mishanya, relationship in query desig are only limited to that particular
query and has no effect on your database structured relationship or
relationship of the same tables in any other query.

Got it. Thanks, Syrus. Although, as I'm new to Access, it seems confusing: if
I set the relationship in the Query Design View, it applies only to this
particular query? So, it can work Left-join in the query, but Right-join or
no relation at all in the general relationships structure?

Changing the relation of tables in design queries does not have any effect in
the relationship of the tables of the database structure. Relationships of

[quoted text clipped - 11 lines]