How to re-order field data in a table

[David Mulholland]

I posted this in the Queries section and got a good start but I need to close
the loop (as it were).

I have a table (I cannot change the layout) with the following structure
(strID is the Primary Key):
tblPers_Awards
strID : Trans : Award1 : Award1Date : NbrAward1...Award40 : Award40Date :
NbrAward40

For each strID there could be data scattered thru the various fields...data
in Award1, Award 15, Award23…etc.

I have 2 separate table with the structure (AwardCat is the Primary Key):
tblCombinedAwards
AwardCat : Precedence

The only difference is in one (original) the Precedence field was set to a 3
char text field (001…300). I made a version that changed the Precedence to a
num (1-300) in case this was easier to use for this project.

Taking the tips from my first post, I took the tblPers_Awards and made a
normalized version with the structure:
tblAwards_Temp
strID : Trans : AwardCat : DateAwd : NbrAward : Precedence (<-- used the 3
char text version)

So here’s the question…
How do I re-order the fields for each strID to put the award sets info back
into tblPersAwards in descending order (based on that award’s precedence in
the tblCombinedAwards) starting with Award1?

I can see what needs to happen, I just can't wrap my head around how to get
Access to do this. Tips and suggestion would be greatly appreciated.

 

[Larry Linson]

Your data is not "normalized", that is, does not follow relational database
definitions. You have multiple fields for the same type of information,
which complicates the task of retrieving and using it. If you cannot change
the layout, nor generate a temporary table from that one, for your own use
(and I cannot see why you would not be a ble to do the latter), you are "up
the creek".

Your temporary tables need to be as follows:

TempTable1:

strID (assuming this is a unique id for the particular "Trans")
Trans (whatever a "trans" is in your existing table)

TempTable2:

unique ID (autonmumber would be fine)
foreign key to TempTable1 (copy of strID)
award identification
award date

That can be accomplished in VBA code, using DAO (or, uck, ADO) to read your
table, and generate from each of your records: one TempTable1 record, and as
many TempTable2 records as you have non-null Award fields in the original
record.

I guarantee you will find it easer to retriev what you want from the
temporary tables.

Larry Linson
Microsoft Office Access MVP