MS Access Vinyl Database 2 - Creating Tables

<p>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. <p>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. <p><center><a href="http://3.bp.blogspot.com/-oKvohi4c0e8/U5slbeQ3U1I/AAAAAAAANKY/29_swANrz70/s1600/20140613a.jpg" imageanchor="1"><img border="0" src="http://3.bp.blogspot.com/-oKvohi4c0e8/U5slbeQ3U1I/AAAAAAAANKY/29_swANrz70/s1600/20140613a.jpg" /></a></center> <p>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... <p>Open Access and then create a new database based on the <i>Blank Database</i> option as shown - simply double-click this icon. <p><center><a href="http://4.bp.blogspot.com/-_Z2X5-L3YZw/U5smFDzZ7dI/AAAAAAAANKg/aZHZcAjkMTU/s1600/20140613b.jpg" imageanchor="1"><img border="0" src="http://4.bp.blogspot.com/-_Z2X5-L3YZw/U5smFDzZ7dI/AAAAAAAANKg/aZHZcAjkMTU/s1600/20140613b.jpg" /></a></center> <p>Give your project a name - I've called mine "<i>music</i>" and I'm storing it in a sub-folder called "<i>ACCESS</i>". <p><center><a href="http://3.bp.blogspot.com/-Aq95HL2NAIg/U5smeerVjTI/AAAAAAAANKo/8i80fdZinfo/s1600/20140613c.jpg" imageanchor="1"><img border="0" src="http://3.bp.blogspot.com/-Aq95HL2NAIg/U5smeerVjTI/AAAAAAAANKo/8i80fdZinfo/s1600/20140613c.jpg" /></a></center> <p>The first thing to do is to create our first table. Click on <i>Create</i> and then on the <i>Table</i> icon to the left. <p>The new table will open in <i>Datasheet View</i>. This looks a little bit like an Excel spreadsheet (Access can be thought of as basically a series of linked spreadsheets). <p>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 <i>cars</i>, for instance, the fields would hold the <i>make, model, colour</i> etc. <p><center><a href="http://1.bp.blogspot.com/--ljzNZ8ghRM/U5soNjKC_hI/AAAAAAAANK8/ar0hR-BZ2yY/s1600/20140613d.jpg" imageanchor="1"><img border="0" src="http://1.bp.blogspot.com/--ljzNZ8ghRM/U5soNjKC_hI/AAAAAAAANK8/ar0hR-BZ2yY/s1600/20140613d.jpg" /></a></center> <p>So straight away click the <i>View</i> icon as shown. This will be currently showing the icon for the <i>Design</i> view and this is the one we want. You could also click the little triangle (down arrowhead) below the word <i>View</i> and then click the option for <i>Design View</i>. <p><center><a href="http://3.bp.blogspot.com/-um4x7gSWOXA/U5spFtWuOkI/AAAAAAAANLE/--NLil8ayPI/s1600/20140613e.jpg" imageanchor="1"><img border="0" src="http://3.bp.blogspot.com/-um4x7gSWOXA/U5spFtWuOkI/AAAAAAAANLE/--NLil8ayPI/s1600/20140613e.jpg" /></a></center> <p>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 "<i>music</i>" and then also called the form (the window for entering data into the table) "<i>music</i>" 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 "<i>music</i>" and we don't know which is which. <p>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 "<i>music</i>" we'll name this table "<i>t_music</i>" and when we get to creating the forms and queries they will be "<i>f_name</i>" and "<i>q_name</i>" 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! <p>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. <p>Ok, I've made you read all that when all you needed to do was type <i>t_music</i> and click the <i>Ok</i> button! Moving on... <p><center><a href="http://1.bp.blogspot.com/-_kHLwEgBMHg/U5s2ttZxjTI/AAAAAAAANLk/qprXPpkH1n4/s1600/20140613f.jpg" imageanchor="1"><img border="0" src="http://1.bp.blogspot.com/-_kHLwEgBMHg/U5s2ttZxjTI/AAAAAAAANLk/qprXPpkH1n4/s1600/20140613f.jpg" /></a></center> <p>A few things to say here. All of the text you see in the two columns labelled <i>Field Name</i> and <i>Data Type</i> 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. <p>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 <i>Primary Key</i>. By default the top field will be named <i>ID</i> 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. <p>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. <p>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 <i>Media</i> 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 <i>music_media_id</i> 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 "<i>Field Size</i>", the top row in the bottom part of the window. <p>Last point to make here: I've labelled every single field with the prefix word "<i>music</i>" and an underscore. Other tables may have a similar fieldname - the <i>Artist</i> table will have a field called <i>artist_ID</i> and to avoid confusion we don't want there to be two tables having fields with the same name. So the prefix makes it <i>music_artist_ID</i> and it will save us heartache in the future. <p>We've created the first table - click the <i>Save</i> 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 "<i>Property Sheet</i>". <p><center><a href="http://3.bp.blogspot.com/-drbcuAX26Zw/U5s3vJfkvaI/AAAAAAAANLs/xhTg6nBCk9w/s1600/20140613g.jpg" imageanchor="1"><img border="0" src="http://3.bp.blogspot.com/-drbcuAX26Zw/U5s3vJfkvaI/AAAAAAAANLs/xhTg6nBCk9w/s1600/20140613g.jpg" /></a></center> <p>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 <i>media</i> table. Once again go to <i>Create</i> > <i>Table</i>, then click the <i>Design View</i> icon and name the new table <i>t_media</i>. <p>This is the table mentioned earlier where you need to change the primary key from <i>AutoNumber</i> to <i>Text</i>. Then change the field size from "255" to "2". Make sure that the Primary Key icon is lit up as shown and against "<i>Indexed</i>" in the left column of the lower window it should read "<i>Yes (No Duplicates)</i>" in the right hand column. <p>Click the <i>Save</i> 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 "<i>Property Sheet</i>". <p><center><a href="http://4.bp.blogspot.com/-v1RJC3pUu6M/U5s6r0VPqoI/AAAAAAAANL4/pyVl7r4r3cQ/s1600/20140613h.jpg" imageanchor="1"><img border="0" src="http://4.bp.blogspot.com/-v1RJC3pUu6M/U5s6r0VPqoI/AAAAAAAANL4/pyVl7r4r3cQ/s1600/20140613h.jpg" /></a></center> <p>Now for a table to hold details of record labels. Again, only two fields are needed: the code and the name. Name this table "<i>t_label</i>". Make sure you save it once you have defined it. Refer to instructions above if you need help remembering how to create the table. <p><center><a href="http://3.bp.blogspot.com/-bTopsk-KVOY/U5s7c5PN72I/AAAAAAAANMA/wgpER0rbGog/s1600/20140613i.jpg" imageanchor="1"><img border="0" src="http://3.bp.blogspot.com/-bTopsk-KVOY/U5s7c5PN72I/AAAAAAAANMA/wgpER0rbGog/s1600/20140613i.jpg" /></a></center> <p>Last table for now. This is called "<i>t_artist</i>" 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. <p>Just to give an example here. An artist with the name "<i>Elvis Presley</i>" would have the text "<i>presley elvis</i>" stored in the field <i>artist_alpha</i>. We will use this field to sort records so Elvis Presley appears correctly with other surnames beginning with <i>P</i> rather than just after <i>Elvis Costello</i>! In the same way "<i>The Beatles</i>" would simply have "<i>beatles</i>" in the artist_alpha field so that they appear under <i>B</i> in the alphabetical list. <p>Click the <i>Save</i> 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 "<i>Property Sheet</i>". <p>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 <b><i>only</i></b> in the <i>t_media</i> table. <p><center><a href="http://3.bp.blogspot.com/-Pn8dqqe1vBo/U5s-WjiU4JI/AAAAAAAANMM/C8AWLElFcac/s1600/20140613j.jpg" imageanchor="1"><img border="0" src="http://3.bp.blogspot.com/-Pn8dqqe1vBo/U5s-WjiU4JI/AAAAAAAANMM/C8AWLElFcac/s1600/20140613j.jpg" /></a></center> <p>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 <a href="http://bispham2.blogspot.co.uk/search/label/78">blog</a>...) Others may have a collection of even older wax cylinders. You may want to catalogue video files... <p>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!

Diposting oleh Abdullah Syafii