Update field values in records that contain duplicate information

Question

I have a query that returns all records that contain the word "PACK" in more than one Dept... field (at most, the word "PACK" occurs twice in a record, and all by itself within the field):

SELECT *
FROM
(
  SELECT s.*,
    IIf(s.DeptOne = "Pack", 1, 0) AS D1,
    IIf(s.DeptTwo = "Pack", 1, 0) AS D2,
    IIf(s.DeptThree = "Pack", 1, 0) AS D3,
    IIf(s.DeptFour = "Pack", 1, 0) AS D4,
    IIf(s.DeptFive = "Pack", 1, 0) AS D5,
    IIf(s.DeptSix = "Pack", 1, 0) AS D6,
    IIf(s.DeptSeven = "Pack", 1, 0) AS D7,
    IIf(s.DeptEight = "Pack", 1, 0) AS D8,
    IIf(s.DeptNine = "Pack", 1, 0) AS D9,
    IIf(s.DeptTen = "Pack", 1, 0) AS D10,
    IIf(s.DeptEleven = "Pack", 1, 0) AS D11,
    IIf(s.DeptTwelve = "Pack", 1, 0) AS D12,
    IIf(s.DeptThirteen = "Pack", 1, 0) AS D13,
    IIf(s.DeptFourteen = "Pack", 1, 0) AS D14,
    IIf(s.DeptFifteen = "Pack", 1, 0) AS D15,
    IIf(s.DeptSixteen = "Pack", 1, 0) AS D16,
    IIf(s.DeptSeventeen = "Pack", 1, 0) AS D17,
    IIf(s.DeptEightteen = "Pack", 1, 0) AS D18,
    IIf(s.DeptNineteen = "Pack", 1, 0) AS D19,
    IIf(s.DeptTwenty = "Pack", 1, 0) AS D20
FROM MyTable s
) m
WHERE (m.D1 + m.D2 + m.D3 + m.D4 + m.D5 + m.D6 + m.D7 + m.D8 + 
       m.D9 + m.D10 + m.D11 + m.D12 + m.D13 + m.D14 + m.D15 + 
       m.D16 + m.D17 + m.D18 + m.D19 + m.D20) > 1

The query works great, and returns all records that have "PACK" in multiple Dept... fields. Now I need to change the first instance of "PACK" in those records to "PACK-M" and the second instance of "PACK" in those records to "PACK-S"

To view a picture of two records returned in the query go here: http://www.allgoodit.com/stackoverflow/result.jpg

Both records have the word "PACK" appear twice in different fields. I wish to ~~have~~ re-write the first instance within each record to "PACK-M" and the second to "PACK-S". No VBA, just SQL.

@Bulat See edit. For a picture of sample data returned by query, go to http://www.allgoodit.com/stackoverflow/result.jpg — prayingmantes, Sep 18 '14 at 17:39
I hope it is the right time, you go about redesigning your tables. — PaulFrancis, Sep 24 '14 at 09:06
When you say "change the word", do you mean re-write the table data, or change the value that is displayed while leaving the data untouched? — Smandoli, Sep 26 '14 at 17:52
You'd be a whole lot better off revisiting the architecture here. At the very least, you should consider implementing a pivot table. — Tony Chiboucas, Sep 30 '14 at 18:15

Mark C. · Accepted Answer · 2014-09-24T14:33:06.857

2

I think this should do the trick.

Just change your table name in the SQL statement that opens the Recordset.

Dim t1 As String
Dim t2 As String
Dim p1 As String
Dim p2 As String

p1 = "PACK-M"
p2 = "PACK-S"

Dim MyDB As DAO.Database
Dim rst As DAO.Recordset

Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset("SELECT * FROM PRAYING") 'Change PRAYING to your table name Or change the string to your query name


With rst
Do While Not .EOF
        For i = 0 To .Fields.Count - 1
            If .Fields(i).Value = "PACK" And t1 <> p1 Then
                .Edit
                .Fields(i).Value = "PACK-M"
                .Update
                t1 = p1
            ElseIf .Fields(i).Value = "PACK" Then
                .Edit
                .Fields(i).Value = "PACK-S"
                .Update
                t2 = p2
            End If
        Next
    .MoveNext
    t1 = ""
    t2 = ""
Loop
End With

rst.Close
Set rst = Nothing

Note: There is nothing in your question that said this must be SQL, so if VBA is not an option you should have specified.

edited Sep 24 '14 at 14:33

answered Sep 24 '14 at 14:26

Mark C.

6,332
4
35
71

Looks pretty good. Could be refactored a bit...`t2` isn't used, Boolean values would be more elegant. Waiting on upvote till I have the answer to my question asked above. – Smandoli Sep 26 '14 at 18:03
@Smandoli Yeah, t2 is not used (I had used it in testing, as well as p2). As a supplement to your comment above - OP could always use a temp table and perform this if no underlying data was to be change, but a good question nonetheless. – Mark C. Sep 26 '14 at 18:05
@OverMind sorry. This must be SQL. – prayingmantes Sep 30 '14 at 07:28
2

@prayingmantes, it is important to specify "no VBA" in the post. Otherwise we may waste our time! Also, if you provide the reason for the requirement, that may provide insights for those who would answer (I mean, helpful beyond just knowing to leave off VBA solutions). – Smandoli Sep 30 '14 at 13:31
@OverMind This worked. I was able to use VBA. Sorry for the confusion. And thank you very much! – prayingmantes Oct 01 '14 at 19:42

score 2 · Answer 2 · edited May 23 '17 at 11:49

You could use a binary scheme to discern which columns the values are coming from.

So, for example, use your query the way you have it ( I am reducing the number of columns for ease of typing )

SELECT *
FROM
(
  SELECT s.*,
    IIf(s.DeptOne = "Pack", 2, 0) AS D1,
    IIf(s.DeptTwo = "Pack", 4, 0) AS D2,
    IIf(s.DeptThree = "Pack", 8, 0) AS D3,
    IIf(s.DeptFour = "Pack", 16, 0) AS D4,
    IIf(s.DeptFive = "Pack", 32, 0) AS D5,
    IIf(s.DeptSix = "Pack", 64, 0) AS D6,
    IIf(s.DeptSeven = "Pack", 128, 0) AS D7,
    IIf(s.DeptEight = "Pack", 256, 0) AS D8,
    IIf(s.DeptNine = "Pack", 512, 0) AS D9,
    IIf(s.DeptTen = "Pack", 1024, 0) AS D10
FROM MyTable s
) m
WHERE ISNUMERIC( LOG( (m.D1 + m.D2 + m.D3 + m.D4 + m.D5 + m.D6 + m.D7 + m.D8 + 
       m.D9 + m.D10 ), 2 ) + '.0e0' )

Notice that the 'WHERE' clause has to change because looking for sum greater than 1 will have false positives. So, you can change it to look for whether the sum is a power of 2. I am using the 'log' function with a base of 2 and then checking if the result is an integer. To check if a value is an integer, you can append '.0e0' and check isnumeric.

Check this link for the source of this 'hack'.

Then, you can inspect the 'sum' value to determine the smallest bit to determine which column needs updated with 'PACKM' and the larger bit would be the column that needs updated with 'PACKS'

*Disclaimer: I haven't provided the 'UPDATE' script as part of my answer. I have also not tested my 'SELECT' statement and 'WHERE' logic. I just hope I have provided enough explanation to point you in the appropriate direction with some solid reasoning. Hope this helps you.

Creative. Strenuous. If I couldn't use VBA, I'd give it consideration. — Smandoli, Sep 26 '14 at 18:05
At first glance, I was like, whaaaatt??? But after actually looking it, this is pretty cool. Seems like an answer to a code golf question. I would make a suggestion, if I may, that you test it out and complete it your answer. (even if only using 10 fields) — Mark C., Sep 26 '14 at 18:40
I also would love to see more of the gritty details, for the sake of the OP. Hey, I'd settle for 5 fields! — Smandoli, Sep 26 '14 at 19:41
Sounds good. I'll post something tomorrow. Glad you guys found this interesting. — punsandguns, Sep 29 '14 at 03:05

Update field values in records that contain duplicate information

2 Answers2