Friday, 26 September 2014

MS Access Vinyl Database 4 - Creating Relationships Between The Tables

In any database there will be certain relationships between the tables. For instance in our vinyl records database Elvis Presley released many records. Therefore there will be many data records in the t_music table that need to reference the data record for Elvis in the t_artist table.

Creating a relationship at database level is optional, but it will do three things:

  1. It will save us from having to state the relationship in every single query we create
  2. It will stop us from entering a music track with a reference to an artist who doesn't exist in the t_artist table
  3. It will stop us from deleting an artist who has music tracks - otherwise that would leave what are called "orphan" records in the t_music table

To create our relationships, open the database and first of all click on Database Tools on the menu bar and then click the Relationships tool as shown.

The above screen window will open. We now need to select and add all of the tables shown. Either add them one at once, or click t_artist, then hold down the [Shift] key and click the bottom table. This will select them all and then clicking the Add button will add them all at once.

We now have a screen with all the tables added. They will all be the same height at first which means we will not be able to see all of the fields in the t_music table. To extend the t_music box click on the bottom border and with the mouse button still held down, drag the mouse downwards to expand the box as shown.

All of the relationships we are going to create involve the t_music table, so it makes sense to have that in the centre of the screen. You can move the tables about by clicking the title of the table and dragging.

First move the t_label table to a spot underneath the t_artist table and then move the t_music table into the space it has left in the middle.

Now to create our first relationship. We'll start with the t_label table and click and drag from the label_ID field upwards and to the right, releasing the mouse button whilst the mouse is hovering over the music_label_ID field of the t_music table. Make sure you do it in this direction and not by starting from the t_music table. Record labels release many music tracks. We don't want each music track to be able to have many labels...

The Edit Relationships dialogue box will open at this point. First of all, check that both label_ID and music_label_ID appear in the small table in the middle as shown. If all looks ok, click the Enforce Referential Integrity tick box. This is the factor that will stop you later from deleting a label with tracks existing in the t_music table. Otherwise by deleting the label you could leave the music tracks sort of floating in limbo and wondering which label released them... It will also stop you from adding a music track with a bad or non-existent reference to a label. For example; if you had 21 artists in the t_label table but tried to enter "47" as a label reference in the music_label_ID table. In a later article we'll create a form to enter the data in anyway and that will allow us to pick the label from a list of existing ones - you won't need to remember any artist_ID references and if you even try to enter a bad reference MS Access will tell you that you are being stupid... When you are certain everything looks right, click the Create Button.

Your screen should now look like this. A line appears between the two fields that have the relationship and it should show "1" against the t_label table and the sign for infinity "∞" against the t_music table.

In the same way, create relationships between the table t_media and t_music and finally between t_artist and t_music. Remember: always click and drag to the t_music table, not from it.

Our relationships are now in place. They should be treasured as should any good relationship!

Wednesday, 18 June 2014

MS Access Vinyl Database 3 - Creating a Menu Form

At the end of the previous article I optimistically said that in the next article (i.e. this one) we would create both a menu screen and our first data entry screen. Far too ambitious for a single article! We'll create a menu form for the music database in this article - but the only button on it will be the Quit button and therefore the only thing it will do until the next article (probably the next couple) will be to close both itself (the form) and Access itself. But think of the fun you can have with that!!!

This is going to take a while - I'd suggest you have a good hour set aside. Did I mention we are going to totally ignore Wizards in this series of articles? We're going to be doing some whizzy stuff alright, but far easier to do it from scratch than have Microsoft Wizards thinking they know what you want and then you having to change it all about...

Ok, the first thing we have to do is open our database either by double-clicking the database file icon or by opening MS Access and then opening it from the list on the right of the opening screen.

Once it is open we'll create a blank form.

Start off by clicking on the word "Create" and then click on the icon labelled "Form Design".

This will create a blank form with the default name "Form 1" and we'll get round to changing the name in a moment. First of all let's take a look at the screen. The bits of our form that we want ourselves or other users to see once it's working is under the label Form 1 currently highlighted in orange. There is a single block under the heading "Detail" which is the main blank canvas that we can work with.

Over on the right hand side is the Property Sheet. This has a range of headings and values relevant to what is shown in the first box under the heading "Property Sheet". It is currently showing "Form" so we are seeing the properties for the entire form. Later once we start to add items to the form we can view their individual properties in this column.

There is currently no caption given, so by default if the form was in normal display (rather than the Design view that we are currently looking at) the tab description would show the form's name. We want it to show the text "Music Menu" so click into the cell to the right of the one saying "Caption" and type "Music Menu".

I know - you've just done something really great and want to see it in action, right? So move the mouse cursor top left and click to drop down the list of possible views as shown. We want the Form View but as you can see, there are a few possible different views. Design View is highlighted because that's the view we are currently in. Click Form View.

Fantastic! The tab heading says "Music Menu" or whatever you thought you could sneakily call it without me knowing!

However there are a couple of bits present by default that we won't need to have showing on the form. No.1 is the record selector. Usually a form is for showing records from the database and clicking in the record selector on the left will allow you to do something - like delete it... We won't have any need for that because this is going to be a menu screen and won't have any table or records associated with it. No.2 is the navigation buttons, also used to move forwards or backwards through a set of records. We don't need those either on this form.

Go back top left and return to the Design View. We now need to make a few changes to the Property Sheet for the form. I've altered a few and the diagram above shows what we want them to read. First off I've changed a few of the possible views from Yes to No, only leaving the Form View. Further down I have changed both Record Selectors and Navigation Buttons to No. Because we are not going to design a menu screen that is too big to be seen all at once on your monitor I've also turned Scroll Bars to the option Neither.

Test things out by going back top left and clicking for the Form View. When you have had enough of looking at a blank form without record selectors, navigation or scroll bars, come back to the Design View.

Ok. Having got back to the Design View, I want to show you how Access reacts to the context of what you last clicked on. We'll do this by right-clicking in a few different places.

Right-clicking the tab or the blue bar to the right of it allows you to select options based on the entire form. We can save it (don't yet...), close it, close all open objects, or move between the different views. Because we just turned some of them off in the Property Sheet, the only ones now available are the Form or Design Views.

Right-clicking the box with the black square (a handy box to select the form properties into the Property Sheet) or the horizontal or vertical rulers gives a second set of options. We'll look at events later in this article and at Tab Order once we create a form based on a table. The Properties option toggles the Property Sheet on and off. In my experience I always want it to be visible.

Finally I've put the mouse cursor into the blank block representing our form's canvas. A right-click here opens a list with some of the options we've already seen but there are a few new ones and we're going to test out a couple of them next. Note that we can toggle on or off the ruler and grid lines. Both are highlighted because both by default are on. I'd strongly suggest leaving them on. Particularly on forms with lots of objects, the facility to have them snap to the grid lines is very useful.

Feeling artistic, I now want to change the colour of the canvas and therefore I'm going to change the Fill/Back Color and choose a medium grey (always the adventurer!). We will use colour - some of it quite soon, but database screens should never look like a 1970s shirt... This canvas is where all the buttons to open more useful forms and reports will go.

Note, now that you can see all the bottom options that there are two header/footer options and we can again toggle the Property Sheet on or off.

The Form Header/Footer puts a separate bit of canvas up at the top and down at the bottom of the form. The Page Header/Footer does the same sort of thing, but if you had a table with so many fields that it wouldn't fit on a single screen, you can then split it onto more than one page instead of scrolling down and have the same header/footer on all of them.

In fact we'll enter a Form Header/Footer right now. The canvas has changed colour to the one we selected, I've right-clicked the canvas again and now I choose the option for Form Header/Footer.

I'm actually only going to use the footer on this menu, so I can position the mouse cursor on the top of the Detail bar and click-and-drag it all the way up to the top of the Form Header. The heading bar for the Form Header will still be there in the Design View, but in the Form View it simply won't appear. Go and have a look if you like! Come back to the Design View when you're done.

I'm going to change the colour of the footer area now by right-clicking with the mouse cursor in the footer area and I'll change it to a dark blue. Use what colour you want, but remember it - if a database is to look professional it will use colours consistently, so you are choosing the colour of every single form here... It will also have light grey buttons on it so it should contrast nicely with those and not clash with the darker grey of the main canvas.

We are doing well. There's only a couple of things left to do - create a button to close the database, and save the form. That's going to take as much instruction as you've already waded through...

Click the Design tab on the Menu Bar at the top of the screen and then click the option for a new Button (both highlighted with a pointing finger above). Having clicked to create a button the next click will become the top-left corner of your button and then keeping the mouse button down you will click-and-drag to the bottom-right of where you want your button to be. Remember to start this in the footer element of the canvas. A Wizard window will open, as shown, as you finish click-and-dragging to size your button. You are allowed to shudder before clicking its Cancel button!

Notice that by default the button you created will have the snazzy caption "Command1". If you look in the Property Sheet, this title also appears in the text box at the top of the Property Sheet, so the properties now being displayed are those of the button, not the entire form...

If you have inadvertently clicked elsewhere and the Property Sheet is not showing the button's properties, just click back on the button before doing anything else. (See the pointy finger)

Amend the Caption to read "Quit Database". There's actually two ways to do this. You can type in the cell next to the word "Caption" in the Property Sheet, or you can simply single-click over the text on the button until it lets you type directly onto the button. Your choice, they both do the same job.

Back in the Property Sheet I've changed the Font Weight to Bold and the Fore Color to bright red by typing a hash (#) followed by the Hexadecimal value "FF0000". We'll use a few of these colour codes in later articles. I'll do an article to explain them in the next couple of days and then link to it here.

One other thing to do now. The "name" of our button as far as Access is concerned is still the less-than-meaningful "Command1". It will be easier when we have lots of buttons on this menu to be able to see what they are instead of remembering what Command4 and Command5 do.

We've not touched the row of options in the Property Sheet as yet as the Format tab has done all we needed so far. Now though we need to click the Other tab which will change the list of properties below the tab row. In the top row - against the heading "Name" we can now type a meaningful name: "btn_quit". All of our buttons will have the prefix "btn" and an underscore.

Our button now looks the biz (go and check the Form View if you like) but clicking it will do absolutely nothing just yet.

We now need to build an event for the button. This will be a Visual Basic procedure that instructs Access what to do when the button is clicked. To do this we need to select the Property Sheet tab for Event and then drop down the On Click property and choose the [Event Procedure] option. Now click the button with the three dots (...) next to the drop down box. A new window will open for Visual Basic.

Now this could get complicated, but I'm going to ask you to take me at face value for now and I'll keep it as simple as I can. Visual Basic is a programming language. It will do much more than manipulate things in Access. It operates as a collection of routines, each one labelled a Sub (sub-routine). Each sub has a name. The name in Access is the name of the control (in this case a button) and the action. In this case the action we have specified is a mouse click. We could specify a double-click, a right-click, the mouse cursor rolling over the button.... and so on. Each line of the sub is read and actioned in turn and each sub-routine ends when it reaches a line that says "End Sub".

A closer look at the same screen. The sub is named "btn_quit_click()". The vertical bar is actually the mouse cursor which takes this form in Visual Basic. The beginning and end of the sub are created but no actual instructions have been entered because Access has no idea what we want the button to do yet.

We only need a single command in this sub which tells Access to close down the database and itself. The syntax "DoCmd" tells Access that a command is coming. It can be typed "docmd" and the fact that it will change automatically to the version with capital or upper case characters tells you that you have typed a command that Visual Basic recognises. As you type the following dot (full stop) a list of possible commands will open as a drop down list. You can either scroll through these and bore yourself, or just type the command "quit". It's at the point where you move the cursor onto another line, either with a mouse click, carriage return or arrow key, that the text will capitalise if the command is recognised.

The indentation of the command text was intentional. It helps quickly identify where the start and end of a routine is and also I'll use indentation when we come to use If...then statements. It helps no end when you come up against a sub not doing what you expected and you have to figure out why!

Before anything else we now need to save the Visual Basic routine. Click the Save icon as shown above. This will require the form itself to be saved also and a window will open asking for a name.

The default name of Music Menu will be taken from the Caption property that we changed way back. But if you remember the previous lesson where we named all our tables "t_" followed by the name, you'll not be surprised that we are going to call the form "f_music_menu". Amend the text and click Ok.

And that is it for this time! Click the Form View icon to have a last lingering look at the new form in all its glory.

It should look like this. The buttons for opening data entry forms and reports will go in the dark grey area and the button to close everything down should work. Go on... give it a click!

If everything disappears, the form has worked! Try it again by reopening the database and you can run the form by double-clicking the form name in the list of database objects.

I hope this was fairly easy to follow. Let me know! This was the easy form - once we start on the more complicated forms there will be different ways to present data fields for direct entry, drop down lists of allowed data content and look-ups to other tables to verify data. For each form there will be a button on this menu form that will open it and at the same time turn the menu invisible. An exit button on each data form will close that form and turn the menu back visible.

It's going to take a few weeks/months to do everything, but I hope you stay with me! By the end you will be able to use the techniques to create any other sort of database. Just be aware that if you intend to store personal data about people you may need to be aware of the Data Protection Act if in the UK or any similar legislation elsewhere.

Friday, 13 June 2014

MS Access Vinyl Database 2 - Creating Tables

This series will take a little time to build as I decided I should start from scratch for the blog. My advantage is I can copy from what I've already done and import data, but starting from scratch will allow me to avoid some of the inconsistencies that exist in my current database.

In this article we will create the database for our music collection and then create the vital tables - the "storage jars" for the data about our music.

Microsoft Access is the database tool that comes with Microsoft Office. If you don't have this then this series of articles is going to be a touch difficult to follow...

Open Access and then create a new database based on the Blank Database option as shown - simply double-click this icon.

Give your project a name - I've called mine "music" and I'm storing it in a sub-folder called "ACCESS".

The first thing to do is to create our first table. Click on Create and then on the Table icon to the left.

The new table will open in Datasheet View. This looks a little bit like an Excel spreadsheet (Access can be thought of as basically a series of linked spreadsheets).

This method of creating a table is a little dangerous and we are not going to enter any data yet until we have defined the fields of the table. If you had a table for cars, for instance, the fields would hold the make, model, colour etc.

So straight away click the View icon as shown. This will be currently showing the icon for the Design view and this is the one we want. You could also click the little triangle (down arrowhead) below the word View and then click the option for Design View.

A small window will open asking you to give a name to the new table. We are going to create the main table first - the one that will hold details of all the music tracks you have in your collection. Probably the most important table that the database will contain. By the time we finish we will have tables, forms, queries and possibly reports. You might want to name them all along similar lines but that carries a risk of confusion. If we simply called this table "music" and then also called the form (the window for entering data into the table) "music" and then called a query the same name as well, then it's possible we may be faced with a list of database objects where there are three objects called "music" and we don't know which is which.

This may sound a bit complicated at this stage and I'm sorry if it does, but to get round this problem I'm going to name every single table with a "t" and then an underscore before the more meaningful bit of the table name. So instead of "music" we'll name this table "t_music" and when we get to creating the forms and queries they will be "f_name" and "q_name" and then in any list we can tell which is the table and which is something else. Just take my word for it at this stage. You could be glad you did at some point in the future!

By the way, I'm old-fashioned - I know you could have spaces in your table name instead of using underscores, but again there's a risk somewhere that you mix up a table name with the following word or cut one short leaving out a word, so using underscores is a safe approach.

Ok, I've made you read all that when all you needed to do was type t_music and click the Ok button! Moving on...

A few things to say here. All of the text you see in the two columns labelled Field Name and Data Type needs to be either typed in or defaults into the right hand column as you add the field name and will need amending in some cases to match the diagram above.

Every table will have a main ID field at the top and for this field (and for this field only) it needs to be designated the Primary Key. By default the top field will be named ID and will be the primary key. This means it will hold an automated number and each record will always have that number and if a record is ever deleted then that number will never be allocated to a new record. Think of it this way: in a table of four records if record number 3 gets deleted then what was record number 4 becomes the third record. But the primary key will not change either for the old record 3 or old record 4. If you listed the records in the order they were entered, the ID field would read 1,2,4. A safety feature. Very necessary as that field will be the link between tables.

Rather than have to type "Parlophone" in the label field for every record released by that company, we will be able to store just the number that refers to the label Parlophone. It saves space. And if you have a large record collection of thousands of tracks it will also save you a lot of time. And no... you won't have to remember, or ever see, the numerical ID.

Whilst all ID fields can be numerical, you may want at some time to specify your own codes for something. I've done that for the Media ID. The media field will hold details of whether the music is on a 7 inch 45 rpm vinyl, a music cassette, an album or is a digital file on your PC, laptop, iPad, phone or whatever. So against the music_media_id you can see that this is a text field, not a number. At the bottom of the window, where the default for a text field would be 255 characters, I have made it 2 characters. This will appear in "Field Size", the top row in the bottom part of the window.

Last point to make here: I've labelled every single field with the prefix word "music" and an underscore. Other tables may have a similar fieldname - the Artist table will have a field called artist_ID and to avoid confusion we don't want there to be two tables having fields with the same name. So the prefix makes it music_artist_ID and it will save us heartache in the future.

We've created the first table - click the Save icon in the top left corner of the screen next to the Windows icon. Not necessary, but again it's good practice: close the table by clicking the X on the right of the window just to the left of the words "Property Sheet".

I've covered a lot here, but to make it worthwhile for you, let's create some of the other tables now to finish off this article. We'll start with the media table. Once again go to Create > Table, then click the Design View icon and name the new table t_media.

This is the table mentioned earlier where you need to change the primary key from AutoNumber to Text. Then change the field size from "255" to "2". Make sure that the Primary Key icon is lit up as shown and against "Indexed" in the left column of the lower window it should read "Yes (No Duplicates)" in the right hand column.

Click the Save icon in the top left corner of the screen next to the Windows icon and close the table by clicking the X on the right of the window just to the left of the words "Property Sheet".

Now for a table to hold details of record labels. Again, only two fields are needed: the code and the name. Name this table "t_label". Make sure you save it once you have defined it. Refer to instructions above if you need help remembering how to create the table.

Last table for now. This is called "t_artist" and will hold details of artists, both individual singers and groups/bands. All the fields we need for now are the ones shown. A code, the name of the artist and the name as it would be used in an alphabetical sort.

Just to give an example here. An artist with the name "Elvis Presley" would have the text "presley elvis" stored in the field artist_alpha. We will use this field to sort records so Elvis Presley appears correctly with other surnames beginning with P rather than just after Elvis Costello! In the same way "The Beatles" would simply have "beatles" in the artist_alpha field so that they appear under B in the alphabetical list.

Click the Save icon in the top left corner of the screen next to the Windows icon and close the table by clicking the X on the right of the window just to the left of the words "Property Sheet".

Ok, I know you must be dying to start entering data into these tables but there is a risk in just entering data straight into a table. It's easy to add an extra field by mistake or delete a table instead of deleting a single record (row). If you really must do this now, I suggest you do it only in the t_media table.

Open the table by double-clicking it in the list of tables on the left of the screen. Add whatever records you need to enter. Some will wonder what a 78 rpm record is (read my other blog...) Others may have a collection of even older wax cylinders. You may want to catalogue video files...

In the next article we'll create the first form based on one of the simpler tables and also create a special form that will be used as a menu screen. This menu form will be used to open all the other forms and to select report output. We'll also build buttons onto it to close the database down so that when using the database you won't need to be an expert in Access. In fact you'll be able to let people use it who have no idea what Access is!

Wednesday, 11 June 2014

MS Access Vinyl Database

I know it's a while since I posted here, but thought I'd start a project that made for a few entries and having retired recently and started to catalogue my vinyl and (being honest) shellac collections, I thought there must be a few other people out there with large collections of music on black discs and trying to keep track of them.

There's no details in this post but the intention is to get you interested by showing what I intend to do. The main menu screen as yet does not have a great many items, but they do all currently work!

This will log music on all formats - 78s, 7-inch, LP albums, CDs, tapes, 8-tracks and digital files. You can have multiple tracks on albums and you can have an album artist different to track artists.

The main data entry screen currently looks like this. Artists are entered in the top box and the bottom window will only show music tracks by the displayed artist. On this page you can associate tracks with an album. Track number (if appropriate), label, format, storage box reference and the disc's own catalogue number can be stored.

You can also store the file path to digital music tracks (for both A and B sides) so that these can be played directly from the database. Clicking the button when a path has been specified will open your default media player. If no file path has been specified the button has different text (see B Side button above) and clicking has no effect.

This is the report that lists a single storage box. It can be opened as an Access report...

...or as an Excel spreadsheet, in which case it will open Excel automatically.

Note that this is a work in progress. I intend to create a separate Artist input screen to allow more details of the artist and also a button that will open an image of the record label/artist/record sleeve if you have scanned these.

No macros are used in this. Instead we'll be looking at using a bit of Visual Basic to do the main work behind the scenes. Excited? Good! Watch out for the first instalment soon...

Thursday, 3 February 2011

Using Symbols in Text

There are times when you want to include a symbol as part of your work. Perhaps in a document where you want the symbol to be the same size as your text.

A good example would be when including contact details. Instead of typing the word Tel: followed by a telephone number, you might want to have a symbol of a telephone number.

You could just find an image of a telephone and use that of course, but then it would be fiddly getting it to the right size and having text flow around it properly.

Luckily for us there are special fonts that we can use. Even every day fonts that you use all the time have some symbols - letters with accents such as "é" for instance. But also other useful ones like the copyright symbol "©", fractions such as ½ and ¾ etc.

Scientific and mathematical symbols are there in most fonts too so you can give the temperature as 31° without having to spell out degrees and use ± instead of writing plus or minus. All my examples so far are there in the standard Arial font.

So too are arrows, letters of foreign alphabets, the four suits of a deck of cards, smiley faces, musical notes... You thought perhaps there would be around 70 or 80 characters in the Arial font? 26 letters, 26 capital letters and a set of numbers plus all the other charaters you see on your keyboard? There are over 150 characters in Arial. But not a telephone... Drat... But all is not lost yet or else how would I have included the telephone symbol at the foot of my email above?

So let's have a look where to look...

We start by clicking the Start button. Then choose All Programs, Accessories and System Tools. There you will see an icon labeled Character Map.

This does not contain Bill Gates' family tree or employees but something far more useful (unless you are a future descendant looking for the vital link to claim a share of inheritance).

This is what the Character Map looks like. You scroll down to find the symbol you want. You then click it upon which it becomes bigger so you can see if it is the one you thought it was and if it is the one you want to use you click the Select button to add it to the box at the bottom and then the copy button which puts it into the clipboard so that you can now switch back to your document and paste it in the usual way.

If you don't find the sybol you wanted then try a different font.

You can drop down and choose from a list of fonts. Some of these will contain purely symbols. For instance the Webdings and Wingdings fonts. Strangely, the Symbols font is a grave disappointment to most searchers of symbols... Oh, and the Euro symbol "€" has a font all of its own - the Euro Sign font!

When pasted these may revert to other characters from whatever standard font you are using. (I am currently agog with excitement wondering whether all my work in finding the symbols mentioned will actually show up when I post this blog entry!)

If you find your symbols have turned back into somewhat random but ordinary letters in the same manner as Cinderella's coach at midnight then you will have to go back and amend the font. So I'm fairly confident my Arial symbols will show ok but the Euro sign may well just be a "B" or something! (Actually it worked just as it should do!)

I found my telephone symbol in the Wingdings font and it is shown here after I clicked on it, which is why it has expanded to this size.

Enjoy those symbols!