Okay folks....
Here is what I need to do:
First, determine if there are sets of records that share GPS coordinates, but have different years. Those records that have commonality of coordinates need to be combined into a single row/record. This creates duplicates of coordinates (some are combined, others are not and based upon the original data).
Second, I need a way to deal with the duplicates, yet include records for which there is currently only one year entry.
Third, i need to create a table from the resulting combined records without duplicates, but including the records for entries that only have a single year.
Any thoughts?
Here are my queries:
To create Photo_Link from data entry -
Code:
SELECT
Photo_Files.Unique_ID,
Project.Project_Name,
Project.Project_Area,
Project.Photo_Year,
Spatial_Locations.FSVeg_Location,
Spatial_Locations.FSVeg_Stand_No,
Spatial_Locations.Easting_UTM,
Spatial_Locations.Northing_UTM,
"C:\Documents and Settings\admessing\Desktop\Archive_Files\"
& [Project]![Photo_Year] & "\" & [Photo_Files]![File_Base]
& "N.jpg" AS IMG_North,
"C:\Documents and Settings\admessing\Desktop\Archive_Files\"
& [Project]![Photo_Year] & "\" & [Photo_Files]![File_Base]
& "E.jpg" AS IMG_East,
"C:\Documents and Settings\admessing\Desktop\Archive_Files\"
& [Project]![Photo_Year] & "\" & [Photo_Files]![File_Base]
& "S.jpg" AS IMG_South,
"C:\Documents and Settings\admessing\Desktop\Archive_Files\"
& [Project]![Photo_Year] & "\" & [Photo_Files]![File_Base]
& "W.jpg" AS IMG_West INTO Photo_Link
FROM (Project INNER JOIN Photo_Files ON Project.Unique_ID = Photo_Files.Unique_ID) INNER JOIN Spatial_Locations ON Project.Unique_ID = Spatial_Locations.Unique_ID
ORDER BY Photo_Files.Unique_ID;
To combine records based upon coordinates (creates duplicates) (see image ScreenshotQueryCombine) -
Code:
SELECT
Photo_Link.Photo_Year,
Photo_Link.FSVeg_Location,
Photo_Link.Easting_UTM,
Photo_Link.Northing_UTM,
Photo_Link.FSVeg_Stand_No,
Photo_Link.IMG_North,
Photo_Link.IMG_East,
Photo_Link.IMG_South,
Photo_Link.IMG_West,
IIf([Dupe].[Easting_UTM]=[Photo_Link].[Easting_UTM]
And [Dupe].[Northing_UTM]=[Photo_Link].[Northing_UTM]
And [Dupe].[Photo_Year] > [Photo_Link].[Photo_Year],[Dupe].[Photo_Year]) AS Photo_Year2,
IIf([Photo_Year2] Is Not Null,[Dupe].[IMG_North]) AS IMG_North2,
IIf([Dupe].[IMG_North] = [Dupe].[IMG_North], [Dupe].[IMG_East]) AS IMG_East2,
IIf([Dupe].[IMG_East] = [Dupe].[IMG_East],[Dupe].[IMG_South]) AS IMG_South2,
IIf([Dupe].[IMG_South] = [Dupe].[IMG_South], [Dupe].[IMG_West]) AS IMG_West2
FROM Photo_Link LEFT JOIN Dupe ON Photo_Link.Easting_UTM = Dupe.Easting_UTM;
To deal with duplicates, but results in loss of records with only a single year -
Code:
SELECT [Combine Records].FSVeg_Location,
[Combine Records].FSVeg_Stand_No,
[Combine Records].Easting_UTM,
[Combine Records].Northing_UTM,
[Combine Records].Photo_Year,
[Combine Records].IMG_North,
[Combine Records].IMG_East,
[Combine Records].IMG_South,
[Combine Records].IMG_West,
[Combine Records].Photo_Year2,
[Combine Records].IMG_North2,
[Combine Records].IMG_East2,
[Combine Records].IMG_South2,
[Combine Records].IMG_West2
INTO Photo_Link_Combined
FROM [Combine Records]
WHERE ((([Combine Records].IMG_North2) Is Not Null));