I’ve looked at Full Join and found this suggestion for a way to build the SQL to do what I want:
SELECT * FROM TableA
FULL OUTER JOIN TableB
ON TableA.name = TableB.name
(http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/)
However Base rewrites that SQL sequence and then says it can’t find the table.
What I am trying to do is to merge two tables of data that share the same part number - so that the result is a new table that has all the part numbers from the old tables in order with missing numbers from either table inserted in sequence in the new table.
I am not using a Primary Key for either table.
My code:
SELECT * FROM “TABLEA”
FULL JOIN “TABLEB”
ON “TABLEA”.“Part Number” = “TABLEB”.“ArtNr”
Base converts to:
SELECT “TABLEB”., “TABLEA”. FROM { OJ “TABLEA” FULL OUTER JOIN “TABLEB” ON “TABLEA”.“Part Number” = “TABLEB”.“ArtNr” }
Get the error message:
The data content could not be loaded.
Table not found: TABLE B in statement [SELECT…
Both tables show up in the design page with the link which does say it is a “Full (outer) Join”.
I’m missing something obvious…aren’t I?
To have some good idea…please watch the video
www.TheFrugalComputerGuy.com
Group 13 / Video 34 (Creating A Relationship)
This might give you some trouble shooting ideas.
Good Luck.
Not sure what you mean by ‘Base rewrites that SQL sequence’?
Both tables show up in the design page
So it looks like you are using Create Query in Design View to make your query? Maybe the Create Query in Design View tool is just not doing a very good job at writing a proper SQL query? Have you simply tried to use Create Query in SQL View to make your query to see if that works?