Hard to explain - multiple update fields query...

[David Mulholland]

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.

 

[KARL DEWEY]

It has and ID field(PK) and 40 sets fields (award, date, number awarded).
You need to change your table structure from a spreadsheet to that of a
database.

ID field(PK) - does this identify the employee?
Award - Text - foreign key - AwardList table
AwardDate - DateTime

I am not sure what purpose 'number awarded' serves.
all the data to the front of the table (in precedence order).

Table do not have a 'front'. What determines the precedence order?

 

[John Spencer]

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]

Yes...the field ID identifies the employee (and it is set as a primary key)

I have a second table that has the precedence order for the 'Award' field.

Number awarded simply shows how many of a particular award an employee has.

The table is set up as follows:

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

For each employee...they can have zero awards or awards scattered throughout
the various fields.

What I'm trying to do is formulate a plan to reorder the 40 sets of award
data so that they populate the fields in precedence order...starting with
Award1...

 

[David Mulholland]

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.

 

[KARL DEWEY]

No can do on the table restructuring...it's dictated by the Department of
Defence
Ok, so then use a union query to pull it together like this --
SELECT ID, Award1 AS Award, Award1Date AS AwardDate, NumberAwarded1 AS
AwardNum
FROM YourTableName
UNION SELECT ID, Award2 AS Award, Award2Date AS AwardDate, NumberAwarded2 AS
AwardNum
FROM YourTableName
UNION SELECT ID, Award3 AS Award, Award3Date AS AwardDate, NumberAwarded3 AS
AwardNum
FROM YourTableName
.......
UNION SELECT ID, Award40 AS Award, Award40Date AS AwardDate, NumberAwarded40
AS AwardNum
FROM YourTableName;

 

[John Spencer]

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.

 

[pietlinden]

Hopefully this will make things a bit easier. It basically writes the
repeating union queries for you...

Public Function GenerateUnion(ByVal intStart As Integer, ByVal intStop
As Integer)

'SAMPLE CALL: ?generateunion(1,3)

'sample result:
'SELECT ID, Award1 AS Award, Award1Date AS AwardDate, NumberAwarded1
AS AwardNum FROM YourTableName UNION ALL
'SELECT ID, Award2 AS Award, Award2Date AS AwardDate, NumberAwarded2
AS AwardNum FROM YourTableName UNION ALL
'SELECT ID, Award3 AS Award, Award3Date AS AwardDate, NumberAwarded3
AS AwardNum FROM YourTableName;


Const cSTRSQL = "SELECT ID, Award1 AS Award, Award1Date AS
AwardDate, NumberAwarded1 AS AwardNum FROM YourTableName UNION ALL "
Dim strsql As String

Dim intCounter As Integer

For intCounter = intStart To intStop
strsql = Replace(cSTRSQL, 1, intCounter)
GenerateUnion = GenerateUnion & vbCrLf & strsql
Next intCounter

GenerateUnion = Left$(GenerateUnion, Len(GenerateUnion) - Len("
UNION ALL ")) & ";"
End Function

I can't remember where the limit is on the length of a SQL statement
before Access barfs... so if you have to, you can create intermediate
unions. I did it for several months pretty much every day at my last
job, so been there, done that! This was one of the routines I used
all the time. A word of warning, though. Performance on union
queries is absolutely horrible, because you can't take advantage of
indexing. You could create a temporary table or a table in another
database and dump the result of the unions to that table and then
index that and summarize it and that would work...

Hope it helps a little.

Pieter