Normalization Tool

Normalization Tool

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.

Normalization 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 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:- —————– Views
2017_10_29  ——— 20

… …

Using the Tool - In Pictures

ALL Controls are Disabled ......

Image 1

When you first open the Form all of the controls are disabled, except for the first Combobox. 

Image 1

Select the Table

Image 2

Select the table you want to transpose data from. Note:- the combobox only shows tables with names prefixed “Xtbl”

Image 2

Select the Unique "ID" Field

Image 3

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.

Image 3

Select the Fields from the Listbox

Image 4

Selecting the unique “ID” triggers the list box to fill with names of the fields from the table. Select the fields that you want to be transposed from the old table into the new table

Image 4

Select the Table to Transpose to

Image 5

The names of tables prefixed with “Ytbl” will appear in this combobox select the table you want the data to be transposed into from this combobox.

Image 5

Select the Field to store the ID

Image 6

Now select the field that stores the record ID that matches the new table to the original Table

Image 6

Choose a two, or more column Table

Image 7

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”

Image 7

Two or Three Column Solution

Image 8

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”

Image 8

Select the Field for the the Data

Image 9

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.

Image 9

Press "ONLY ONCE!"

Image 10

All of the necessary selections have been made. You just need to press the button to complete the process of filling the table. Don’t press the button more than once otherwise you will double or triplicate the data placed in it.

Image 10

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…..

Cheers Tony…

 

Date …………… Views
2010_08_23 … 516
2010_08_24 … 551
2010_08_25 … 576
2011_02_19 … 824
2011_10_19 … 1476

… …

Easy MS Access Checklist

  Checklist Required! Checklist Required! Video 1 (1:27) I offer a solution to a problem which might, sort of, sneak up on you when you’re developing

Read More »

More Useful Stuff HERE:-

Leave a Comment