Access DB query - need help updating certain records

Question

I have an access DB which we use to track tickets. Each ticket may have multiple occurrences because of different programming changes associated with that ticket. Each record also has a program_type field which is SVR or VB. Example:

123456 - SVR - SomeCode

123456 - VB - SomeVBCode

I've added a column to the database called VB_Flag, which defaults to 0, which I would like to change to the number 1 for every ticket containing VB code. So, the result here would be:

123456 - SVR - SomeCode - 1

123456 - VB - SomeVBCode - 1

But, I can't figure out for the life of me how to write this update query. At first I tried:

UPDATE table SET VB_Flag = 1 WHERE program_type = 'VB'

But that obviously left out all the SVR code that shared a ticket number with the VB code.

I'm at a loss. Help?

Answer 1

This should work:

UPDATE table SET VB_Flag = 1 
WHERE TicketNum IN (SELECT TicketNum FROM Table WHERE program_type = 'VB')

Answer 2

A simple nested select should take care of your problem:

UPDATE myTable
SET VB_Flag = 1 
WHERE TicketID in (Select TicketID from myTable WHERE program_type = 'VB')

Answer 3

UPDATE Table INNER JOIN Table AS Table2 ON Table.TicketNumber = Table2.TicketNumber SET Table2.VB_Flag = 1 WHERE (([Table].[program_type]="VB"))

Answer 4

You can do something like this:

UPDATE tickets SET VB_Flag = 1
WHERE ticket_id IN (SELECT ticket_id FROM tickets WHERE program_type = 'VB');

The inner SELECT statement returns a list of all ticket_ids that have a program_type of 'VB'.

The update then sets the VB_Flag to 1 for ALL records with one of those ticket_ids (that includes those with a program_type of 'SVR'.