Colin For 8 --SQL8 SELECT t.* FROM [FloristicListPlots] AS t RIGHT JOIN [Plots2Select] AS u ON LCase(t.[Plot Name]) = LCase(u.[Plot]) or for 9 --SQL9 SELECT t.* FROM [FloristicListPlots] AS t RIGHT JOIN [Plots2Select] AS u ON StringToLowerCase(t.[Plot Name]) = StringToLowerCase(u.[Plot]) If you don't care about case (so that different species can be differentiated by differing case, as for genes I think) then omit the conversion functions. Why RIGHT JOIN? It depends what you want. A right join excludes unmatched records on the left (the first table), but lists all records on the right (second table) even if unmatched. Use LEFT JOIN if you want to see all records from the first table, even if unmatched. Use FULL JOIN to see all records from both tables, regardless of whether they are matched. To include all rows from both tables, whether matched or not, use INNER JOIN. Unmatched rows (if listed) are paired with blanks (NULL). This is all informative--or can be. I would say, never use a comma join (as you did). It's nobody's friend. Instead, you put both tables side by side in your head, then you apply the join condition, then you list the results. There's no magic, it's just one step after the other. Tim
|