SQL problem

[John Sh]

I have the following SQL string that has an error in the JOIN statement that I cannot fix.
It's probably very simple but my SQL knowledge is insufficient to sort it out, try as I may.
In this iteration, sTable = "Barry Collier Collection"

sQry = "INSERT INTO NoMatch ( Family, Genus, Accession, Collector, CollNumber, tFamily )" & " " & _
                           "SELECT Family, Genus, AccessionNumber, Collector, CollectorNo, Bad_Family.Family" & " " & _
                           "FROM [" & sTable & "] LEFT JOIN Taxon ON " & sTable & " .Family = Taxon.family"

 

[plog]

Why did you bracket the first instance of sTable but not the second?

 

[John Sh]

Why did you bracket the first instance of sTable but not the second?

If I don't bracket the first instance, I get a different error, so it works when bracketed.
If I do bracket the second instance I get another error, so I get the correct string, "Barry Collier Collection.Family" With the code as shown.
What I need to know is what, or where, is the missing operator?

 

[plog]

There is no missing operator, its the spaces in the table name. It either needs to be bracketed in the second instance, or assigned an alias in the FROM and the alias used in the JOIN.

 

[plog]

I'm looking closer now. Have you given us the full SQL? Usually in an unmatched query there is a WHERE dealing with NULL values in the LEFT JOIN source. Also...

...Bad_Family.Family"

Is there another JOIN? Where is Bad_Family? It isn't in the FROM nor JOIN you have given us.

 

[The_Doc_Man]

You have an extra space in the ON clause ... "ON " & sTable & " .Family = Taxon.family" - there should be no space in front of the first .Family - and I'm concerned that the error message truncated the second .Family reference as well, which means there MIGHT be a hidden character or else there is a special character in one of the substitutions.

EDIT by The_Doc_Man after a 2nd look.

 

[John Sh]

I have used the query designer to create a query that achieves the desired result and then taken the SQL from that query,

[CODE
strSQL = ]INSERT INTO nomatch ( Family, Genus, Accession, Collector )
SELECT Main.Family, Main.Genus, Main.AccessionNumber, Main.Collector
FROM (Main LEFT JOIN Taxon ON Main.Family = Taxon.family) LEFT JOIN Bad_Family ON Main.Family = Bad_Family.Family
WHERE (((Main.Family)>"") AND ((Bad_Family.Family) Is Null) AND ((Taxon.family) Is Null));
[/CODE]

Without getting into the "Normalised" argument,
what I need is all instances of the actual table name "Main" to be the variable "sTable", allowing the SQL string to be used for multiple tables with a similar construct.
As I understand it the "SELECT" statement can be "Family, Genus, AccessionNumber, Collector" without reference to the actual table name.

 

[plog]

As I understand it the "SELECT" statement can be "Family, Genus, AccessionNumber, Collector" without reference to the actual table name.

It can be, but not in this instance. You've got multiple [Family] and [Genus] fields in the underlying datasources so the SQL will throw an ambigous field error without the table prefix. Of course, that would be an error you'd get once you fix this syntax one.

I suggest an alias for the sTable in your initial post and update the other clauses appropriately:

sQry = "INSERT INTO NoMatch ( Family, Genus, Accession, Collector, CollNumber, tFamily )" & " " & _
                           "SELECT A.Family, A.Genus, A.AccessionNumber, A.Collector, A.CollectorNo, Bad_Family.Family" & " " & _
                           "FROM [" & sTable & "] AS A LEFT JOIN Taxon ON A.Family = Taxon.family"

 

[John Sh]

Thank you Plog.
I wasn't aware of the alias thing but it certainly makes life easy
My final code is below.

sQry = "INSERT INTO NoMatch ( Family, Genus, Accession, Collector, CollNumber, tFamily )" & " " & _
                           "SELECT A.Family, A.Genus, A.AccessionNumber, A.Collector, A.CollectorNo, Bad_Family.Family" & " " & _
                           "FROM [" & sTable & "] AS A LEFT JOIN Taxon ON A.Family = Taxon.family" & " " & _
                           "WHERE A.Family <>'' AND Bad_Family.Family Is Null AND Taxon.family Is Null"

Many thanks.
John