If you happened on this page perchance and are wondering what it’s all about, then I suggest you have a look at these links.
This “Transpose Tool” – “Normalization Tool” allows you to easily convert Spreadsheet structured data into MS Access structured data. This transformation of your data allows you to build incredibly powerful MS Access Databases. This transformation is a critical process, only known to and practice by “Power Users”, users with a good grasp of MS Access, usually the “Go To” person in your organisation for MS Access problems. If that’s you, then you probably already know of this technique, and I offer you this tool to smooth and improve the process!
… .. .. .. .. .. .. .. .. …
Normalisation tool in Action
Excel in Access - Transpose YEARS
Video 1 (4:38)
I created this video in answer to the question in this thread. Query for years of service — Basically the table resembles an Excel Spreadsheet. This columnar Data is not ideal for MS Access.The solution is to transpose the Excel like table into a new table, in a format suitable for MS Access. You can download the tool “Transpose Tool – Nifty Access” from “Sellfy” – press the download button below.
Video 1 (4:38)
More Info on Transpose YEARS :-
File Type: accdb Database1.accdb (328.0 KB)
Date:- ————– Downloads
2017_10_29 ——— 020
2018_07_20 ——— 107
2019_01_21 ——— 150
Using the Tool - In Pictures
Select the Unique "ID" Field
Select the field which represents the “Records” unique identity. This integer will enable you to link all of your tables back together again, and display the data in the original format. You might be wondering why you have to go through this awkward process, however as you can see in the above video, the benefits in the simplicity of creating your queries, reports and everything else you want to use/do with the data far out way this small Inconvenience, particularly if you catch it early enough in your database development.
Choose a two, or more column Table
You can copy the data to a two column table or 3 columns, in a table with more than two columns. A two column table would be for storing Data directly related to the field name. Where the field name identified the data, for instance a “true or false” situation like “Order Completed”
Two or Three Column Solution
In this case you have selected the three columns solution. This means you have a column for the ID; a column for the field name and column for the Data. The field name “conveys information” like in the example video above, the field name conveys the information “the year” and then whether the person was an active volunteer in that year.. “The Data”
Select the Field for the the Data
Again you are using the three columns solution. So now you need to select the field where you want the data to be placed. In this case, see again the example video above; the Data is whether the person was a volunteer for that particular year or they abstained.
This Tool helps convert a flat file MS Excel spreadsheet into a Relational set of Tables more suitable for use in MS Access. Looking back through my notes I realised that this was the original thread that got me started on creating the Transpose Tool – Split one table into 2 (relational) ones
If you have any problems using this form, of if you have a data set that you think should transpose but you’re not sure how to do it, give me a call…..
Date …………… Views
2010_08_23 … 516
2010_08_24 … 551
2010_08_25 … 576
2011_02_19 … 824
2011_10_19 … 1476