Learn Access - 3 - Build a database - One more day - By david bailey

Welcome to Learn Access. I'm Crystal. Lets build a database. The music for this lesson is one of my all-time favorite songs, "One More Day" by David Bailey. He was amazing. There is a link you can click to David's website, just read the video description (David Bailey: "Don't let the grass grow beneath your busy feet") For more great tutorials, visit our sponsor, EverythingAccess.com This is the Access 2010 Back Stage. Its door is the File tab on the ribbon. This is what happened to the round Microsoft Office button. The left pane shows choices under the File menu. I encourage you to explore the Access defaults just as you would look at a dashboard to choose a radio station and turn the heater on ... Defaults make your ride more comfortable. Older versions: Tools, Options… In the middle pane, database templates are displayed. We will build an application from scratch, so we will not need a template. Click on the Open Folder icon in the right pane to choose the path where your database file will be stored. ("One More Day you can hold your children") ("One More Day you can hold your wife") Specify a filename when you specify a directory or in the filename textbox. Click Create There is a table (automatically created) that we did not make, so we do not want it. Close Table1 without saving it. Close the database. There is another way to create new files Right-click in the directory where you want to put a new file. From the shortcut menu, choose New > Microsoft Access Database. Name the file to include a code for the date. This saves a lot of headaches down the road. Double-click the filename to open the database. This is Access 2010 and here is the ribbon. ("you might have years of tears behind you") ("but right now you've got One More Day") To make a new table for contacts, click the Table Design icon on the Create ribbon. As soon as you click the icon the ribbon changes Now we are in Table Tools on the Design ribbon The Primary Key for this table will be CID and it will be an AutoNumber. These are the data types. For Number, the Data Type is specified by Field Size. Common Number Data Types are Long Integer, Integer, and Double When a table is created, special thought needs to go into the tablename and the Primary Key, which is the field, or combination of fields, that will uniquely idenify each record. The table name will be c_Contacts. The Primary Key (PK) will be an automatic number (AutoNumber) so it will be different for every record. The contacts table and related tables will be prefaced with c_ to group them by name. Since the primary key field will be used and typed a lot, it will be short. I will call it CID. It will be an AutoNumber field Notice the values for CID. As you can see, CID is different on every record. I'm #1. Who is #7? By looking up the CID, you can find out the names, which are stored as text fields. IsHuman indicates whether the information on this record belongs to a human being or to an artificial entity such as a Company or Organization. Three integer fields are used to store year, month, and day for DOB (Date of Birth). Normally I would choose Date/time to store a date but I don't always know all 3 parts of someone's birthday. Dates are stored as double-precision numbers with the whole part representing the number of days since Dec 30, 1899 and the decimal part representing the passing of time through the day. Age is a calculated field and uses a custom VBA function called GetAge. In the table design, specify FIELDNAME, DATA TYPE, DESCRIPTION, and pay attention to FIELD SIZE. Besides making it more clear what your field keeps track of when you look at the Design View of a table, the Description is also used for StatusBarText when you enter information into that field in the Datasheet View or on a form, so fill it out. ("Don't let the grass grow beneath your busy feet") ("Don't let the grass grow above you when you rest") ("You've got One More Day to get to where your going") ("One More Day to give your very best") ("don't let the clouds ever block your sunshine") ("don't let the sunshine blind you on your way") Year has 4 digits and is less than 32K, so it is stored as an Integer Integer starts with an "I", so just type "I" when you are in the Field Size property of a Number. Year, Month, and Day will all be stored as Integers. Since we have created the DOByr field, lets copy it and paste it for DOBmo and DOBda. Change Name and Description. Save the table. Right-click on the object tab and choose "Save" from the shortcut menu, or press Ctrl-S Oh! no primary key! Access does like having one. Since Access sees an Autonumber field, if I click Yes and let Access create the primary key, it will choose CID. A Primary Key should always be set. Autonumbers are perfect. There is the Contacts table displayed in the Navigation Pane ... and in the Database Window. Create a table for Address information The Primary Key will be an AutoNumber field called AdrID Set the Primary Key by clicking on the Primary Key icon on the Table Tools Design ribbon. Create the CID (Long Integer) field so we have a way to link to the Contacts table and get the Name that this address belongs to. Fields will be created to hold 2 lines of the address. We will have City, State, Zip, and Zip2. This will also accommodate international postal codes where one part of the code is printed before the city and one part is after. Ctry is the 2-character country code. Area is for grouping addresses Create a table for phone numbers Select the Primary Key, PhoneID, by right-clicking the mouse on the selector box and choosing "Primary Key" from the shortcut menu. If information looks like a phone number, even if it does not belong to a phone it will be stored in this table, such as a fax number. ("don't let the cynics tell you they know better") ("better yet, don't let 'em talk to you at all") We have defined 3 tables. Now it is time to look at the relationship diagram. (click on Msys tables and press DELETE) I love being able to drag tables from the navigation pane to the relationship diagram. After the tables are on the diagram, resize the fieldlists to show all the fields. Rearrange the tables so that data that must be created first is positioned to the left. When you define a relationship, drag from the (Primary Key) field in a table to the CID (Foreign Key) in the related table. Unless you have a specific reason not to Enforce Referential Integrity, check the Enforce Referential Integrity box. This will ensure that you do not have any CIDs in the Address or Phones table that don't have corresponding contact information. My relationship diagram flows as data must be entered. This gives me a great birds-eye view of my database structure. ("Hallelelujah, you've got One More Day") �