Delete and/or Select Distinct records query

[admessing]

Hello all-

I am having a bit of trouble writing a query that will return only those records in a table that are needed. The table is created via another query that combines records together based upon GPS coordinates (2 fields). This issue is....it also returns rows that are not combined (ie. the first year of photos only). I need to write a query that will (I think)return the first row of each GPS location pair (and all the sundry info columns).

My table is called Combined_Records

Fields to compare are:
Easting_UTM
Northing_UTM

Columns to return in query:
Location
Stand
Easting_UTM
Northing_UTM


Photo_Year
North
East
South
West
Photo_Year2
North2
East2
South2
West2

Any thoughts? The query needs to either create a new table for export, or just modify the Combined_Records table.

Once this works....I need to automate the queries, or combine all of them into one so that my users don't forget a step.

[admessing]

Here is a screen shot in document form of what my Combine_Records1 table looks like. The yellow highlighted rows are what I need to remove, but in some cases, there will only be one row for a particular GPS (UTM fields) pair. I need to remove the duplicates, but retain any records with a single entry, so a NOT NULL query will not work since the single records rows will have null fields.

[Robeen]

Have you considered writing a 'Select DISTINCT . . .' query?

Will the rows that have the duplicate GPS pairs be identical?

You can try the MS Access Query Wizard and then use the 'Find Duplicates Query Wizard'.
Have you considered that already?

[admessing]

Have you considered writing a 'Select DISTINCT . . .' query?

Yes, but I am not particularly familiar with them and I don't have a good reference for how to write one (other than the folks here) and my DB books are about as helpful as a rock .

Will the rows that have the duplicate GPS pairs be identical?

If you look at the attachment for my last post you will see that the yellow highlighted rows share the same GPS coordinates with one that is adjacent (non-highlighted). The only difference between the rows is that there will be one that is complete and having multiple photo years and image paths, and 1-2 others with only a single year and image paths, but matching GPS coordinates. BUT....there may also be rows that are unique since they do not have a GPS match (yet).

You can try the MS Access Query Wizard and then use the 'Find Duplicates Query Wizard'.
Have you considered that already?

I tried this option and it cuts out most of the information that I need included in the output table.

[admessing]

Suggestions?

[Robeen]

I looked at your Word attachment again.
Do all the rows that you want excluded have 'No Data' in the Photo_Year2 field?

[admessing]

For the most part yes. But, there will be some records that only have a single photo series, so in this case, there will be "No_Data" in the Photo_Year2 field. Basically, I need the query to grab all of the records with multi-photo year data, as well as the records that have no match for additional years, while getting rid of the records that have correlating (duplicate) coordinates.

The DB is set up so that photo series are entered by coordinate and year...then there are make table queries that add in the file path for the photos, and then a query to combine the records based on matching coordinates....the final query needs to get rid of the duplicates and select the distinct records from the second query.

[admessing]

Anyone with ideas?

[Robeen]

Is there any chance you can post your database here?

I think what you're trying to do is not difficult. It's just that I am having a hard time understanding exactly what you need - even with the word doc that you attached.

You can always strip the database down to just the bare essentials before you post it. It will be easier if I can see all the data and work with it hands on.

[admessing]

Here is the DB file...stripped down to it's test data. You will see that there are 3 linked tables: Project_MOD, Spatial_Locations_MOD, and Photo_Files_MOD.

Project_MOD: Provides reference for the project name/type and area

Spatial_Locations_MOD: Exact location in the forest (all of the fields are required....this data is used to link to our mainframe server and other DBs)

Photo_Files_MOD: Provides the common portion of the file name (the first query, "Photo_Link Query" builds the file paths to the photos), used to make data entry easier on my users.

Queries:

1. Photo_Link Query_MOD (make table): Builds the file paths and combines the 3 main tables into one (this is necessary since the resulting table can/will be used with mapping software and needs all the info together).
2. Combine_Records Query : Used to combine the table rows from the first query based upon matching the Easting_UTM and Northing_UTM (GPS coordinates). This query is what gives the duplicate rows.
3. Combine Records Part 2_MOD : This is my original attempt at getting rid of the duplicates. The issue here deals with records that may only have one photo series/year associated with the coordinates....they get left out .

The "Dupe_MOD" table is used as part of the second query...right now it must be generated manually (it is a copy of the Photo_Link table that is created in the first query). (The MOD portion of the table/query name denotes that it is test data)

Hope you can make sense of what I have here. In the main DB there is also an entry form and about 35 tables that are part of the standard format for a GIS related DB (personal geodatabase), but I stripped them out of this copy so that I could meet the size requirements for upload here. These tables do not effect any of the queries or the main data...they are just in the main DB so that our mapping software can read the DB tables.

[Robeen]

Just to confirm - Is All the data you need in the Photo_Link_MOD table???

I ran your Make Table query to create the Photo_Link_MOD table - but was not able to run the other Make Table query because "the input table or query DUP_MOD" was not found.

DUP_MOD is referenced in your 'Combine Records Query' - so I can't run it.

[Robeen]

Ok . . . sorry - I just created the Dupe_MOD table per your instructions - and I am able to run your query now.

[Robeen]

Still trying to understand why your query has to have two copies of the same data.

The two tables in your query [Photo_Link_MOD, and Dupe_MOD] are really the same table with the same data but with different names - right?

Can you explain that to me, please?
I have not ever come across the need to do something like this - so you'll need to explain the reasoning on this to me.

[admessing]

Still trying to understand why your query has to have two copies of the same data.

The two tables in your query [Photo_Link_MOD, and Dupe_MOD] are really the same table with the same data but with different names - right?

Can you explain that to me, please?
I have not ever come across the need to do something like this - so you'll need to explain the reasoning on this to me.

This is done since the query needs to compare the data from the tables to be able to combine them. For example:

Record 2 (from Photo_Link_MOD) coordinates match those of records 2 (same record), and record 33 from Dupe_MOD, so the query combines the information into a new single record. BUT, the problem is it will also list record 1 and 33 separately...this is the problem. I need that combined record, but not the other 2 since they are essentially duplicates. But as I have said before, there will be some records that do not have matching coordinates in other records....these need to be preserved....hence why just using a "null/no data" query will not work.

If you know a better and simpler way to do this I am open to it....I would also love to combine every one of the queries into one, or automate them all upon closing of the entry form (once they work)...but VB coding is not a strength of mine...I am an XSL/HTML coder not a VBA scripter....

[Robeen]

I don't think you need to have two identical tables in your database.
You can achieve what you need by doing multiple Queries on the same Table - but we can get to that later.

First . . . using only the Photo_Link_MOD table . . .

1. When I look at your data as you specified, I see that in Photo_Link_MOD - Unique_IDs 2 & 32 [not 2 & 33 as you said] have identical long/lat Coordinates.

2. I created a query on Photo_Link_MOD and put the Easting and Northing coordinate fields in the first two columns of the query [to make it easier for me to see what was going on] & then sorted the query on Easting_UTM - Ascending.

Except for 'Unique_ID' 1 - every other Unique_ID has an exact match in those two coordinate fields.

You might call them 'duplicate' rows - except that there are fields in the row that do NOT have matching data - Photo_Year, and the 4 IMG. . . fields [that include the photo year in the file paths].

Now that I have taken a closer look at your data and know what each record holds, please tell me again - what do you need your query to do?

If you leave out the Unique_ID field, the Photo_Year field and the Image path fields, - you can do a Group By and end up with a single row for each distinct pair of coordinates - like this sql:

SELECT Photo_Link_MOD.Easting_UTM, Photo_Link_MOD.Northing_UTM, Photo_Link_MOD.Project_Name, Photo_Link_MOD.Project_Area, Photo_Link_MOD.FSVeg_Location, Photo_Link_MOD.FSVeg_Stand_No
FROM Photo_Link_MOD
GROUP BY Photo_Link_MOD.Easting_UTM, Photo_Link_MOD.Northing_UTM, Photo_Link_MOD.Project_Name, Photo_Link_MOD.Project_Area, Photo_Link_MOD.FSVeg_Location, Photo_Link_MOD.FSVeg_Stand_No
ORDER BY Photo_Link_MOD.Easting_UTM;

This will condense your 61 records into 31 rows in which no two rows have the same Easting and Northing coordinate pair.

Paste that SQL into a new query in your db and let me know if that gets you PART of the way to where you want to be.

Then - tell me what needs to happen next.