Ambiguous Outer Joins

[Emmy]

Could someone please give an example of how to create a separate query that performs a first join, and then include that query in a SQL statement? I followed the example in the help menu, but it doesn't seem to work?
I don't really know how to use SQL...is there a way to modify this using Design View in MS Access? Thanks in advance. Below is the SQL view of my query:

INSERT INTO Attribute ([Measurement Type Code], [Created By], [Created On], Attribute)
SELECT [Measurement Type].[Measurement Type Code], 675 AS Expr1, Now() AS Expr2, Sheet1.Attribute
FROM (((Sheet1 LEFT JOIN [Measurement Type] ON Sheet1.[Measurement Type] = [Measurement Type].[Measurement Type]) LEFT JOIN [Attribute Type] ON Sheet1.[Attribute Type] = [Attribute Type].[Attribute Type]) LEFT JOIN [Object] ON Sheet1.Object = Object.Object) INNER JOIN Attribute ON (Object.Object = Attribute.[Object Code]) AND ([Attribute Type].[Attribute Type] = Attribute.[Attribute Type Code]);

 

[anna the third]

Hi,

The way I like to work out these queries is as follows

1.) create the select query using the interface of Access
2.) goto the sql view of the select query and add the following

SELECT * FROM
("your previous created sql") AS TEST

3.) run the query (output = previous output)
4.) open the query in design view (the TEST query is now shown as a table in the above screen)
5.) change the query to an append query (Access asks you for a table name)
6.) connect the TEST fields to the corresponding fields of the table you want to append the records to

This way you are least likely to make mistakes, in my humble opinion

Let me know if it helps

anna

[This message has been edited by anna the third (edited 04-05-2002).]

 

[Pat Hartman]

The problem with your SQL as written is that an Inner join may not follow a Left join. So, if the Inner Join can be logically changed to a Left Join, the error will go away. If you cannot logically change this join, create a query that just does an inner join of these last two tables. Then change this append query and replace the last two tables with a left join to the new query you just built.