Relating a field in a table to another field in the same table

[Amer]

I have a table on employees details. In the table, a field representing the
ID number of the employee's manager is linked to the primary key field of a
copy of the same table (captioned: Managers).

However, whenever I create a new form or report based on a query combining
the two Employee IDs or relevant infomation I get a message like this:

The specified field '[tabContactInfo.' could refer to more than one
table listed in the FROM clause of your SQL statement.

How to solve this issue?

Thank you all...

 

[Allen Browne]

In query design view, you have 2 copies of the same table.
One is called tabContactInfo; the other is aliased as Managers.

Design the SQL statement so that only the Email field from one field is
returned, e.g. tabContactInfo.Email. If you need the Email field from the
other table, alias it by typing another name and a colon before the field
name in the Field row in query design, e.g.:
ManagerEmail: Managers.Email

Do not use the wildcard with both tables.

If you are still stuck, post a followup with the SQL statement (SQL View of
the query.)

 

[Amer]

That worked allright... thank you

In query design view, you have 2 copies of the same table.
One is called tabContactInfo; the other is aliased as Managers.

Design the SQL statement so that only the Email field from one field is
returned, e.g. tabContactInfo.Email. If you need the Email field from the
other table, alias it by typing another name and a colon before the field
name in the Field row in query design, e.g.:
ManagerEmail: Managers.Email

Do not use the wildcard with both tables.

If you are still stuck, post a followup with the SQL statement (SQL View of
the query.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a table on employees details. In the table, a field representing the
ID number of the employee's manager is linked to the primary key field of
a
copy of the same table (captioned: Managers).

However, whenever I create a new form or report based on a query combining
the two Employee IDs or relevant infomation I get a message like this:

The specified field '[tabContactInfo.' could refer to more than one
table listed in the FROM clause of your SQL statement.

How to solve this issue?

Thank you all...[/QUOTE]
[/QUOTE]