Last active
June 7, 2016 16:03
-
-
Save FlicAnderson/0a3ab3622c6902733f5b to your computer and use it in GitHub Desktop.
Query used in Padme Arabia (Access) to add FielRex query from script_dataGrabFullLatLonOrGazLatLon_Socotra.R to get around capacity issues with sqlQuery ODBC drivers
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
INSERT INTO FieldRexTemp ( recID, expdID, collector, collNumFull, lnamID, acceptDetAs, acceptDetNoAuth, detAs, lat1Dir, lat1Deg, lat1Min, lat1Sec, lat1Dec, AnyLat, lon1Dir, lon1Deg, lon1Min, lon1Sec, lon1Dec, AnyLon, coordSource, coordSourcePlus, coordAccuracy, coordAccuracyUnits, dateDD, dateMM, dateYYYY, fullLocation ) | |
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 '*Socotra:*' Or (Geog.fullName) Like '*Abd al Kuri:*' Or (Geog.fullName) Like '*Socotra Archipelago: Samha*' Or (Geog.fullName) Like '*Socotra Archipelago: Darsa*') AND ((LnSy.[Synonym of]) Is Null)) OR (((Fiel.[Longitude 1 Decimal]) Is Not Null) AND ((Geog.fullName) Like '*Socotra Archipelago: Socotra') AND ((LnSy.[Synonym of]) Is Null)) OR (((Fiel.[Longitude 1 Decimal]) Is Not Null) AND ((Geog.fullName) Like '*Socotra Archipelago') AND ((LnSy.[Synonym of]) Is Null)) | |
ORDER BY Team.[name for display]; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment