Re: Hard to explain - multiple update fields query...

Ok, then try restructuring the data into a table you create in your Access database. Data structure - field and tables names - are all generic guesses.

Fields:
ID
Award
AwardDate
NumberAwarded
AwardPrecedence

You will need 40 queries to populate the table from your current table.

INSERT INTO TempTable (ID, Award, AwardDate,NumberAwarded, Precedence)
SELECT ID, Award1
, Award1Date, NumberAwarded1
, AwardsPrecedenceTable.Precedence
FROM [AWARDsTable] Left JOIN AwardsPrecedenceTable
ON AwardsTable.Award = AwardsPrecedenceTable.Award
WHERE Award1 is Not Null

Now you have the data normalized and can shove it back into your original table. Perhaps cleaning out the 40 sets of fields first using an update query to set all the fields in the set to null and then using an update query to populate the sets.

You could do one set at a time if you wish. Clear out set 1 and populate it, clear out set 2 and populate it.


STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you expect.
.

Relevant Pages

  • Re: Hard to explain - multiple update fields query...
    ... You would use an update query to get the data back into the AwardsTable ... UPDATE AwardsTable as A INNER JOIN TempTable as T ... would be to build a parallel structure of the AwardsTable in your Access database and do all the work there. ... You will need 40 queries to populate the table from your current table. ...
    (microsoft.public.access.queries)
  • Re: Table Value Update with Value in Another Table
    ... to generate the corrective action plan, ... to populate the area of work in the second table. ... Doug Steele, Microsoft Access MVP ... using an update query and set value as a macro. ...
    (microsoft.public.access.forms)
  • Re: How to pull a partial string from the middle of a field?
    ... Update query to populate them, and then delete this combined field once you ... Using similar logic, you can parse where the ", Address:" characters are ... string, and populate your other fields with them in your Update query. ...
    (microsoft.public.access.modulesdaovba)
  • Re: merge tables
    ... Dan @BCBS wrote: ... > then they used ita as a drop down to populate it. ... > select to a update query, what do I put in the Update To: ...
    (microsoft.public.access.modulesdaovba)
  • Re: Auto Populate Column
    ... Say I would like to populate ... in any given field within column cage will be dependent on criteria that ... would I have to do an update query for each ... able to update to the result of a Switch() function call; ...
    (microsoft.public.access.formscoding)