MS Access Insert/Update

[BobDole]

I have spent countless hours researching this issue and have found nothing that works.

I have data in one table that has duplicate records... they aren't duplicate in the usual sense though, I need to sum the quantity field in those duplicates and make it one record.

For example,
PK Fields - Store#, Item#
non-PK Fields - Description, Qty

Right now, the way the data is available (via excel files) there are multiple records of the same item from the same store with different Qty's that need to be summed together to get the true value.

In MS SQL I could very easily write some T-SQL that does something that basically says... If exists Update... else insert

The problem: Jet SQL is neutered. I can not find ANYTHING that can get this done.

I will love forever the person that can help me with this.

[BobDole]

I think I have some sort of a solution, I can insert into another table using the following select as a subquery in the insert. Heres the problem, this select isn't doing what it is supposed to do, any ideas?

SELECT Temp.[Str #], Temp.M, Temp.[Street Address], Temp.City, Temp.State, Temp.[Zip Code], Temp.[Phone Nbr], Temp.Description, Temp.[Item #], Temp.YTD, Temp.OH, Sum(Temp.Qty) As SumQty
FROM Temp
GROUP BY Temp.[Str #], Temp.[Item #], Temp.Description, Temp.M, Temp.[Street Address], Temp.City, Temp.State, Temp.[Zip Code], Temp.[Phone Nbr], Temp.YTD, Temp.OH;

[BobDole]

Ok, the fact that it wasn't doing what it should be was a typo in the data, this would have not been an issue if the group by only contained store# and item#, is there a way to have it so I don't have to include everything in the group by?

[whiteflags]

Dump all items and store names in a query and filter for duplicates (but don't remove them) of store name records. I know Access has a filter for this purpose. You will have to rebuild the whole table still but it should be pretty easy based on the query results.

Set the store name as a primary key after you finish it though so that people are forced to update records with the same store name. Data integrity issues basically screw the entire database (sometimes to the point where you have to roll back to a backed up state) so prevention is probably the best method to employ from now on.