PadmeFieldRexTempOmanQuery.SQL

SELECT 'F-' & Fiel.id AS recID, Fiel.Expedition AS expdID, Team.[name for display] AS collector, Fiel.[Collector Number] AS collNumFull, LnSy.id AS lnamID, LnSy.[Full Name] AS acceptDetAs, LnSy.sortName AS acceptDetNoAuth, Lnam.[Full Name] AS detAs, Fiel.[Latitude 1 Direction] AS lat1Dir, Fiel.[Latitude 1 Degrees] AS lat1Deg, Fiel.[Latitude 1 Minutes] AS lat1Min, Fiel.[Latitude 1 Seconds] AS lat1Sec, Fiel.[Latitude 1 Decimal] AS lat1Dec, IIf(IsNull(Fiel.[Latitude 1 Decimal]),Geog.[Latitude 1 Decimal],Fiel.[Latitude 1 Decimal]) AS anyLat, Fiel.[Longitude 1 Direction] AS lon1Dir, Fiel.[Longitude 1 Degrees] AS lon1Deg, Fiel.[Longitude 1 Minutes] AS lon1Min, Fiel.[Longitude 1 Seconds] AS lon1Sec, Fiel.[Longitude 1 Decimal] AS lon1Dec, IIf(IsNull(Fiel.[Longitude 1 Decimal]),Geog.[Longitude 1 Decimal],Fiel.[Longitude 1 Decimal]) AS anyLon, Fiel.coordinateSource AS coordSource, IIf(IsNull(Fiel.[Latitude 1 Decimal]),'Gazetteer','Record') AS coordSourcePlus, Fiel.coordinateAccuracy AS coordAccuracy, Fiel.coordinateAccuracyUnits AS coordAccuracyUnits, Fiel.[Date 1 Days] AS dateDD, Fiel.[Date 1 Months] AS dateMM, Fiel.[Date 1 Years] AS dateYYYY, Geog.fullName AS fullLocation
FROM (((([Field notes] AS Fiel LEFT JOIN Geography AS Geog ON Fiel.Locality = Geog.ID) LEFT JOIN Teams AS Team ON Fiel.[Collector Key] = Team.id) LEFT JOIN [Latin Names] AS Lnam ON Fiel.determination = Lnam.id) LEFT JOIN [Synonyms tree] AS Snym ON Lnam.id = Snym.member) LEFT JOIN [Latin Names] AS LnSy ON Snym.[member of] = LnSy.id
WHERE (((Geog.fullName) Like '*Oman*') AND ((LnSy.[Synonym of]) Is Null))
ORDER BY Team.[name for display]