Update query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table (Table name:Scheduling) which has 11 fields F1, F2, F3, F4,
F5, F6, F7, F8, F9, F10 & Check. The field 'Check' is a Yes/No type field. I
have around 50000 records in this table, every record consists of a number
from 1-100.

I want to run an update query. The query should look for five numbers (7, 8,
24, 56, 91) in every field of all records if those five numbers are present
in either of the 10 fields, then update the 'Check' field with YES.
 
I would first add a primary key field [ID] and normalize your table with a
union query (quniScheduling)
SELECT ID, F1 as F FROM Scheduling
UNION ALL SELECT ID, F2 FROM Scheduling
UNION ALL SELECT ID, F3 FROM Scheduling
UNION ALL SELECT ID, F4 FROM Scheduling
UNION ALL SELECT ID, F5 FROM Scheduling
UNION ALL SELECT ID, F6 FROM Scheduling
UNION ALL SELECT ID, F7 FROM Scheduling
UNION ALL SELECT ID, F8 FROM Scheduling
UNION ALL SELECT ID, F9 FROM Scheduling
UNION ALL SELECT ID, F10 FROM Scheduling;

Then create a table [tblLookFor] with a single numeric field [LookFor]. Add
your 5 numbers to this table.

Then create a query like:
SELECT quniScheduling.ID, Count(tblLookFor.LookFor) AS CountOfLookFor
FROM quniScheduling, tblLookFor
WHERE (((quniScheduling.F)=[LookFor]))
GROUP BY quniScheduling.ID;

The CountOfLookFor will have the number matches for each [ID]. If you want
to update the Check field, you can append the ID fields that match your
requirements to a table of just the ID field as a primary key. You can then
create an update query with your appended table and Scheduling.
 
Back
Top