Append Query generates duplicate entries

[ricardusmaximus]

Hi guys. I am using an Append Query to add records to an Archive Table in an MS Access Application. The code generates 2 records for each one identified.

strSQLAppend(1) = "INSERT INTO Timings_Archive " & _
                  "SELECT Timings.Record,Timings.Activity_Nr, Timings.Owner, Timings.Role,Timings.Category,Timings.[Sub-Category],Timings.Comments,Timings.[Start Time],Timings.[End Time],Timings.[Total Time],Timings.Overwrite,Timings.[Enter Date],Timings.[Effective Date]" & _
                   "FROM Timings INNER JOIN Activities_Archive " & _
                   "ON Timings.Activity_Nr = Activities_Archive.Activity_Nr;"
 
CurrentDb.Execute strSQLAppend(1), dbFailOnError

[Rabbit]

Your thread has been moved to the Access forum.

Please use code tags when posting code or formatted data.

Why are you joining to activities_archive? You're not selecting or filtering on anything in that table.

[jforbes]

Yep, there are probably two records in Activities_Archive that match the join.

[ricardusmaximus]

that's what is interesting. The records in the Activities_Archive are unique, but the query generates two records for each record matched. If there are multiple Timings Records associated an Activity then 2 records are generated for each one.

[ricardusmaximus]

Well.... I guess I did generate multiple duplicate entries in the Activities_Archive when testing. Thanks for the assist.

[jforbes]

That's normal functionality for an Inner Join. If you only want one record from the Timings_Archive then you could use a Left Join, otherwise you will get a record for each match for both tables.

Like Rabbit, to me, the join to Activities_Archive when you aren't selecting any values from the Table is seems a bit odd. But I'm not really sure what you are attempting to accomplish.

[NeoPa]

JForbes:
If you only want one record from the Timings_Archive then you could use a Left Join

I'm not sure where you're going with that one. I don't believe that will help. To ensure no duplicates are involved you would need to ensure there is only one (or none would also work for a LEFT JOIN) record that matches from the table JOINed to.

[jforbes]

Good catch, Timings_Archive was a typo. I meant Timings:

FROM Timings LEFT JOIN Activities_Archive 

I'm not saying it is right as I'm not really sure what the goal is here, but the Select would return only one record per Timings Record.

[NeoPa]

JForbes:
... but the Select would return only one record per Timings Record.

I'm afraid that is not guaranteed at all. If there were more than one matching [Activities_Archive] record then there would be an output record for each. That was the point I was attempting to make earlier.

The easiest thing to do is to try it out for yourself, but there's supporting info at SQL JOINs.

[jforbes]

Holy cow, I'm not sure how I got turned around on that. Please ignore anything I said about Left Joins as it is way wrong.