MS Access Insert/Update

This is a discussion on MS Access Insert/Update within the Tech Board forums, part of the Community Boards category; I have spent countless hours researching this issue and have found nothing that works. I have data in one table ...

  1. #1
    Registered User
    Join Date
    Aug 2002
    Posts
    14

    MS Access Insert/Update

    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.

  2. #2
    Registered User
    Join Date
    Aug 2002
    Posts
    14
    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;

  3. #3
    Registered User
    Join Date
    Aug 2002
    Posts
    14
    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?

  4. #4
    Registered User whiteflags's Avatar
    Join Date
    Apr 2006
    Location
    United States
    Posts
    7,709
    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.

Popular pages Recent additions subscribe to a feed

Similar Threads

  1. Speed test result
    By audinue in forum C Programming
    Replies: 4
    Last Post: 07-07-2008, 05:18 AM
  2. Ping
    By ZakkWylde969 in forum Tech Board
    Replies: 5
    Last Post: 09-23-2003, 12:28 PM
  3. The Timing is incorret
    By Drew in forum C++ Programming
    Replies: 5
    Last Post: 08-28-2003, 04:57 PM
  4. MS Access & C++Builder
    By Colin in forum C++ Programming
    Replies: 0
    Last Post: 03-07-2002, 05:14 AM
  5. using DAO without MS Access installed
    By zMan in forum Windows Programming
    Replies: 2
    Last Post: 02-20-2002, 02:14 PM

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21