Linking to Same Table Twice

[lloydmav]

Hi,

Can anyone help me solve this issue.

I have a main table lets call it [product] which has a [created by] field and also a [modified by] field. I need to link both these fields to the same [employee] table and returns the surname for each.

I want to return all the records from the [product] table, and only the surname for a [created by] record when a there is a created by match, and also a surname for a modified by record when there is a match. Not all records have been modified and not employees still exist.

I have tried linking to the same table in a query twice, but that only brings back the created by surname for those records which also have a modified by match.

So I tried adding the same table into the query twice and then linking each one seperately. This will only work if I try to bring back only those records which match in both tables, but if I tell it to bring back all records from product table, and only those from other tables that match I get this message

"ambiguous column naming in select list #960"

This is really baffling me

Thanks

 

[pbaldy]

You can link the same table twice (the second instance will be aliased). Based on your description you want to modify both joins to be left joins (in design view, edit the join and choose "Include all records from Product...").

 

[lloydmav]

Hi, thanks for the reply, I tried that but I get the error mentioned above. I think what its saying is the field name in the first version of the table is ambiguous with the name of the field in the alias table, or something like that

 

[namliam]

If you have 2 tables with simular column names, you have to disambiguate them.

In this case you have twice the same table but the first table will be "TableName" the second table will have an allias, something like "Tablename_1" which is the Access default but you can change it.

Now if you want to select the name from either one or both tables, you have to disambiguate that name. You cannot just use "Name" because Access can fetch it from two different tables, so you have to tell access what table to fetch it from (that is what this error is about).

So add the tablename in front of the field you want to get, like so: Tablename.Name

Good Luck!