Renaming or Dropping duplicate Fields in Psuedo Full Outer Join.

[Sanguineus]

Hi All - hoping for a little help as banging my head against a brick wall at the moment.



I'm trying to 'Full Outer Join' several tables together but do so in way allows me to do next to nothing if other fields are added to the tables at a later date. (the extra data would not be duplicated in any other table)

	SELECT *
	FROM Table_A
	LEFT JOIN Table_B
	ON Table_A.[FieldName_1] = Table_B.[FieldName_2]
	UNION
	SELECT *
	FROM Table_A
	RIGHT JOIN Table_B
	ON Table_A.[FieldName_1] = Table_B.[FieldName_2]

Works absolutely fine - When I then try to Left/Right and UNION this to a 3rd table I get the error Table_A.[Fieldname_1] could refer to more than one table. which makes absolute sense as I have one of both the left and right joins. Is there a way to join on the first instance or better yet automatically drop the duplicate field on subsequent left/right/unions?

For refence the tables I am looking to join are currently made up of the following fields.

Table_1
ACT-REF
Field1
Field2
Field4
Field5

Table_2
ACT-REF
PERSON-REF

Table_3
ACT-REF
PLACE-REF
Field1
Field2
Field3

Table_4
PERSON-REF
Field1
Field2
Field3
Field4

TABLE_5
PLACE-REF
Field1
Field2
Field3
Field4


The Fieldx references contain data unique to the table and the field name and data are not duplicated in any other table. I am looking at full outer joins (simulated) as not ever PLACE, ACT, PERSON REf will ahev data for every other refence.

TIA!

[Micron]

You really have multiple fields named "field1"? Must be because this will be a one time thing and you are trying to mash it all into one table. In that case, design a table and use several append queries. If this is to be a common exercise, then you have real serious design issues, part of which would be using special characters in names (-) and possibly using the same names over and over again.

[Sanguineus]

Sorry fieldX was a placeholder for a field unique to that table - really should have been a bit more specific there. I listed the PK/FK specifically and then left the rest generic again my apologies.

I decided upon creating a singular table of [ACT-REF] via UNION and then LEFT JOIN 'ing the other tables - based upon my data (which is actually half the battle. understand the data and the rest is far easier) i'm getting the required results...... I think.