Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2006

    Unanswered: detecting duplicate entry i& Compuatation issue

    Hi guys,

    I am using MS Acess in visual basic environment. what i wants to achieve is to be able to check for any duplicate entries in the database using sql quey (insert, update). But nt too sure as to hw this can be achieved. Any guidance or code ref is kindly appreciated. I tried to use a foreach loop to iterate through the records but nt working fine, i have declared the variables as a variant for the for each loop. I would like to make of the properties of recordSet to achieve this. if the entries exists it sld nt be inserted in the table. SerialCode AutoNum as the pk. Another issue is whenever i appended, inserted new records the previous record gt overwritten, which shld nt be the case

    Set recorset
    open database for the recordset
    find the records field
    check for duplicate entry before inserting or updating

    Next, i would like to compute the balance. For instance I have these few fields in my table (TotalParticpants, WithDrawl, Bal) where activitiy = running. I want to write a sql stat that will able to compute the bal of particpants that did not withdrawl from the contest and update the bal in table.

    My query as followed
    select TotalParticpants, Withdrawl, bal(TotalParticipants - Withdrawl)
    from Contest where Activity = Running.

    Acess the fields (TotalParticpants, Withdrawl) frm the table. Retreieved the values of fields(TotalParticpants = 100, Withdrawl = 500) and compute the bal = 500 in textbox of the form and update the value in the contest table. But nt too sure hw to mainpulate the recordSet to acess the field, retrieve the fields values from the database and update the bal in the contest table.

    Thanks folks for guidance or code ref.

  2. #2
    Join Date
    Oct 2002
    Leicester - UK
    the best method for finding dups is to do the following

    SELECT tbl.*
    FROM tbl JOIN tbl AS tbl2 ON tbl.fld = tbl2.fld

    tbl is the table
    fld is the field with the duplicate values
    pk is the primary key field
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts