Query used in Padme Arabia (Access) to add FielRex query from script_dataGrabFullLatLonOrGazLatLon_Socotra.R to get around capacity issues with sqlQuery ODBC drivers

PadmeFielRexCapacityFixQuery.SQL

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];