Query issues

[admessing]

Howdy folks....

Okay, I have asked this before, but as yet have not recieved an answer.

I have a photo archive DB, it is used to track changes in the ecosystem over time for a referenced GPS location.

There are three main tables with 1-to-1 relationships on ID field(Project is main):

Project: ID, Project Area, Project Name, Year
Photo_Files: ID, File Base
Spatial_Locs: ID, X, Y, Lat, Lon, Location Code, Stand Number

There is an entry form to speed data entry that is linked to all the main tables.



I have a query that uses the information in the Photo_Files table to create the four image file names based upon the File_Base (ie:...N.jpg, ...S.jpg..etc). This creates a new table that combines most of the information from the three main tables and the new file names - Photo_Link.

I have a second query that compares the data in Photo_Link to a duplicate of Photo_Link (Dupe) to determine if any of the GPS coordinates have multiple photo series.

The third query, which is where the issue lies, is used to remove the duplicate records from the second query and create a table called Combined_Photo_Link. The problem that I face is that not all points will have a secondary set of photos.

How can I work around this issue?

[aytee111]

Show us your query

[admessing]

Here you go:

#1) QUERY-CREATE TABLE: Photo_Link from Form Entries:

 
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;

-----------------------------------

#2) Query: Combine Years (creates dups):

 
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;

-----------------------------------
#3) QUERY-CREATE TABLE: Combine Multiple Records to Single Point (No Dups):

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

[aytee111]

I'm sorry, I'm still trying to understand. Can you attach your database (or at least the bits that are pertinent).

[admessing]

Give me a few moments....dealing with a printer issue in another department

[admessing]

Well...I would upload it for you, but am having issues with my secured network allowing it. Will try again when I chat with IT and figure out the issue.

[admessing]

Unable to post DB...getting error from accessforums website. Unknown issue. Will see if I can post some screenshots so you get an idea.

[aytee111]

I'm struggling with the complexity of it.

User enters project and year. Check to see if there are 4 pictures. If not add them.

What am I missing?

[admessing]

Not quite.

In the entry form, they enter the ID (it is not a auto number), project name, project area, year, Location, stand number, GPS locations (in 2 formats...4 total fields), and the common file base for each series. The file base is like this:

2011CP0604059601

The file output query (Q#1) takes the base and appends the file path, direction and file extension (Example: C:\Documents\Archive\2011\2011CP0604059601N.jpg)

The second query simply compares the GPS coordinates of each entry and the year of the photo series, then combines them if there are multiple years.

[admessing]

Okay....

Had to chop out all the geodatabase stuff that wouldn't matter to you anyway. Here is the most basic form of the DB. You will just need to add the second and third queries to it. Also, to get the last 2 to run, you will need to dummy up the main tables with more data. Just remember that the last 2 queries are based upon the coordinates (Northing_UTM and Easting_UTM), so that is the data that is most important.

[aytee111]

So far:

1) Is the table Dupe just a copy of Photo_Link after the first query has run?

2) This from your second query "IIf([Dupe].[IMG_East] = [Dupe].[IMG_East],[Dupe].[IMG_South]) AS IMG_South2" -
a) the statement will always be true so why put it in
b) why use East and then populate South

[admessing]

1) Is the table Dupe just a copy of Photo_Link after the first query has run?

A] Yes it is simply just a copy of Photo_Link

2) This from your second query "IIf([Dupe].[IMG_East] = [Dupe].[IMG_East],[Dupe].[IMG_South]) AS IMG_South2" -

a) the statement will always be true so why put it in

A] Hmm....I can't recall why.....{EDIT}Actually, it is because it will not always be true....IMG_East may be "No Data"

b) why use East and then populate South

A] It goes back to if there is a IMG_North2....if that is true, then it will populate the others, otherwise it is to return "No Data" or some such...and the format for the archive has to be in the order of N, E, S, W to maintain collection guidelines as set forth by the ecosystem managers in D.C.

[aytee111]

A] Hmm....I can't recall why.
Not good enough!! Did you take over the database from someone else?

Instead of looking at what you have done, let's look at it from the other side - what you are trying to achieve, in simple terms.

You want to track changes in the ecosystem over time for a referenced GPS location. For each GPS location there is a year and 4 photos. Let's forget project and other information for now as we can hook into that later based on the GPS location. Is this the bottom line? So the final result of the three queries, ignoring their inner workings for now, is ..... fill in the blanks.

[admessing]

A] Hmm....I can't recall why.
Not good enough!! Did you take over the database from someone else?

In a sense yes I did. I have had to rebuild one that was started in 2005, but was experiencing some issues after the main server had a surge.

Instead of looking at what you have done, let's look at it from the other side - what you are trying to achieve, in simple terms.

You want to track changes in the ecosystem over time for a referenced GPS location. For each GPS location there is a year and 4 photos.

Partially true. In the chopped down version that you have, it only shows a single record for each GPS location, but in reality, there are likely 2 records that share the same GPS location, but differing year and photos. And the number of records that may share a GPS location will increase as years pass.

Let's forget project and other information for now as we can hook into that later based on the GPS location. Is this the bottom line? So the final result of the three queries, ignoring their inner workings for now, is ..... fill in the blanks.

The final result of the three queries should be a table that joins all the spatial information, photo files information, and project information together based upon the GPS coordinates. This table needs to reflect BOTH the locations that only have a single set of photos AND the locations that have mulptiples. BUT, it cannot contain any duplicate locations.

[admessing]

Once all is said and done, there will be over 56000 individual photo records, with roughly 2/3 of them sharing GPS coordinates. And this will occur by Feb 1, 2012.