Replace values of other table which same in one table

[ootkhopdi]

Hi


i have one table
having IDs like this
S.No. ID Value
1 A Z
2 A Y
3 B X
4 C W
5 A W
now i create a query which having unique ids and value filtered=W
now result in query is
Id Value
C W
A W


now i want to replace all values of Id "A" and "C "into P and



how can i do it..
i have more than 50 records in query means more than 50 Ids with Same Filtered Value..
i want to replace all values of Ids , which are in query into P
if i do it one by one it takes too much time..i want to do it fast..

how can i do..


i think i clear my problem..


please help me

 

[isladogs]

Use an update query:

To do this for all records:

UPDATE YourTableName SET YourTableName.ID = 'P'
WHERE ((YourTableName.ID='A') Or (YourTableName.ID='C'));

Or if you only want to do this where Value = 'W'

UPDATE YourTableName SET YourTableName.ID = 'P'
WHERE (((YourTableName.ID='A') Or (YourTableName.ID='C')) AND YourTableName.Value='W';

Substitute your own table or query name in the above
Also Value is a reserved word in Access so should not be used as a field name

 

[ootkhopdi]

Thanks Ridders
but there is a problem


as i tell me in my post.

i have more than 50 ids which values are containing or equal to "W".
In your suggestion , i can update one or two ids , but i want to update all ids which in a table or query.


please tell me how can i do..


thanks once again

 

[arnelgp]

Update query:

Update yourTableName Set Id = "P" Where yourTableName. Value="W";

 

[ootkhopdi]

i think there is some confusion


let i try to more clear my problem


1- i have a table with 10000 records with 10 fields , Main Fields are IDs and Value
2-In Table Values of Ids are different like my example.


ID Value
A W
B X
A Y
C Z
C J
A Z



3- Now create a query to unique ids with more than 1 values..
Id

A

C


4-Now i want to update all values as Below
Id Value
A W
C Z


now if i do it manually.

i follow below steps.
a-first i find value in field = "Y"
b-Now i filter all records ,which id =A
c- now replace , all values to W,
d-Remove filter..
e. Step 1


as.my query having a large no of records(Ids) so when i do it one by one. it takes too much time.. so i want to update values in table

all values to related records in query to their related ids.
like where Id =A ,, Field value to W
Id=c , field =Z
id ... Field..


i hope now clear more

 

[isladogs]

I think you've added to the confusion with your latest post.
Previously you wanted to update the ID field to 'P' in certain circumstances (which weren't clear).
In the latest post, there is no mention of doing that.

Now I think you're asking to update records where there are duplicate IDs so the Value field is equal to the first record Value for that ID. But I'm far from certain about that.

I suggest you post an example db with two tables showing simple data before and after changes required. Include enough results so it's absolutely clear what is required.

 

[arnelgp]

If you want to change the Value of each record with same ID's but
differing Value (just a wild guess based on your sample where Value "W" is the first occurrence in "A" ID and Value "Z" is the first occurence in ID: "C"), and the New Value is the first occurrence on the recordset,
you can do it in code:

Private Sub TEST()

    '!!! Replace yourTable in the code with your tablename
    ''
    Const THIS_TABLE As String = "yourTable"
    
    Dim rs1 As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Dim sValue As String
    
    Set rs1 = CurrentDb.OpenRecordset("Select Distinct ID From " & THIS_TABLE)
    With rs1
        .MoveFirst
        While Not .EOF
            Set rs2 = CurrentDb.OpenRecordset("Select * From " & THIS_TABLE & " Where ID = "" & !ID & """)
            sValue = ""
            If Not (rs2.BOF And rs2.EOF) Then rs2.MoveFirst
            While Not rs2.EOF
                If sValue = "" Then
                    sValue = rs2.Value
                
                Else
                    If sValue <> !Value Then
                        rs2.Edit
                        rs2.Value = sValue
                        rs2.Update
                    End If
                End If
                rs2.MoveNext
            Wend
            rs2.Close
            .MoveNext
        Wend
        .Close
    End With
    Set rs2 = Nothing
    Set rs1 = Nothing
End Sub

it would be much error prone if you add an autonumber field.
if you have added an autonumber field on your table,
you need to modify this part, to:

            Set rs2 = CurrentDb.OpenRecordset("Select * From " & THIS_TABLE & " Where ID = "" & !ID & "" Order by yourAutoNumberFieldName")

again replace the yourAutoNumberField to the correct fieldname.

 

[ootkhopdi]

Thanks to All..
i have done it with a form "Update value",


i create a combo box with query unique id ,a text box for update values, and a command button with update query..


i do it ..thanks all to give me replies..