Delete and/or Select Distinct records query

[admessing]

Robeen-

Yes...it gets me part way there. But, the information such as Photo_Year, and the images are missing. These are crucial for what I need to do.

Basically....the final table (which is used to create the map layer) must contain the coordinates, location, stand no, photo year(s), and image path(s)...grouped by the coordinates. The photo years should be listed oldest then newest (ie photo_year = 2007, photo_year2 = 2012...for each coordinate set).

[Robeen]

I thought that was what you would say.

Here's the deal:

You can get the rows grouped by distinct coordinates - BUT - as soon as you introduce data that doesn't match in BOTH the rows for each distinct set of coordinates [Unique_ID, PhotoYear, IMG_East, IMG_South . . .] - you will get a separate row for each set of different values.
That's how queries work.

Tell me if I've got this correct:
You want what my query gives you . . .
AND . . .
you want ALL the data that differs in two records where the coordinates Match - to be forced into the SAME row of data . . .

So that . . . you end up with all the matching data:
[Easting, Northing . . .]
AND
the NOT matching data
[PhotoYear_1, IMG_NORTH_1, IMG_SOUTH_1..., PhotoYear_2, IMG_NORTH_2, IMG_SOUTH_2 . . .] data for those two coordinates
. . . in the SAME row.

Is that correct?

[admessing]

Correct.

Basically the archive is a repository for all the photos info to be dumped and sorted into something useable. Since our mapping software will only display a single row of information for a unique coordinate set...we have to force all the photos into the same row to create the map layer and have it work.

I have considered using a different structure...ie...they look up the coordinates and just enter new photos, but there are more than 56000 current records (no dups) and growing...so this option is out...hence the structure it has now. This archive DB, once I am done dumping all the data into it will contain approximately 250,000 records by the end of next year....some will be combined by coordinates...others will be unique. And my end-users are not exactly the DB type...they barely know Excel....so simple is how it has to be.

[Robeen]

This will be a little tricky.
1.
You MIGHT be able to do it using Queries alone - it depends.
How many 'duplicate' rows MIGHT there be for a certain set of coordiantes?
Is this going to be a finite and predictable number?
Eg: will there always be NO MORE THAN 2 rows that have matching coordinates?
Or is it possible that there could be SEVERAL [unpredictable number of] rows that have matching coordinates?

2.
If the number of rows with matching coordinates is not predictable:
You might have to resort to writing VBA - and creating the final Table you need IN the VBA Code - by writing a query that sorts the rows of your Photo_Link_MOD table by Easting . . . and then looping through each row.
Read one row into variables
Move to next row
If the coordinates are the same - add the new Photo Year & Image Path values to variables along with the variables you stored while reading teh first row.
Move to the Next Row.
If the coordinates are different, write the values you stored for the preovious two rows and then store new variables for the new set of coordinates. . . .

and so on.

[admessing]

This will be a little tricky.
1.
You MIGHT be able to do it using Queries alone - it depends.
How many 'duplicate' rows MIGHT there be for a certain set of coordiantes?
Is this going to be a finite and predictable number?
Eg: will there always be NO MORE THAN 2 rows that have matching coordinates?
Or is it possible that there could be SEVERAL [unpredictable number of] rows that have matching coordinates?

For the next year or so...there is likely only going to be 2 rows with matching coordinates. But, after that it could be 3, 4, 5....I have no idea. This archive DB needs to be functional for the long term....at least until we create and can access a secure cloud....then we start from scratch again.

2.
If the number of rows with matching coordinates is not predictable:
You might have to resort to writing VBA - and creating the final Table you need IN the VBA Code - by writing a query that sorts the rows of your Photo_Link_MOD table by Easting . . . and then looping through each row.
Read one row into variables
Move to next row
If the coordinates are the same - add the new Photo Year & Image Path values to variables along with the variables you stored while reading teh first row.
Move to the Next Row.
If the coordinates are different, write the values you stored for the preovious two rows and then store new variables for the new set of coordinates. . . .

and so on.

As I have said in previous messages...VBA coding is not my strongest skill. I may need help to do this one. As a mapping (GIS) specialist, we rarely do VBA coding...or deal with regular DBs (even though this one is set up to be used by the mapping software, it is NOT a true geodatabase like I use). We generally only do XHTML/HTML/XSL/Python scripting...any VBA we need is generated by the software we use.

[Robeen]

If the number of possible 'duplicate' rows is predictable - you can go the query route.

Otherwise - I can't think of a way to do this without using VBA.

The VBA is only a problem if you have not done that kind of coding before - as in your case.
Even if you have - it is time-consuming to get the loop working correctly as you have to make sure that at each step of the way you are executing the correct logical actions - based on whether you have found a new pair of coordinates in the new record or if the coordinates in the current record are the same as the pair in the previous record . . .

Are you up for learning some VBA???

[admessing]

Sure...gonna have to learn it sometime if they keep throwing things like this at me. Go for it...this may run into tomorrow since I am off of work in a couple hours.

[Robeen]

I'll start by giving you a summary of the method.
This will help you stay on track if you feel you're drowning in code!

1. Declare database & recordset variables - AND - variables to hold values from each field in the current & subsequent rows of data that the recordset returns.
2. Open the recordset and read the first row of data - and store all fields in your field variables.
3. Move to the next row in the recordset.
4. Compare the Easting & Northing of the new row with the Easting & Northing that you have in your variables for the previous row of data.
5a. If they are identical [you are at the same coordinates] - add the photo year and image path values to the list of variables - and move to the next row.
5b. If the coordinates have changed - write all your variables into a row in the table you have set up to receive this data - re-set all your variables to "" [empty] and then read in the next row of data and loop through this process till you get to the end of the file - or EOF [no more rows in the table].

Next . . .
Here is a simplified example of code that can make this work:

To 'get your feet wet' - create a Table1 with a text field named 'Field1' and a Number field named 'Field2'.
Type in two or three records.
Then - create a Module and paste in this code.

 
Function Get_DB_Values()
'Assumes that you have a Table1 and that Field1 is Text and Field2 is a Number.
 
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strField1, StrSQL, strSQL2 As String
Dim intField2 as Integer
On Error GoTo Error_Handle
 
Set db = CurrentDb
strSQL = "Select * From [Table1]"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
With rs
 
'This Do While loop goes through all the records in strSQL.
Do While Not rs.EOF
 
'Pull values from the current row into variables.
strField1 = rs![Field1]
intField2 = rs![Field2]
 
'I put this little If Then here so you can look at some values as the code executes.
'substitute these with your own values.
'This is where you will write the logic part of your code.
'To start out - just play with this If - Then - Else statement
'using a real value from your Table1 where i have the number 5.
 
If intField2 = 5 Then 
    'MsgBox strField1
End If
 
'**************
'This is where you will also have your 'Insert' logic that will add records to your 'final' Table
'when your logic says you have encountered a new East/north pair.
'That code looks like this [it's commented out for now]:
'StrSQL2 = "INSERT INTO Table1 (Field1) "
'StrSQL2 = StrSQL2 & "VALUES (" & "'" & StrField1 & "'" & "); "
'DoCmd.RunSQL StrSQL2
 
'. . . of course - your StrSQL2 will look complicated because there will be
'so many fields that you need to add to the table you will be creating.
'**************
 
.MoveNext                     'Move to next record in recordset.
 
Loop                         'Back to 'Do While' to check if we are at the end of the file.
 
Exit_Get_DB_Values:
    If Not rs Is Nothing Then
        rs.Close
        Set rs = Nothing
    End If
    Set db = Nothing
    Exit Function
 
Error_Handle:
    Resume Exit_Get_DB_Values
End With
End Function

To run this code - I highlight the function name and then click the Run button [green 'Play' button] at the top of the code window.

Let me know if you have questoins.

[admessing]

Anyone with thoughts? I have attempted to write queries...I get most of the way there...then failure . I have attempted modifying the VBA provide by Robeen....no results (see below).

I am beginning to get tired of looking at the same DB day after day....

Anyone? The sample DB is in a previous post. The table that all this needs to be based upon is the Photo_Link one (May be listed as Photo_Link_MOD in the version here).

Function Get_DB_Values()
'Get values from a table using a query in VBA.
'Process values row by row.
'Insert processed row into another Table.
Dim db As DAO.Database
Dim rs As DAO.Recordset

Dim intField1, intField2, intField4, intField5 As Integer
Dim strField3, strField6, strField7, strField8, strField9 As String
Dim intPrevField1, intPrevField2, intPrevField4, intPrevField5 As Integer
Dim strPrevField3, strPrevField6, strPrevField7, strPrevField8, strPrevField9 As String
Dim intNewField1, intNewField2, intNewField4, intNewField5 As Integer
Dim strNewField3, strNewField6, strNewField7, strNewField8, strNewField9 As String
Dim intSQL As Integer
Dim strSQL As String
Dim intRecordCount As Integer

On Error GoTo Error_Handle

Set db = CurrentDb

strSQL = "Select * From [Photo_Link] ORDER BY [Easting_UTM]"
intRecordCount = 1
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

With rs

'This Do While loop goes through all the records in strSQL.
Do While Not rs.EOF
If intRecordCount = 1 Then
intField1 = rs![Easting_UTM]
intField2 = rs![Northing_UTM]
strField3 = rs![FSVeg_Loc]
intField4 = rs![Stand_Num]
intField5 = rs![Photo_Year]
strField6 = rs![IMG_North]
strField7 = rs![IMG_East]
strField8 = rs![IMG_South]
strField9 = rs![IMG_West]
intRecordCount = intRecordCount + 1
Else 'Not first record.
intNewField1 = rs![Easting_UTM]
If intNewField1 = intPrevField1 And intNewField2 = intPrevField2 Then 'Same Field1 - concatenate values.
strNewField3 = rs![FSVeg_Loc]
intNewField4 = rs![Stand_Num]
intNewField5 = rs![Photo_Year]

If strNewField5 <> strPrevField5 Then
strField5 = strField10
End If
If strNewField6 <> strPrevField6 Then
strNewField6 = strField11
End If
If strNewField7 <> strPrevField7 Then
strField7 = strField12
End If
If strNewField8 <> strPrevField8 Then
strField8 = strField13
End If
If strNewField9 <> strPrevField9 Then
strField9 = strField14
End If
Else 'Field1 changed - Write the record to other table.
'Create Insert SQL.
strSQL = "INSERT INTO Photo_Link_Combined (Easting_UTM, Northing_UTM, FSVeg_Loc, Stand_Num, Photo_Year, IMG_North, IMG_East, IMG_South, IMG_West, Photo_Year2, IMG_North2, IMG_East2, IMG_South2, IMG_West2) "
strSQL = strSQL & "VALUES (" & "'" & intNewField1 & "'" & ", " & "'" & intField2 & "'" & ", " & "'" & strField3 & "'" & ", " & "'" & intField4 & "'" & ", " & "'" & intNewField5 & "'" & ", " & "'" & strField6 & "'" & ", " & "'" & strField7 & "'" & ", " & "'" & strField8 & "'" & ", " & "'" & strField9 & "'" & ", " & "'" & intNewField10 & "'" & ", " & "'" & strField11 & "'" & ", " & "'" & strField12 & "'" & ", " & "'" & strField13 & "'" & ", " '" & strNewField14 & "'" & "); "

'Execute Insert SQL
DoCmd.RunSQL strSQL

'Populate current row values into variables.
intField1 = rs![Easting_UTM]
intField2 = rs![Northing_UTM]
strField3 = rs![FSVeg_Loc]
intField4 = rs![Stand_Num]
intField5 = rs![Photo_Year]
strField6 = rs![IMG_North]
strField7 = rs![IMG_East]
strField8 = rs![IMG_South]
strField9 = rs![IMG_West]

End If 'End If strNewField1 = strField1 Then
End If 'End If intRecordCount = 1
intPrevField1 = intNewField1
intPrevField2 = intNewField2
strPrevField3 = strNewField3
intPrevField4 = intNewField4
intPrevField5 = intNewField5
strPrevField6 = strNewField6
strPrevField7 = strNewField7
strPrevField8 = strNewField8
strPrevField9 = strNewField9

.MoveNext 'Move to next record in recordset.
Loop 'Back to 'Do While' to check if we are at the end of the file.
'Create SQL for Last Row of data that is still stored even though Access found the EOF.
strSQL = "INSERT INTO Photo_Link_Combined (Easting_UTM, Northing_UTM, FSVeg_Loc, Stand_Num, Photo_Year, IMG_North, IMG_East, IMG_South, IMG_West) "
strSQL = strSQL & "VALUES (" & "'" & intField1 & "'" & ", " & "'" & intField2 & "'" & ", " & "'" & strField3 & "'" & ", " & "'" & intField4 & "'" & ", " & "'" & intField5 & "'" & ", " & "'" & strField6 & "'" & ", " & "'" & strField7 & "'" & ", " & "'" & strField8 & "'" & ", " & "'" & strField9 & "'" & "); "
'Execute Insert SQL.
DoCmd.RunSQL strSQL

Exit_Get_DB_Values:
If Not rs Is Nothing Then
rs.Close
Set rs = Nothing
End If
Set db = Nothing
Exit Function
Error_Handle:
Resume Exit_Get_DB_Values
End With
End Function

[admessing]

Anyone with thoughts?

[Robeen]

What exactly happens when you run your code?

1.
In your Insert statement, you are treating the string variables and the integer variables with the same syntax.
The syntax for inserting Strings and Integer values is different.
There is a very good example of the differences here:
http://www.baldyweb.com/wherecondition.htm

2.
It looks like you are using strSQL for two tasks:
i. To Read your Table and
ii. To Write data.
I'm not sure that that is a problem - but in the code sample I gave you, I have a second variable strSQL2 that I use fot the insert.

Have you tried putting a Break Point in the code and stepping through to see what is happening?
What I sometimes do is run the code with only two or three rows of data in the table so that I don't have to loop through the entire table.
You could restrict the number of rows that your first query returns

[admessing]

Okay....I got the code to run (see below)....and my results are attached as a screenshot .doc file.

Code:

Function Get_DB_Values()
'Assumes that you have a Table1 and that Field1 is Text and Field2 is a Number.
 
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strField1, StrSQL, strSQL2 As String
Dim intField2 As Integer
On Error GoTo Error_Handle
 
Set db = CurrentDb
StrSQL = "Select * From [Table1]"
Set rs = db.OpenRecordset(StrSQL, dbOpenSnapshot)
With rs
 
'This Do While loop goes through all the records in strSQL.
Do While Not rs.EOF
 
'Pull values from the current row into variables.
strField1 = rs![Field1]
intField2 = rs![Field2]
 
'I put this little If Then here so you can look at some values as the code executes.
'substitute these with your own values.
'This is where you will write the logic part of your code.
'To start out - just play with this If - Then - Else statement
'using a real value from your Table1 where i have the number 5.
 
If intField2 = 5 Then
    'MsgBox strField1
End If
 
'**************
'This is where you will also have your 'Insert' logic that will add records to your 'final' Table
'when your logic says you have encountered a new East/north pair.
'That code looks like this [it's commented out for now]:
strSQL2 = "INSERT INTO Table1 (Field1) "
strSQL2 = strSQL2 & "VALUES (" & "'" & strField1 & "'" & "); "
DoCmd.RunSQL strSQL2
 
'. . . of course - your StrSQL2 will look complicated because there will be
'so many fields that you need to add to the table you will be creating.
'**************
 
.MoveNext                     'Move to next record in recordset.
 
Loop                         'Back to 'Do While' to check if we are at the end of the file.
 
Exit_Get_DB_Values:
    If Not rs Is Nothing Then
        rs.Close
        Set rs = Nothing
    End If
    Set db = Nothing
    Exit Function
 
Error_Handle:
    Resume Exit_Get_DB_Values
End With
End Function 'To run this code - I highlight the function

Are my results what I am supposed to get? I seems a bit redundant to just copy the Field1 values and just paste them into new records in the same table.

[admessing]

Here is the other VBA...this shoudl concatenate the values.....BUT, it isn't grouping the correctly.

Code:

Function Get_DB_Values()
'Get values from a table using a query in VBA.
'Process values row by row.
'Insert processed row into another Table.
'Requirements:
'1. Two Tables [AA_Test] & [AA_Test_Write] with four fields named  'One', 'Two', 'Three', 'Four' - All Text fields.
'   both tables have the same field names in this example - just go with it!! :D
'2. A few Rows of data in [AA_Test]. Leave [AA_Test_Write] empty.
'3. You will need a couple of sets of rows to have matching field [One] data.
'   Rows with matching values in field [One] will have other values concatenated.
'4. I'm attaching a screenshot of the two tables so you can perhaps duplicate my data.
'   The top is [AA_Test] & under it is [AA_Test_Write] - with the data that got written to it - based on logic.
'5. Just set the two tables up and execute the code and see what happens.
 
Dim db As DAO.Database
Dim rs As DAO.Recordset
 
Dim strField1, strField2, strField3, strField4 As String
Dim strPrevField1, strPrevField2, strPrevField3, strPrevField4 As String
Dim strNewField1, strNewField2, strNewField3, strNewField4 As String
Dim StrSQL As String
Dim intRecordCount As Integer
 
On Error GoTo Error_Handle
 
Set db = CurrentDb
 
StrSQL = "Select * From [AA_Test] ORDER  BY [One], [Two], [Three], [Four]"
intRecordCount = 1
Set rs = db.OpenRecordset(StrSQL, dbOpenSnapshot)
 
With rs
 
'This Do While loop goes through all the records in strSQL.
Do While Not rs.EOF
If intRecordCount = 1 Then
    strField1 = rs![One]
    strField2 = rs![Two]
    strField3 = rs![Three]
    strField4 = rs![Four]
    intRecordCount = intRecordCount + 1
Else                                    'Not first record.
    strNewField1 = rs![One]
    If strNewField1 = strPrevField1 Then    'Same Field1 - concatenate values.
        strNewField2 = rs![Two]
        strNewField3 = rs![Three]
        strNewField4 = rs![Four]
 
        If strNewField2 <> strPrevField2 Then
            strField2 = strField2 & ", " & strNewField2
        End If
        If strNewField3 <> strPrevField3 Then
            strNewField3 = strField3 & ", " & strNewField3
        End If
        If strNewField4 <> strPrevField4 Then
            strField4 = strField4 & ", " & strNewField4
        End If
 
    Else                                         'Field1 changed - Write the record to other table.
        'Create Insert SQL.
        StrSQL = "INSERT INTO AA_Test_Write (One, Two, Three, Four) "
        StrSQL = StrSQL & "VALUES (" & "'" & strNewField1 & "'" & ", " & "'" & strField2 & "'" & ", " & "'" & strField3 & "'" & ", " & "'" & strField4 & "'" & "); "
 
        'Execute Insert SQL
        DoCmd.RunSQL StrSQL
 
        'Populate current row values into variables.
        strField1 = rs![One]
        strField2 = rs![Two]
        strField3 = rs![Three]
        strField4 = rs![Four]
 
    End If                              'End If strNewField1 = strField1 Then
End If                                  'End If intRecordCount = 1
    strPrevField1 = strNewField1
    strPrevField2 = strNewField2
    strPrevField3 = strNewField3
    strPrevField4 = strNewField4
 
.MoveNext                               'Move to next record in recordset.
Loop                                    'Back to 'Do While' to check if we are at the end of the file.
    'Create SQL for Last Row of data that is still stored even though Access found the EOF.
    StrSQL = "INSERT INTO AA_Test_Write (One, Two, Three, Four) "
    StrSQL = StrSQL & "VALUES (" & "'" & strField1 & "'" & ", " & "'" & strField2 & "'" & ", " & "'" & strField3 & "'" & ", " & "'" & strField4 & "'" & "); "
    'Execute Insert SQL.
    DoCmd.RunSQL StrSQL
 
Exit_Get_DB_Values:
    If Not rs Is Nothing Then
        rs.Close
        Set rs = Nothing
    End If
    Set db = Nothing
    Exit Function
Error_Handle:
    Resume Exit_Get_DB_Values
End With
End Function

I'm confused

[Robeen]

That's pretty close to what that code is supposed to do!
You're on the right track.

With this last code of yours, however, there should only be one row of data per color.

The fact that you're getting two rows for one of the colors means that something in the code is incorrect.

You should troubleshoot it to make all colors in Field1 have only ONE row.
That will be good progress.

Are you familiar with how to troubleshoot code [using Break Points to step through code and track variables as they change etc]?

[admessing]

Hmm....that's odd since it is verbatim from what you gave me....I'll see if I can get some break points in and see where the issue is. But I have never used them and the "help" doesn't exactly tell you how to use them or where to put them...it is just a basic description that means very little.