12-20-06, 10:56 #1Registered User
- Join Date
- Nov 2006
Unanswered: detecting duplicate entry i& Compuatation issue
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
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.
12-20-06, 12:09 #2Registered User
- Join Date
- Oct 2002
- Leicester - UK
the best method for finding dups is to do the following
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 fieldDefinition of a Beginner, Someone who doesn't know the rules.
Definition of an Expert, Someone who knows when to ignore the rules.