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

No can do on the table restructuring...it's dictated by the Department of
Defence :(

the structure is:

ID : Award1 : Award1Date : NumberAwarded1 : Award2 : etc... : NumberAwarded40

1 record for each employee...about 10k records total.

I thought about about a function, but I'm a neophyte compared to the brains
in here.

I'm toying with the idea of a temp table...work magic...delete the Award
Table records...append correct format

Any pointers/suggestions would be greatly appreciated.

"John Spencer" wrote:

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...
    ... Award: The value of one of the award fields ... 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. ... It has and ID fieldand 40 sets fields. ... What I want to do is reorder the data by the award precedence taking into account the various empty fields a record will have. ...
    (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)