Normalization Tool

Normalization Tool

Introducing the “Transpose Tool” or “Database Normalization Tool,” your go-to solution for seamlessly converting spreadsheet-structured data into MS Access-structured data. This transformation not only equips you to construct exceptionally powerful MS Access databases, but is also a critical process exclusive to power users—those with an adept understanding of MS Access, often regarded as problem-solvers within an organization. If that’s you, you’re likely familiar with this technique. This tool aims to streamline and enhance the process, offering you a smoother experience!

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 1

Build Query Heads 1

Video 2 (4:24)

In this first 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.

Video 2 (4:24)

Nifty Access YouTube Thumb Nail
Play Video

In answer to an OP question on AWF Here:-

… …

Build Query Heads 2

Build Query Heads 2

Video 3 (10:05)

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 is above, See Video 1. It can also 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 3 (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 4 (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 4 (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 5 (5:41)

Our tool provides an insightful example, showcasing how to utilize the optimized database. This tutorial demonstrates how to answer a specific question, as illustrated in the third video: “Filter Fleet City to Return Rate.” This video answers the original poster’s question, leveraging the improved structure (see YouTube: Nifty Access – Transpose Excel to Access).

By separating elements like City and Fleet, these items can be looked up in combo boxes and the resulting data can be used to perform the required search. At first glance, transforming a simple Excel table into a more complex three-table system might seem counterintuitive, and perhaps like a great deal of work for minimal return. However, the benefits are immediately apparent. Opting for an unoptimized, un-normalized route is simply not the most efficient path.

The advantages and future simplicity your database offers far outweigh the initial extra effort. If you bypass this restructuring process when transitioning from Excel to MS Access, you might find tasks becoming increasingly challenging, eventually to the point where obtaining the desired results with a de-normalized structure becomes impossible.

The bottom line is, if you’re building anything beyond the simplest of databases, this step is non-negotiable. If you’re managing only a very simple database, MS Access may not be necessary—you might consider sticking with Excel. But for more complex tasks, our tool is here to simplify and improve your MS Access experience.

Video 5 (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

… …

More Useful Stuff HERE:-

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.