Repeat an update query for multiple fields in a single table

[PRANerd]

I have a table with 13 fields of information, filed 1 is the item name, and the remaining 12 fields are numerical information about that item. Some of the data in the numerical fields contain undesirable values (eg: "-2", and "-3")....

I'm new to Access, but i can see how to write an update query to update a single field to "0" where "-2" or "-3" is found, but when I tried expanding that query to handle all 12 fields it failed.

I know access can achieve this, I'm just too new to recognize the best way. I'm hoping there is a better way than writing 12 different update querries.

thanks.

[zmbd]

1) Check out normalzation > Database Normalization and Table Structures.

2) Please click on the [CODE/] button above and paste your SQL between the [CODE] [/CODE] tags so that we can review it.

[PRANerd]

UPDATE [MR-FUNCTION_Results]
SET [MR-FUNCTION_Results].[1CDF-E12-5 RAW] = 0
   , [MR-FUNCTION_Results].[1CDFSEQS RG RAW] = 0
WHERE ((([MR-FUNCTION_Results].[1CDF-E12-5 RAW])=-2)
   AND (([MR-FUNCTION_Results].[1CDFSEQS RG RAW])=-2)) 
   OR ((([MR-FUNCTION_Results].[1CDF-E12-5 RAW])=-3) 
   AND (([MR-FUNCTION_Results].[1CDFSEQS RG RAW])=-3));

[zmbd]

Here is the lesson on fishing first:
Create a simple select query, use the wizard it's fast and easy.
In this query, make sure that only the records you want to effect are returned. Once that is done, then convert to the update action query.

So for the fish:
The way your query is written:
BOTH fields [1CDF-E12-5 RAW] AND [MR-FUNCTION_Result s] must = -2
OR
BOTH fields [1CDF-E12-5 RAW] AND [MR-FUNCTION_Result s] must = -3
within the same record in order to be returned within the select query (or effected in the update)

However, it sounds as if you want
field [1CDF-E12-5 RAW] when it = -2 or -3
or when
field [MR-FUNCTION_Result s] when it = -2 or -3
to be eventually

Then you need a query like this for the select:

SELECT [MR-FUNCTION_Results].[1CDF-E12-5 RAW]
   , [MR-FUNCTION_Results].[1CDFSEQS RG RAW]
FROM [MR-FUNCTION_Results]
WHERE ((([MR-FUNCTION_Results].[1CDF-E12-5 RAW])=-2)) 
   OR ((([MR-FUNCTION_Results].[1CDF-E12-5 RAW])=-3))
   OR ((([MR-FUNCTION_Results].[1CDFSEQS RG RAW])=-2)) 
   OR ((([MR-FUNCTION_Results].[1CDFSEQS RG RAW])=-3));

This then converts to the update query:

UPDATE [MR-FUNCTION_Results] 
SET [MR-FUNCTION_Results].[1CDF-E12-5 RAW] = 0
   , [MR-FUNCTION_Results].[1CDFSEQS RG RAW] = 0
WHERE ((([MR-FUNCTION_Results].[1CDF-E12-5 RAW])=-2))
   OR ((([MR-FUNCTION_Results].[1CDF-E12-5 RAW])=-3)) 
   OR ((([MR-FUNCTION_Results].[1CDFSEQS RG RAW])=-2)) 
   OR ((([MR-FUNCTION_Results].[1CDFSEQS RG RAW])=-3));

(this SQL has been vetted in ACC2010)

Additionally I highly advise you to remove the dash/subtraction sign from your names "-" this is a reserved token and can cause you issues.
Access 2007 reserved words and symbols
AllenBrowne- Problem names and reserved words in Access

[ADezii]

1. Here is some compact Code that will check Fields 2 thru 13 of every single Record in a Table named tblData. If a Value of either -2 or -3 is found in any of these Fields, in any Record, it will automatically Update that Value to 0. You need not know any of the Field Names, only that you are processing Fields 2 thru 13.
   

   Dim MyDB As DAO.Database
   Dim rst As DAO.Recordset
   Dim intFldCtr As Integer
   
   Set MyDB = CurrentDb
   Set rst = MyDB.OpenRecordset("SELECT * FROM tblData", dbOpenDynaset)
   
   With rst
     Do While Not .EOF
       'Skip the 1st Field, only process Fields 2 thru 13
       For intFldCtr = 1 To (.Fields.Count - 1)
         If .Fields(intFldCtr) = -2 Or .Fields(intFldCtr) = -3 Then
           .Edit
             .Fields(intFldCtr) = 0
           .Update
         End If
       Next
         .MoveNext
     Loop
   End With
   
   rst.Close
   Set rst = Nothing

2. tblData before processing:
   

   ID	 2	 3	4	5	6	7	8	9	10	11	12	13
   1	 2	-2	4    -2	6	7	-2	9	10	11	12	-3
   2	-3	15   16	17	18	19	20	-3	22	-3	24	-3

3. tblData after Code Execution:
   

   ID	2	3	4	5	6	7	8	9	10	11	12	13
   1	2	0	4	0	6	7	0	9	10	11	12	0
   2	0	15	16	17	18	19	20	0	22	0	24	0

4. Kindly forgive the poor formatting. I did not have time to format the Table Values properly.

[PRANerd]

About "-" usage

Thanks for the tip about using the "-" is names - I did not know that this would be a reserved token.

Is your recomendation for table names, or also for field names within a table?

Also, does using the underscore "_" have the same implicaitons?

thanks

[PRANerd]

Dont know what a code tag is

UPDATE [MR-FUNCTION_Results]
SET [MR-FUNCTION_Results].[1CDF-E12-5 RAW] = 0
   , [MR-FUNCTION_Results].[1CDFSEQS RG RAW] = 0
WHERE ((([MR-FUNCTION_Results].[1CDF-E12-5 RAW])=-2)
   AND (([MR-FUNCTION_Results].[1CDFSEQS RG RAW])=-2)) 
   OR ((([MR-FUNCTION_Results].[1CDF-E12-5 RAW])=-3) 
   AND (([MR-FUNCTION_Results].[1CDFSEQS RG RAW])=-3));

You request that i use code tags in the future - what is this? (did i mention i was new to Access?)

[zmbd]

Fully explained here:
> Before Posting (VBA or SQL) Code :: Section B paragraph 2

This is a good page to read in its entirety as it covers some basic troubleshooting that can save you time if followed prior to posting questions (^_^)

[PRANerd]

Fully explained here:
> Before Posting (VBA or SQL) Code :: Section B paragraph 2

This is a good page to read in its entirety as it covers some basic troubleshooting that can save you time if followed prior to posting questions (^_^)

Awesome, will do. Thanks for the tip!