deleting the repeated record from a particular field

[dennymw]

hello all, I need a help in VBA, I have table in my MS access, with two fields, name and items eg : Name items Ram | Book Ram | Pen Ram | Pencil Raja | Book I want to delete a particular rowfield from my table if the data repeats. After deleting, i want the table to be like Ram | Book | Pen | Pencil Raj | Book i dont want to repeat Ram hope my doubt is clear

[Nanda_MR]

Actually u don't want display the ram repeatedly use SQL query Union, Union all, minus. or you can use condition and remove the name or same records.

[dennymw]

Can i update 2nd and third ram with some particular value

[Nanda_MR]

Ya u can do it in 2 ways 1.permanently or 2.temp. 1. Update the database record using update statement. 2. use Condition statement replace value in front end.

[Lost User]

You can use Update query i which u can set value. Like : Update table Set Name = "Demo" where Class = "null"; Regard Anubhava Dimri

mailto: anubhava.prodata@gmail.com

[dennymw]

Thanks friends... but if i use the update command, all the three ram is replaced with some particular value. i want to retain first Ram just want replace second and third with some value or with blank space. How to do these.

[Nanda_MR]

In where class use the more then one condition like where name='Ram' and age=20; like then that record only updated. Ananda.

[dennymw]

Sorry for disturbing. My table name is General Issue table and the two fields are TestCaseName and TestScriptName. In TestCaseName column some testcases are same. i just want only one entry for particular test case. The code i using is, rs.Open "SELECT TestCaseName FROM [General Issue table]", oConnection Do While (rs.EOF = False) varTestCaseName_Repeat = rs.Fields(0).Value rs.MoveNext If (varTestCaseName_Repeat = rs.Fields(0)) Then "here how to use update statement" End If Loop

[fego ogwara]

TO DELTE THAT RECORD WHAT U NEED DO IS TO SET UR DATABASE IN ORDER NOT TO REPEAT A NAME. FOR INSTANCE IF U ARE USING ADODC CONTROL THE CODE WILL BE LIKE THESE DIM RS AS NEW ADODB.RECORDSET DIM CN AS NEW ADODB.CONNECTION CN.CONNECTIONSTRING ="DATABASE PATH" CN.OPEN RS.OPEN "DELETE * FROM TABLENAME WHERE FIELDNAME='" & NAME & "'",CN

[Nanda_MR]

Use this.

Dim Str As String
rs.Open "SELECT TestCaseName FROM [General Issue table]", oConnection
Do While (rs.EOF = False)
varTestCaseName_Repeat = rs.Fields(0).Value
rs.MoveNext
If (varTestCaseName_Repeat = rs.Fields(0)) Then
Str= "UPDATE TestCaseName SET name='" & value1 & "', age=" & value2 & " WHERE name='" & rs.Fields(0) & "' And age=" & rs.Fields(1) & ";"
rs.Open Str, oConnection
End If
Loop

Ananda