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

My idea would be to restructure the tables if at all possible.
You would have an additional table with at least four fields

Field : Content
AwardID: Some value for the primary key of this table
ID : The value of the PK field in your current table
Award : The value of one of the award fields
AwardDate : The corresponding date field for the above award
Number Awarded: The corresponding value for the above award

A record for each PK and award in your original table. Once that is done life would be much simpler.

If you can't build the new table, then you will need to write a VBA function to reorder the awards within the record. You might be able to do this with a series of SQL queries, but life will get tough if you have filled up all 40 sets of fields.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

David Mulholland wrote:
I'm starting to toss around ideas to see how I can accomplish this:

I have a SQL table for awards that I connect thru ODBC. It has and ID field(PK) and 40 sets fields (award, date, number awarded). Over the years there has been different ways of how these awards were entered:
-sometimes sequentially thru the fields
-sometimes by type (certain awards go in certain fields)
-sometimes in any order the puncher felt like

Needless to say, the data is in disarray. What I want to do is reorder the data by the award precedence (which I have a seperate table made) taking into account the various empty fields a record will have. Basically to push 'up' all the data to the front of the table (in precedence order).

Any thoughts/ideas on how I might tackle this? I await your genius. :)
.

Relevant Pages

  • Re: Hard to explain - multiple update fields query...
    ... I'm toying with the idea of a temp table...work magic...delete the Award ... series of SQL queries, but life will get tough if you have filled up all 40 ... fieldand 40 sets fields. ... data by the award precedence taking into ...
    (microsoft.public.access.queries)
  • Re: Hard to explain - multiple update fields query...
    ... FROM YourTableName ... I'm toying with the idea of a temp table...work magic...delete the Award ... fieldand 40 sets fields. ... data by the award precedence taking into ...
    (microsoft.public.access.queries)