<-- Back to Current Category

A Guide to Creating an Excel Database

By: Paul Guenther, Fri Jan 8th, 2010

The time Microsoft Excelled Database has never actually sat very well with me since my foremost function in the, IT world has been in developing chronicle and what is used by the spreadsheeting fraternity is what I believe an over hyped slope. However, for the sake of peace, I want to outline some of the principles you must pursue in house what is known as the Microsoft Excel Database.

Let us assess ...

A memo in essence is what is considered a collection of information that is linked in some style. For example if you were running a group and promotion an effect you may have a catalog that just lists all the sales you have made over a period of time. Storing the figures in a record would make discern as the guests information and the promotion of an effect is linked and as such would be appropriate for the folder.

There are many different types of databases vacant such as Microsoft Access Databases, Oracle Databases, MySQL databases and so on but Microsoft Excel also has a form of a folder known as a slope file. The form of the file is nearly the same as the other databases as the records is under string headings in rows, but after that regular summit, the Excel file goes in its own control. See, to look for aspect numbers within microsoft Excel Database or Excel List we do not use the general file style of SQL, we actually use expressly printed functions. These functions are customs written by you and are known as criteria.

So how do we coin an outclass file...

Well first off, there is one regime we must always chase and that is one outclass record per catalog. Anymore and you just get yourself into loads of anxiety. In actuality if you necessity to have several outshine databases within your workbook cleanly put each surpass record against a divide table.

The next thing you must chase is that your catalog lists first row must restrict the direction of the roll. That is the first row contains your meadow names. Plus each of the take names must be 100% rare. You cannot have two tackle names with the same name or again you will have a directory that will not work.

The next copy you essential to be concerned with is identifying the subject names. Excel databases have an austere govern, the domain names or string names must be exclusive. Now the way you isolate them is easy, all you have to do is to ensure the domain names are many different facts types, configure, design etc to the breather of the folder in your lean. Generally what I do is to layout my province names in bold to persuade this requirement.

One of the most important system you must consider when you design a shine database is that around the row and columns of the fields and facts there must be a vacant row and discourse. What this means is that you can still have a title at the top of the fields, but there must be a perplexed row between the headline and the fields as well as along the last column as well. The break row manage also applies to the foot of the tilt as well.

When you are inflowing records into your record, every chamber in every record must delimit some quantity even if it is purely impassive (a uniform help is still considered a value) and each record must contain the same number of fields. If there is no limited facts for a domain you plainly entrust it break and move to the next arena.

Ensure that when you are entering figures into a sphere that you don't have spaces before the text or at the end of the text in the arena. If you do have spaces, then what will ensue is that taxonomy and seek for figures in the incline will be compromised and you will get unexpected fallout.

Upper argument and low rationale characters in the ground do not change the searches or place tips unless you specifically tell the Microsoft Excel application it is a rise. You can also use formulas in a group if required. Formulas can refer to cells within the Excel Database List or unlikely of the Excel Database.

Note also that you can correct and format the cells just like any other spreadsheet however the deliver you must consider as a priority is that the pasture names must have a different format to the surplus of the figures in the database slant. It is extremely recommended that there be no other formatting in the list excluding for the arena headings. The ensures that there are no miscalculations by the application as to what is a turf banner in the excel database and what is not.

Now that you have setup your list in this way following the policy, you are now eager to interrogate the list by applying criteria. The easiest way that you can do this is by with the Form dialog box. To get into the Excel Database form you plainly wish the Data menu and then show Form from the drip down menu.

From the Excel Database Form you can purely prefer the Criteria badge, font the criteria you have for your data and choose the Find Next switch and it will take you to the first record that satisfies the criteria you are probing. Excel Databases are particularly useful for abrupt data that is where the book of records you have in your database doesn't exceed 65,536 rows.

Learn more about Database Programming and Database Storage Virtualization at Database Connect.