Results 1 to 3 of 3
  1. #1
    admessing's Avatar
    admessing is offline GIS DBase Tamer
    Windows XP Access 2007
    Join Date
    Dec 2011
    Location
    Northern CO
    Posts
    79

    Exclamation Combining Records

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

  2. #2
    admessing's Avatar
    admessing is offline GIS DBase Tamer
    Windows XP Access 2007
    Join Date
    Dec 2011
    Location
    Northern CO
    Posts
    79
    Anyone with ideas?

  3. #3
    admessing's Avatar
    admessing is offline GIS DBase Tamer
    Windows XP Access 2007
    Join Date
    Dec 2011
    Location
    Northern CO
    Posts
    79
    Does anyone have any ideas that may help? I'm kinda crunched for time as the deadline is fast approaching.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Help with Combining multiple Records
    By Jennivere in forum Queries
    Replies: 1
    Last Post: 11-28-2011, 08:05 PM
  2. Query for combining records
    By alpinegroove in forum Queries
    Replies: 6
    Last Post: 01-28-2011, 07:29 AM
  3. Combining / Merging Records
    By alpinegroove in forum Access
    Replies: 8
    Last Post: 01-27-2011, 09:43 AM
  4. combining multiple records into one record
    By RedGoneWILD in forum Queries
    Replies: 5
    Last Post: 07-28-2010, 11:19 AM
  5. combining field from two records into one
    By RedGoneWILD in forum Queries
    Replies: 8
    Last Post: 07-13-2010, 09:47 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums