Robin,
If you happen to have a relatively large number of fields needing transformation to normalized output, an alternative solution, not dependent upon unionization of a multitude of select queries, could be considered. This can prove all the more convenient if the number of de-normalized columns in source table is likely to fluctuate over time. In such a case there would be no need to modify any query. Simple editing of data in an ancillary table listing field names would suffice.
My sample db named TableNormalizationByPureSQL, demonstrates the approach outlined above. It is in access 2000 file format and is available at Rogers Access Library. Link:
http://www.rogersaccesslibrary.com/forum/forum_topics.asp?FID=45
Basically, the process involves Cartesian join between the de-normalized source table (say T_Source) and an ancillary table (say T_SourceFieldsConverted) which is a single field table holding the names of fields sought to be transformed to normalized status.
In your specific case, let the source table T_Source have fields ClubName, Posn1, Posn2, Posn3, Posn4 and so on. Create table T_SourceFieldsConverted having a single field named Position (text type). Populate this table with the names of de-normalized fields i.e. Posn1, Posn2, Posn3, Posn4 and so on.
Sample select query Q_TransformNormalized as given below, will display the normalized output. Against each ClubName and Position, PValue depicts the value held in the de-normalized column of source table.
If you wish to store the results in a new table (say T_ResultNormalized), it could be done via sample make table query Q_MakeTableNormalized, as given below.
Best wishes,
A.D. Tejpal
------------
Q_TransformNormalized
(Sample select query)
===============================
SELECT T_Source.ClubName, T_SourceFieldsConverted.Position, DLookUp([Position],"T_Source","ClubName = '" & [ClubName] & "'") AS PValue
FROM T_Source, T_SourceFieldsConverted
ORDER BY T_Source.ClubName, T_SourceFieldsConverted.Position;
===============================
Q_MakeTableNormalized
(Sample make table query)
===============================
SELECT T_Source.ClubName, T_SourceFieldsConverted.Position, DLookUp([Position],"T_Source","ClubName = '" & [ClubName] & "'") AS PValue INTO T_ResultNormalized
FROM T_Source, T_SourceFieldsConverted
ORDER BY T_Source.ClubName, T_SourceFieldsConverted.Position;
===============================
----- Original Message -----
From: Robin Chapple
To: MS_Access_Professionals@yahoogroups.com
Sent: Tuesday, May 31, 2011 09:04
Subject: [MS_AccessPros] Manipulating data.
I have a database that has been presented in the format:
ClubName - Posn1 - Posn2 - Posn3 - Posn4 - Posn5 - Posn6 - and so on.
I need it in the format:
ClubName - Posn1
ClubName - Posn2
ClubName - Posn3
ClubName - Posn4
ClubName - Posn5
ClubName - Posn6
How do I manipulate the original data?
Many thanks,
Robin Chapple
[Non-text portions of this message have been removed]
Tidak ada komentar:
Posting Komentar