Update Query - Multiple Fields

[razorking]

Struggling with this:
I have a table with six fields. I want to update the values in the six fields, in one fell swoop, if possible. The six fields contain two digit codes (record values), or they are null. What I want is: set all field values, across the six fields, to null, unless the value in any one of the fields is "CT". Does that make sense?

I know I could use six separate queries, one each for each field. And put them all in a macro. But is there a way to do this update in one query?

 

[MarkK]

I think SQL like this would work for what you've described.

UPDATE YourTable
SET Field1 = Null, Field2 = Null, Field3 = Null
WHERE Field1 <> "CT" AND Field2 <> "CT" AND Field3 <> "CT"

You would have to change the names, and add a couple of fields.

 

[ByteMyzer]

Yes, there is. You can try the following SQL syntax (substitute highlighted items with actual table/field names):

UPDATE [b][i]MyTable[/i][/b] SET
    [b][i]Field1[/i][/b] = NULL,
    [b][i]Field2[/i][/b] = NULL,
    [b][i]Field3[/i][/b] = NULL,
    [b][i]Field4[/i][/b] = NULL,
    [b][i]Field5[/i][/b] = NULL,
    [b][i]Field6[/i][/b] = NULL
WHERE NOT (
    [b][i]Field1[/i][/b] = 'CT' OR
    [b][i]Field2[/i][/b] = 'CT' OR
    [b][i]Field3[/i][/b] = 'CT' OR
    [b][i]Field4[/i][/b] = 'CT' OR
    [b][i]Field5[/i][/b] = 'CT' OR
    [b][i]Field6[/i][/b] = 'CT');

 

[razorking]

I tried both of the suggestions above. They did not work. I've attached a sample database, with the example table that I am trying to update.

What I want is - for the six fields - update all records to null, in the six fields, except for records that are field value "CT"

 

[JHB]

Can you clarify what/where it doesn't work, then when I run your query it seems to work perfect!

 

[razorking]

Can you clarify what/where it doesn't work, then when I run your query it seems to work perfect!

Not sure what we are missing...? When I try to run the query: query4, it just displays: "you are about to update zero rows"

I'm trying to update all field values to null, across the six fields in the table, except for filed values of CT

 

[stopher]

The logic you have provided in the first post implies to me that you want to set all the fields in a row to null except if any one of the fields has a value of "CT". Put another way, if any one of the value in a row is "CT", do nothing, otherwise set all values in the row to null. The SQL provided by MarkK and ByteMyzer does that with exception of rows that are already null. Since all your rows either have a CT or are all null then no rows will change.

But your logic in post 6 reads slightly different to me. It reads like you want all fields set to null except fields that have a value "CT". In which case you could do this I think:

UPDATE Test SET 
   CT1 = IIf([CT1]='CT','CT',Null), 
   CT2 = IIf([CT2]='CT','CT',Null), 
   CT3 = IIf([CT3]='CT','CT',Null), 
   CT4 = IIf([CT4]='CT','CT',Null), 
   CT5 = IIf([CT5]='CT','CT',Null), 
   CT6 = IIf([CT6]='CT','CT',Null);

 

[The_Doc_Man]

I have a question of a different sort: Are these six fields the ONLY fields in the table? Post #1 of the thread suggests that this is the case. If this is true, then your table cannot possibly have a prime key. If it doesn't have a prime key, then one set of nulls is as good as another and you couldn't tell them apart. The heck with setting the fields to nulls. Delete the record - because the records that don't contain a CT in ANY of its fields will become a faceless, unidentifiable record anyway.

I suspect that you had some sort of idea that Access would remember the order in which the records were defined, but you cannot rely on that. SQL doesn't work on records. It works on SETS of records as defined by your WHERE clauses and the occasional HAVING clauses.

In order to make better sense out of what you are doing, tell us the REASON you wanted these things to be CT or Null and what you thought you would do with the set of records once you were done with this particular task.

 

[stopher]

...If it doesn't have a prime key, then one set of nulls is as good as another and you couldn't tell them apart. The heck with setting the fields to nulls. Delete the record - because the records that don't contain a CT in ANY of its fields will become a faceless, unidentifiable record anyway....

Empty records DO have meaning in the sense of a set of records. If 14 out of 1000 records are blank, that could easily have some meaning. If you delete the 14 blank records then you have lost that information.

 

[razorking]

Thanks for all of the replies. I was trying to avoid adding a lot of detail to this post, that's why I posted a basic request.

There are many more fields to this table than are in the example database. I'm trying to accomplish something, and had an idea, which may or may not work.

The table I am using is imported data. So the fields are what I have to work with, I cannot change the source database structure. The table I am working with contains item/packaging information. This data is in, what I believe is called an array. There are six fields that contain quantity information (numeric). Then six more fields that contain u/m information (PC (pieces) CT (cartons) LB (pounds) SF (square feet) etc.). And six more again that contain more u/m information - as in the u/m information above.
So an example item might have information like this:
QTY......UM1......UM2
1..........PC.........EA
1.........SF..........PC
6..........PC.........CT
40........CT.........PA
(there are six rows of these)


I am trying to pull information that shows: item, description, and number of pieces per carton. The difficulty of it is: the fields that contain the unit of measure information can be in any order, or they might be blank. For example: item A might have PC per CT in row 2. And Item B might have PC per CT in row 3, or 4, or 5 or 6. Item C might not have pieces per carton, it may not come in cartons.

So ultimately what I want is - item/description/qty/unit of measure )if it is PC/ unit of measure if it is CT. Blank (packaging, but still item/description) if there is no carton conversion. However, I only want PC per CT, not PC per EA or something like that.

When done I want: ITEM: A/Description/QTY/PC/CT

You see why I did not want to post the long description of the issue?

 

[The_Doc_Man]

Further clarification is required here because it still isn't clear how this relates to a single item. What does the record actually look like as field-names? This whole question looks suspiciously like it is multi-dimensional and that will make the query that much harder to write. I am thinking that we need to know what IS predictable and what isn't.

Talking about rows, you make us think that this comes in from Excel (which isn't a barrier to making it work, but it is a fact we need to understand the mechanics of this problem.) What does it LOOK like?