Normalization Tool

Normalization Tool

This “Transpose Tool” – “Database 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!

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.

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” from — Nifty Access” – press the download button below.

Video 1 (4:38)

Nifty Access YouTube Thumb Nail
Play Video

… …

Build Query Heads 2

Build Query Heads 2

Video 2 (10:05)

In this video I demonstrate how to normalise some data imported from Excel. This consists of breaking it up into 3 separate tables. I then demonstrate how this normalised data can be drawn back together again into a useful and usable table. I then demonstrate how to use a cross-tab query to produce a particular output required.

The form “frmTranspose” (shown in the video) is available from my website CLICK HERE with further information on issues. It is very handy for turning denormalized data into normalised data for use in MS Access.

A precursor to this video can be found on YouTube here:- Build Query Heads – Nifty Access where I do a presentation showing how to convert the data imported from a spreadsheet into data suitable for MS Access.

Video 2 (10:05)

Nifty Access YouTube Thumb Nail
Play Video

In answer to an OP question on AWF Here:-

… …

Transpose Excel to Access

Transpose Excel to Access

Video 3 (11:11)

OP rasras123 ask a question on Access World Forums (AWF) which indicated that the table structures being used were not optimised for MS Access. In answer to the question I created a table in the unoptimised format as indicated by the OP, and used my Transposition Tool to convert the the unoptimized table into 3 tables, suitable for taking advantage of the advanced features of MS Access. As indicated by MajP, there is an alternative the OP could use which is also shown in the thread.

Video 3 (11:11)

Nifty Access YouTube Thumb Nail
Play Video

Original Question on Access World Forums (AWF)

… …

Filter Fleet City to Return Rate

Filter Fleet City to Return Rate

Video 4 (5:41)

Filter Fleet City to Return Rate – Shows how to use the the optimised database created in Video No# 3 above to answer the specific question asked by the OP. In this video:- I show the answer to the actual OP Question, based on the new, improved structure. (See YouTube:- Nifty Access – Transpose Excel to Access) You can see, because I have separated out the City and the Fleet, now those items can be looked up in combo-boxes and the results used to perform the search required. Converting a simple Excel table into a complicated three table system seems counter intuitive, it seems like a lot of work for a very little benefit. However, I hope you can see straight away the benefits, it’s just not good to go in the un-optimized direction, the un-normalized route.. What’s more is the benefits, the simplicity of anything you do with your Database in the future far outweighs the initial extra effort. In fact, if you don’t follow this restructuring process of your MS Access databases, especially when moving from Excel to MS Access, then you will find things become more and more difficult, eventually to the point where you just cannot get the results you want with the de-normalized structure… The point is, if you are building anything but the most simplest database, then you just cannot ignore this step… And really, if you’ve only got a very simple database, then it shouldn’t be in MS Access, you should leave it in Excel…

Video 4 (5:41)

Nifty Access YouTube Thumb Nail
Play Video

Original Question on Access World Forums (AWF)

… …

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

… …

Leave a comment

This website uses third-party software - WordPress Add-Ins to be exact. I don't know what any individual add-in does, but I'm sure that many of them collect information about you. So be aware, if you continue using this site, then you are likely to be sharing your information. I don't know how to disable this sharing for any, or all of the plugins for individual users. So I can't stop the sharing of information. If this worries you then please do not use this site... If you continue to use this site I will assume that you are happy with it.

Do you need a hand in the right direction?

You are in the right place.