Microsoft Access for Beginners - The Complete Beginner's Guide to Access | Chester Tugwell | Skillshare

Playback Speed


  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x

Microsoft Access for Beginners - The Complete Beginner's Guide to Access

teacher avatar Chester Tugwell, Microsoft Office Expert

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Lessons in This Class

47 Lessons (3h 24m)
    • 1. Introduction to the Course

      1:24
    • 2. Download the Course Files

      0:47
    • 3. Flat vs Relational Databases. Why Relational Databases are More Efficient.

      4:41
    • 4. Create and Save a New Access Database

      1:53
    • 5. Understand Access Objects

      6:56
    • 6. Naming Conventions

      1:23
    • 7. Create the Camrof Ltd Products Table

      5:12
    • 8. Create the Camrof Ltd Suppliers Table

      2:53
    • 9. Understand & Use the Caption Property

      2:06
    • 10. Understand & Use the Field Size Property

      4:17
    • 11. Understand & Use the Format Property

      2:17
    • 12. Understand & Use the Lookup Wizard to Create Drop-Downs

      1:58
    • 13. Understand & Use the Field Description

      1:42
    • 14. Enter a Record in the Products Table

      4:08
    • 15. Enter a Record in the Suppliers Table

      3:09
    • 16. Import Records into the Products Table

      3:41
    • 17. Import Records into the Suppliers Table

      1:33
    • 18. Sort Records in a Table

      3:09
    • 19. Filter Records in a Table

      7:25
    • 20. Apply Formatting to the Datasheet View

      1:44
    • 21. Freeze Columns in a Table

      2:19
    • 22. Build Relationships Between Tables Using the Relationships Window

      9:50
    • 23. Build Relationships Between Tables Using the Lookup Wizard

      6:56
    • 24. Create the Product Form Part 1

      3:21
    • 25. Create the Product Form Part 2 - Placing Controls

      6:20
    • 26. Create the Product Form Part 3 - Designing the Form

      1:55
    • 27. Create the Product Form Part 4 - Formatting Controls

      3:23
    • 28. Create the Product Form Part 5 - The Form Header and Footer

      3:50
    • 29. Create the Products Form Part 6: Adding Command Buttons

      7:51
    • 30. Create the Products Form Part 7: Form Properties

      3:14
    • 31. Create the Supplier Form Part 1: Using the Layout View to Create a Form

      3:15
    • 32. Create the Supplier Form Part 2: Formatting Controls in the Layout View

      4:33
    • 33. Create the Supplier Form Part 3: Finishing Touches

      8:20
    • 34. Create a Form Which Includes a Subform Using the Form Wizard

      9:12
    • 35. Create Your First Select Query

      5:52
    • 36. Create a Select Query That Includes Two Related Tables

      4:47
    • 37. Create a Select Query That Uses And/Or Criteria

      4:56
    • 38. Create a Select Query That Uses Comparison Operators

      2:39
    • 39. Create a Select Query That Uses Between And Criteria

      1:46
    • 40. Create a Query That Uses Wildcard Criteria

      4:15
    • 41. Create the Products Report Part 1

      6:57
    • 42. Create the Products Report Part 2

      6:50
    • 43. Create the Suppliers Report Using the Report Wizard

      8:25
    • 44. Create a Navigation Form Part 1

      10:52
    • 45. Create a Navigation Form Part 2

      5:56
    • 46. Create a Navigation Form Part 3

      3:09
    • 47. Student Project: Teala Training Database

      0:56
  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels
  • Beg/Int level
  • Int/Adv level

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.

100

Students

--

Projects

About This Class

This introduction to Microsoft Access provides the perfect foundation for developing expertise. Even though it is a beginner’s course, delegates will build two fully functioning relational databases. Chester Tugwell, your course tutor, started his Microsoft Access training career back in the late 1990s and over the years he has fine-tuned his content and delivery to culminate in this excellent course.

Chester has designed this course to be as hands-on as possible. It is project-based and takes you through the steps involved in building an Access relational database from the ground up. 

Topics Covered in the Course

Relational Database Theory – what is a relational database and how does it compare to the way data is stored in Excel, for example?

Tables – designing tables to store data. Setting data types and field properties.

Importing Data – importing data into your Access database stored in Excel or as text files.

Creating Relationships Between Tables – understanding primary and foreign keys and creating relationships between tables.

Design Forms – that look great and function well.

Select Queries – understand how to set AND/OR criteria, use comparison operators and wildcards.

Design Reports – that look professional.

Create a Front-End for your Database – create a navigation form with command buttons that open your forms and reports. Set database options that control what appears/doesn’t appear to your users when they open the database.

The course was created using Access 365 (2019) but will work for earlier versions.

Meet Your Teacher

Teacher Profile Image

Chester Tugwell

Microsoft Office Expert

Teacher

My name is Chester Tugwell. I am a self-employed IT trainer with 25 years' experience in a commercial and public sector context. I have run MS Office courses in literally hundreds of companies, providing tailored training made relevant to the workplace. I hope you enjoy my course!

See full profile

Class Ratings

Expectations Met?
  • Exceeded!
    0%
  • Yes
    0%
  • Somewhat
    0%
  • Not really
    0%
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.

Transcripts

1. Introduction to the Course: Hi, my name is Chester Tuck. Well, and I'd like to welcome you to my Microsoft Access for Beginners course. This Microsoft Access for beginners course is project-based, even though it's a beginner's course. Together, step-by-step, we will build a fully functioning Access database. You will learn how to create professional-looking folds of reports and how to design tables and queries the proper way. The skills you will learn here will directly apply to the workplace and will improve your job prospects. As well as learning the practical stuff like how to create tables, queries, forms, and reports. You will also be introduced to the theory behind creating relational databases. How does an Access database compared to excel? And when should you use access instead of Excel? I have been teaching Microsoft Office software, including Microsoft Access since the mid nineties. I've told in schools, sector organizations such as the NHS, and hundreds of companies across the UK of all sizes. In 2005, I set up my own training company to provide in-house bespoke training to businesses across the UK. I'm also a Microsoft Office tips and tricks, YouTube URL. But much more importantly, I have years of teaching experience, course development, and delivery. 2. Download the Course Files: Before you start the course, you will need to download the project resources that I've created for you. So if you switch over to the project and resources section, you'll see all the resources on the right-hand side of your screen for the first bit of the course. In fact, for the majority of the course, you will only need the camera off resources. You can download the Taylor protect resources now if you like, but you're not going to need them till the end of the course. But for now, very minimum you want to do is download all of these cameras resources, put them into a folder somewhere on your desktop or in a place that you can easily access. And then you'll be ready to move on to the next lesson. 3. Flat vs Relational Databases. Why Relational Databases are More Efficient.: In this part of the course, we're going to discuss the difference between storing data in Excel and storing it in Access in Excel, generally, you store the data in a flat database. What do I mean by that is that you've got one table that contains all of the information, is an example which you'll find in the files that have provided for you. It's the camera of product list Excel file. And you've got your list of products here, essentially 20 products. And you can all the product information here. But you've also got for every row or the supplier information, we need to know for each product. Who are suppliers? The problem is storing data in this way is, is extremely inefficient. And I'll give you some examples of why that is the case. For example, our products here obviously use different suppliers, but there'll be more than one product that uses the same supplier. For example, catering supplies to you is used many times. You can see in this list, whenever I create a product in this database that uses catering supplies to you, I have to write all this information out each time. It's the same information, but it's gotta be entered into the database each time I create a product. What's more if I need to make a change to the supplier details, catering supplies to you, maybe it's the contact name or email address or something like that. I've got to make that change to each instance of the supplier in my database. And if you've got thousands and thousands of products, you can imagine how time consuming that could be. Hopefully, you can see that this is not an efficient way to store your data. Now I'm gonna show you the same data, but in an Access database, I'm just going to move to access. And this file is also available to you. And basically we've got the same information here. We've got the products table, which contains the product information, but it doesn't contain supplier information. And we've got a separate table that contains the supplier information. Now, what we do in Access is we split the data up into separate tables. So all the related information is together. And then we join the information back together via relationship. And we'll go into this in more detail later on through the course. But just for now, wouldn't you get the concept that we're splitting the information up into separate tables and then we're relating the information back together using filled relationships. But just show you the relationship view here. What we're actually doing in this database is we're linking fields between the two tables, got the product information all here, and the supplier information here. Well, what I've got is this little field here called supply code that links to the information in the supplier table. That means I only need to store each supplier information once within the database, but I can refer to it many times in the product table. Now what that creates is a much more efficient way of storing the data. But for example, if I go to the suppliers table and I expand catering supplies to you here, you'll see that all the products that relate to that particular supplier listed underneath that supplier that I can do the same for direct baking supplies. Those are all the products that they supply. So I only need to store the information once for each supplier and relate all the products to that supplier via Field relationships. That's what we're hoping to achieve during this course. Moving from a flat database to a relational database. Now there are other advantages to using access over excel. And what I've done here is given you a little link, you can click on. And that would take you to a Microsoft page, which will explain all the advantages are not gonna go into them in a great amount of detail. Here is something you can read, but it will tell you, for example, when to use access and when to use Excel. And it goes into some detail on that. Okay, that's all that's going to be covered in this part of the course. Have benefit those files. Make sure you can kind of understand the concept here. The difference between a flat database and a relational database. 4. Create and Save a New Access Database: In this lesson, we're going to look at how to create a new Access database. So what I suggest you do is you close any databases or spreadsheets you have opened from the previous lesson. Then once you've done that, you need to search for the Access application on your computer that can do that on your start menu, you may find it there. Or if you can't find it that way, try typing access into the search bar on your taskbar. And there should pop up, click on it once, and then you're presented with a screen. So you gotta decide how you're going to start your database. You want to start with a blank database or with one of the templates that access provides. Well, we're going to start from a blank database in this course. So click on the blank database button and you'll get this dialogue box. So first of all, you need to give the database a name. Now we're going to call it camera, which is the name of the company we're creating the database for. And then what overdo just put your name off the land. Then you need to navigate to where you're going to save this file. And you'll probably going to want to save it in the same folder as all the course files that I've provided for you. I'm gonna save it on the desktop, so it's easy to get. Now I've created a little folder called Access introductory course, like click on OK. And then once you've done that, divided a name and an occasion, just click on Create, and it will create the database for you. Now one thing to notice is, is that unlike say Word or Excel, where you can enter some data, maybe word a few paragraphs or in Excel a few numbers, and then save. You can't do that in access. You have to save the database before you can store any data in it. Okay, just go through that process of creating a database for yourself and then you'll be ready for the next lesson. 5. Understand Access Objects: Hi, in this lesson I want to talk about access objects. So we've already learned how to create a blank database. But within the database, you're also going to create and save objects. And the objects that we're going to look at in this course are tables, queries, forms, and reports. And as we go through the course, we'll discuss what those different objects are useful. I've opened up the camera off products database that is in the course folder that I've given you. And you can see that there are lots of different objects in the navigation pane down the left side of the screen. We've got tables at the top, queries, forms, and reports. Now tables are used to store the actual data. They're very much like a spreadsheet within Excel. Queries are like a question that you're asking the database. So you might want to know all products that are supplied by a particular supplier. Forms are like a front-end to the database. It's a clone of interface that you design for entering new products or new suppliers. And reports are an output for your database. If you want a professional report, as the name suggests, something you could print out as a PDF or email to somebody. So I'll just give you some examples of each so you know what they are. It starts off with the tables. If I click on the products table, you can see, as I said, it looks very much like an Excel spreadsheet is just rows of data, records and columns or fields. So that's the table. A query asks a question of the data, as I said earlier on. So if you look at the names of these queries, for example, I may only want to see all the pan products in my database. So you can see if you look at the product names, they all contain the Wordpad rather than showing all of the products in my database. So you can ask your database lots and lots of different questions through a query. And another one that I've got here is knife products, steak knives or chef knives or Britt nice. You can see that they're all knife products there. Rather than showing the whole database. That's what a query is. A form, as I said, is lock a front end to your database. So let's have a look at an example of a form fopen up a products form. This is what the form looks like now, all the data that I'm viewing in this form or navigate through it is the date of that's actually stored in the table. If I change data in this form, it changes the data in the table. If I add a new product in this form, it adds the product to the table. So as I said, it's like a front-end to your database. You can nicely designed it, make it very easy for people to access data or, or enter new data. Lastly, let's look at an example of a report. Let's look at the product list. Open that up. So this is something you could send as a PDF printout, attaches an email. That type of thing looks very much like cannibal Word document. But again, you can design it, you can put your logo in, you can solve what font you could use, what colors you're gonna use. So as I said, there are four different object types that we're looking at in this course, tables, queries, forms, and reports. Now, this navigation pane here you can collapse if it's taking up too much room, you'll see that it will button there, collapse and expand it. And there are also different views within the navigation pane. So the moment I am looking at the objects by object type, but if I went to tables and related views, it would, for example, list all the tables in my database and then all the objects that are related to that table. So for the products table, I've got the table itself, and then I've got various queries that are based on the table, a couple of forms that are based on the table, and a couple of reports that are also. So I won't go through all of these different options here. But just to let you know that you can switch between views just by using that little arrow button. There. Wouldn't worry too much about that at this stage, but I'm just letting you know it's there. Now by default, when you double-click on one of these objects, it opens in a window. And so if you've got multiple objects open, they kind of overlap each other, which is fine. But you may find a slightly different layout, more useful, especially when you're initially designing your database. Now we're going to change a setting to actually allow for tapped windows. So if I go to File and then down to options, I want to go to the current database, options for one down here. And what we're looking for is document window options. You've got overlapping windows or tabbed documents. And I want us to try tabbed documents. So if we go k there, and what it says slightly annoying, but you've got a close and reopen the database for this specified option to take effect. So that's what we'll do. So I'll close this file clones, and then I'll reopen it. This is by the way, just a default formed that pops up. I'll show you how to do that later on in the course. But now I'm going to double-click on the products table, and then I'm going to double-click on the queries table. Now what you'll see is, is rather than mopping up or separate windows, they open up as taps within the Access application, which if you're working with lots of windows, lots of objects, is a much better interface from my point of view anyway, especially when you're designing your database. Later on when you've designed it and you want others to use are probably go back to the other option. So we can look at all that later in the course. But for now what I want you to do is just go back into this database. So now you've already got the new database that you're creating open, but you can open this one as well and just navigate between these different objects. Get use to open them and closing them. Changed the options that I showed you in here, File Options here, and leakage current database, and to switch between those so that you're comfortable with how the two methods of showing documents are available to you. Okay. So Door that practice all of that and then we'll move on to the next lesson. 6. Naming Conventions: In this lesson, I want to talk about one more thing before I move back to our due database. And now one more thing is the naming conventions that we're going to apply to objects. Now, one thing that you'll notice is that all of the objects have a three letter lowercase prefix TBL for table, QR wife queries, FOR M for forms, RPT for reports. And this is considered good practice as you get further into your reuse of access, more advanced use, you'll begin to find these naming conventions rarely useful. The other thing that you'll notice is that I didn't have any spaces in these names. And that is also considered good practice. Especially if you're gonna start getting really advanced with access on writing code. If you have spaces in your names, it will becomes quite awkward. Now, if you don't have a three-letter prefix and you do have spaces in your object names. Your database is still going to work. So from a beginner's level point of view, don't get overly concerned with these naming conventions. But if you're thinking long-term is probably worth using these naming conventions now, rather than having to remember them further down the line. 7. Create the Camrof Ltd Products Table: In this lesson, we're going to learn how to create and save a table and how to specify fields of that table. At the moment, if you've been following, you're probably in the camera of products database, which you can now close down so you can get file close file exit. That's come out of that. And you should see, if you still gotta open, you've got your new cameras database. If not, just open it from the folder that you've created for it. Now you'll notice that there is a table in your database. This was created automatically, is called Table one. This always happens when you create a new database. It just creates a table called table one. And what you need to do is save that and give it a name. Now although we've already saved our database, we still need to save the table object within the database and give it a name. So to do that as fairly easy, I probably use the save button top-left or might use control S to save. If you can't see that save button, by the way, just go to your little Customize Quick Access toolbar button. Make sure save as ticked there. So I'm going to click on save and it'll prompt you to save. So TBL, three-letter prefix, no spaces, products. Click on. Okay. Now there are two views for your table. Currently we're in datasheet View, which is a bit like a spreadsheet that become more obvious when we've got more fields. And the other is called Design View, which is where you add fields and design your table. To switch between views. You can do that either on the Fields tab or the home tab on the ribbon. And it's always the first button on the left. It says view. There you click where it says view. You've got two options. Datasheet view, we're currently in design views what we want. So this is what design view looks like. It's very different to the datasheet View because all you're gonna do here is enter the field names, not actually going to enter the data. The data itself is entered in to cheat you. Now it's created a field for us called ID. We're going to change that to product code as our first field name, product code. Now this datatype auto number, which is using, by default, that's useful if you need a unique identifier for each record to be automatically generated for you. Here in our database, we're going to type in the product Cloud. We have a camera of product code that we want to type into. Auto number is not going to quite work. But if you didn't have it, couldn't think of anything that uniquely identifies the record, then auto number would be really useful. But for us, we're gonna change that to short texts. The next field is actually going to be called supplier code. Now you might be wondering why I'm including supplier information in the product code. You remember at the beginning of the course I was saying that you had to split the information into tables. And you only have data that belongs together in a table. So you'd have all the product information in one table or the supplier information and another. So why have I got the supplier code in this table? Well, it's because I'm creating a relational database. I need some way to relate to the correct supplier in the supply table. And this is the field that I'm going to do that with. I'm not expecting you to completely understand that at the moment and you'll see how it works as we go on further through the calls. The supply code will relate to the particular supplier and the supplier table. The next field known as product name. That's also going to be short text. And then we're going to have critically short text again. Next one is whole sale price. Now the datatype for that is going to be Currency because selected from the list. And then we're going to have a list price. Well, that's also going to be currently we're then going to have discontinues. Discontinue. And that's gonna be a yes, no field. We're just gonna ticket if the products discontinued. Yes-no. Then we're going to have product sheets. And that's gonna be an attachment fields. So we can tach like a Word document that specifies everything you need to know about the product. Date first sold. That's obviously going to be a date field. Ok, so we've got all of our field names and all of our datatypes. And we've named our table. And that's as far as I'm going to go in this lesson. And if you open up the course file, there is a list of all the fields that I've created here so that you can create them in your own database. So if you want to get on with that, I'll see you in the next lesson. 8. Create the Camrof Ltd Suppliers Table: In this lesson, we're going to create the suppliers table. So make sure you've saved any changes you've made to your product's table. Then go to the Create tab on your ribbon and click on the Table button. Then go to Save, and we'll save our table. So we'll call it a table, supply the color k. So I've got two tables in our database now, we need to go to design view for the suppliers table so that we can enter our field names. So the first field name we're going to create is supplier code. And again, when nobody is also number, we're gonna use short text for this. Then we're going to have Supplier name. That's also going to be short text. Then firstName is gonna be the name of a contact. Short text again, last name, short text again, email address. Now this is going to be hyperlink. The data type for that. So if someone clicks on the email address, it automatically generates a blank email address to that person. Then drop total will be short texts. Phone number. Now, don't be tempted to use the number type for this. If you think about it, telephone numbers can contain brackets. They often stop, always start with a 0 configuration. If it doesn't start with 0, numbers, can never start with 0. It's definitely a text datatype for phone numbers. I shall texts as well. And we've gotta address, Sorry, S1. One short text address to show Tech City, County. All of these are short text and the notes will have as long text. Now, why much using long text, short texts, you can have up to 255 characters held in that field. Long text is useful if you want to have more capacity. So if you were writing notes, you know, on a particular employee or a patient or whatever, long texts would be the way to go. Okay. So once again, if you look in the coals fall, I've listed all of those field names together with the data-type. If you can enter all that information into the table and then save it, I'll save it now. You'll be ready for the next lesson. 9. Understand & Use the Caption Property: In this lesson, we're going to learn about the caption field property. So let's start in the Products table. And quite a few of these fields will need to set the caption property four. You'll see the caption property down here. And what the caption actually provides us with is a way of displaying the field name in the tables datasheet view. So that would be this view here. So it would be how these field names up here at the top of these columns. At the moment, you can see that the field name side have any spaces in them because of this practice, fulfilled names, not having spaces in them, but those names without spaces. That user-friendly either if you open up a database and you've got two or three words and the field name, and there are no spaces. They're not that easy to read. But also it's how the field name appear in queries, in forms, and also in reports. So for something like product code, we can just write it as product space code. And obviously supply code will be the same supplier space code. I'll just show you how that looks. In the datasheet view. I must say the table first, though we have got product code, just warn There's comes about and supply code. So even though go back to design view, the field names are still without spaces here because I've supplied a caption. That's how they will appear in the datasheet view within the table, but also informs reports and queries. So I won't bore you by going through each of these and adding spaces. You can see where the spaces are needed. And you want to do that for both the products table and the suppliers table. So I'll let you get on with that and I'll see you next lesson. 10. Understand & Use the Field Size Property: In this lesson, we're gonna learn about field properties. So we're in the new database that we've created, cameras off your name. And you might already have the products and the supplier table open. But if not, just open up the products one to begin with. And you need to be in Design View. So we'll go to design view. And you might have noticed as we go through the course that there's this little area of the screen down here called field properties. Now when not in this course going to look at all of these field properties, we're just gonna look at the ones that matter to us right now. And the first one we're concerned with is the field size property. Now by default for short text, for example, the field size is set to 255. Now that means that 255 characters are allowed in this field. Now for something like how product code, we definitely don't need those number of characters. We're going to store about ten characters in that field. Probably less than that, to be honest. So let's put in ten as our field size. The supplier code is similar, we'll say ten. You might wonder why I'm actually doing this. Why am I bothering changing this amount from 255 to ten? Well, all it's doing is it's saving storage area within your database. It's only gonna really matter when you have lots and lots of data, huge amounts of records, hundreds of thousands of records. But we're learning things that are good practice right from the beginning of our training. So setting the field sizes of really good idea product name belonging to do is I'm going to set that to 50. That's more than enough category. Again, we could set that to, let's say 20 for that wholesale price. Now how stock prices or currency field ANY, doesn't have that property. So you'll learn that depending on the data type that you've chosen for your field, you will get different field properties. Let's go. I'm still going to be true for list price as well as currency. There's no field size. Discontinue that yes or no, that has no field size. Product sheet. That's an attachment datatype. No field size, date first sold a date data type, no field size for that. So it's short text that we had that option for. Now we can do the same for the suppliers table. We go into the supplies table and go to design view. You can see what we want to do is set them to sensible amounts. So I'm going to say ten for the supply code, supplier name, we'll say, let's say 25 for that one. Firstname. Well, it's 25. Last name, 24. If you'd have to really think about this, all your names are going to be longer than 25 because if they had more than 25 characters, they're not going to be there at those extra characters. Email address, that's a hyperlink datatype, no field size, job title. Well, as the that phone number, let's say 24 lat postcode came for that, that define a dress. And for these I'm gonna say 5050 city, I'll say 20-45, out1 county, 25 for that one. And notes. This is a long text datatype. It has no option, they're fulfilled. Size is designed to allow you to enter huge amounts of data in that field. So there's no Field Size property for that. Allow you to do now is to go through each of the fields in each of these tables, the Products table and the supplier table. And weather data type allows it set the field size to an appropriate size that you feel is right for that particular failed. Once you've done that, I'll see you in the next lesson. 11. Understand & Use the Format Property: In this lesson, we're going to continue to look at phil properties, but we're going to look at the format property in particular. So if we start off in the Products table, we don't need to set format properties for all the failed. So I'm just going to pick out a few that are important. We can start with a product code. We always want the product code to be capitalized in the format property to ensure that the values, no matter how they talked lowercase or uppercase, to ensure that they always appear in applications where you do is you put the greater than symbol in. You want them in lowercase, probably guessed it. You put the less than symbol and we want greater than. We're also going to do that for the supplier code. Very simple. Just put the greater than symbol in there. For the others category, we're not too worried. Wholesale price now this doesn't need change in, but just to show you a file, just close down the property sheet here to show you a little bit more room. Just to show you that you can switch between your native currency. Here it's Pound sterling and another cancer, heroes, euro. So you can switch between those if you need to. I'll keep minus pound sterling. Also the list price that can stay as it is. The other one we might want to look at is depth-first sold. That's a date field. And you can choose how you want to show the date go time element there as well. General date. I'm going to go for in this situation, this long date format, long date format, that's one chance. Now, that's the Products Table is get to the suppliers table. So again, what we want for the supplier code is we want that in uppercase. So put the greater than symbol in postcode will have that capital lines greater than. And looking through the rest, we don't need any formats on any of the other fields. So I'm going to save both those tables with those changes to those Phil properties. And what I'd like you to do is just go through all the Format settings in your version of the table. When he finished. We'll start the next lesson. 12. Understand & Use the Lookup Wizard to Create Drop-Downs: In this lesson, we're going to learn about something called the lookup as it. And the lookup wizard enabled you to create a drop-down list for failed. We're going to do this in the Products table in the category field. So what you do is you go to the datatype column and in the drop-down list, change the data type to look up wizard. And you have two options. You can either get the values for the dropdown this from another table, or you can type in the values that you want to appear in the drop-down list. We're gonna get for that second option in this example. So I go to next and then up at a toy pin my values. So we have four categories. The first one is likewise. Then we have drinks, then crockery, and then utensils. Mixed. What label would you like for your lookup failed, where we're going to keep it as category. And then you have this option Limit to list. So if you don't want someone to misspell a category name or to enter a category that doesn't really exist. Limited list is a good option, so will tick here. You can also allow multiple values, but our products only belong to one category. So we won't tic that. Let's go to finish. Let's just look at how that works in our table. So I'm going to save the table and I'm gonna go to datasheet view. And if I go to the category column and now have a drop-down list of categories that I can click on. Much easier to enter the category, and I'm less likely to make a mistake. Okay, if you want to go ahead and create the lookup wizard in your table, what should done? I'll see you in the next lesson. 13. Understand & Use the Field Description: In this lesson, we're going to learn about field descriptions. So at the moment we're in the Products table and we're in the datasheet view. So I'm going to get back to design view. And you have this description column next to the datatype column. And you can see it's optional so you don't actually need to use it at all. But if I click into this column, it gives a little description down here of what the description column is used for. The field description is optional. It helps you describe the field and is also displayed in the status bar when you select this field on a form. So it's first use is for people who come to this design view. And they are trying to understand the fields. The door in this particular table set someone who didn't originally set up the database. It will really help them to understand what each field is four. But the other use is for when you've created a form, the description for a particular field will appear on the status bar. On the status bar is at the bottom of the screen, or just come up with the text that you've typed in this description column. So for example, we'll put a description in the discontinue column. And we'll say please tick. If a product is discontinued. And that's all you need to do to add a description. Okay, so if you fancy doing that in your product table, please go ahead and do it. And when you're finished, I'll see you in the next lesson. 14. Enter a Record in the Products Table: In this lesson, we're going to add data to our products table. To add data, you need to be in the datasheet view. At the moment we're in the design view. I'm going to switch to the data sheet. And you can see all our column heading names at the top of this view. This is very much like a spreadsheet. So let's add our first product code, which is c, m, f, three zeros and a one. Now let's enter the supply code. But before we do that, I want us to remember that we set up a format attribute, both the supply code and the product code. And that forced all the characters to appear in uppercase. So let's test that out. Let's enter the supply code in lowercase IS UP DBS. And I'll tab to go to the next field. And you could see with our entity lowercase, it's appeared in uppercase. Now the product name is Apple pica tab. And the category, because we set up a lookup with it for this field, we can select an item from the drop-down list and my haven't bike where for this one, the wholesale price for this is 18 pounds. With a tab, you can see it's automatically formatted as a currency value. Because we selected currency as our datatype, this price will have a 64 pound on genome. Discontinued. This is a tick box. And because this product is not going to be discontinued, who is not currently discontinued? We don't ticket. Now the product sheet used the attachment data type, so we want to attach the product document to this ruffled. Now what I'm gonna do is double-click in this cell and it brings up the attachments dialog box. Click on Add. And you'll need to navigate to your course folder. And in there I've put five product treats. I haven't put it put a product sheet into every, every product, but enough to give you an idea of how this works. So we'll select our product sheet and click on uncle. Okay. Now if I want you to view the product sheet using this table, I will double-click on. Let us again select the document I wanted to do, and then I'll click open. You would open up Word and I could see the product specification sheet. Now date first sold. Let me just close this down. Take for salt for this particular product was the 12th of January 2016 with a timeout. I get these little crashes now means the column is wide enough. So if I double-click there, it will format the column so it's wide enough. And if you remember, we set the format for the day so that the month would show in full as a word. Okay, that's completed. Our first record, we'd need to talk about saving when you're within a table, records and data or automatically saved, you don't need to click on the Save button, but you do need to click on the Save button if you want to save any changes you've made to the layout of your table. And we made a small change to the lamp just now by widening date for salt column. So to save that change the next time we open it, it's the correct width. You do need to save your table, but not saving the data that you've entered in the table acts as a setup to automatically do that for you. Okay. I hope that's clear. What I'd like you now to do is to internet record if you haven't already done so into the products table. The data, if you've not captured it from this video, is in the course file. Okay, so get that down and I'll see you in the next lesson. 15. Enter a Record in the Suppliers Table: In this lesson, we're going to enter our first record in the suppliers table. So we'll switch over to the suppliers table. And we're currently in the design view. To enter data in this table, you need to be in the datasheet View. So to switch the datasheet view, go to your view menu and select datasheet view. And now I can start to add my record. The supply code for the first supplier is SEP DBS. Now I've entered a lowercase, a tab IVR automatically format in uppercase. Because of the settings we entered in the format property for this field, supply is direct baking supplies. Firstname is Luis, lastName is Malik and her email address is L Malik, DB supplies.com. Now, Lewis's job title is Accounts Manager and her phone number is 012345678. Knowing 0. Postcode for Louise winter in lowercase again, PAO1 70. Why tab across automatically formats and in uppercase addressed one Dragon. Industrial, addressed to we have as Hope Road. City is Portsmouth, and county is temperature. Now, there are no notes for this particular supplier. And if you remember that we don't have to save the record is automatically saved for us. But if you were to make changes to column widths, for example, like I might want to auto fit the supplier name column and are going to do that by double-clicking up there in the column width between the two column names, then that is a change to the layout, the format of the table. And that will need to be saved. So I'll save it there. And then I've completed entering the first record in my suppliers table. What I want you to do is to go ahead and do the same in your database. If you go to the course file, you'll find that record available for you. You can just copy the details down. Once you finish that, we'll move on to the next lesson. 16. Import Records into the Products Table: In this lesson, we're going to add some more records to the products table. But rather than entering those records manually, where actually going to import them from a text file. So to do that, you go to the External Data tab on your ribbon. Then you go to the New Data Source button. From there, from file. Then textFile in this dialogue box, browse to the course folder that I've given you. And you want to select product list dot text. You can import it in several ways. You can import it into a new table or append a copy of the records to an existing table. That's what we want to do. Append a copy of the records to the table Table products. You can also link to the data source by creating a linked table. We don't want to do that in this scenario. So we go for the second option. Click on ok. So if you have the table that you're importing data into open, you'll always get this message. The table which you are trying to upend to is currently open and must be closed before proceeding. Do you want to save the changes and close the table? So I will say yes. Do you want to save changes to the layout of tape multiple products? So if you've made any design changes to the table that you want to keep, you need to click yes at this point. And then you'll get the import texts wizard dialog box come up. It says your data seems to be in a delimited format. If it isn't, choose the format that more correctly describes your data. Well, a delimited format is a format where each field is separated by a common character, like a tab or a comma. And that is the case for us. These fields are separated by a tab. We'll keep delimited selected. And then we'll go to the next. And then you have to choose your delimiter and tab is the correct delimiter. I can see that that's the case is I've got the law ends there on the side of each column. For example, if I chose semi-colon comma, you can see it doesn't separate the fields. That tab is definitely the delimiter. The first row contains field name, so I need to take that. And then I can go to the next. That's all the information the wizard needs to import your data, import to table table products. So I can just go to finish. And then I can go to close. Now if I look in the Products table, you can see that it is imported 19 products, so I have a total of 20. You may also notice you'd get this product list import Aaron's table. If I open that up, you can see that for each record is had a problem with the product sheet field, but I wasn't holding any data in the product sheet field in the textFile anyway. So I can safely ignore these errors. So I'm just gonna close this down. And in fact, I'm going to delete that table. Right-click Delete on. Yes, and I'm done. So that's all this lesson is going to cover. So if you want to do the same thing in your database, import the records from that product list textFile. And when you're done, I'll see you in the next lesson. 17. Import Records into the Suppliers Table: In this lesson, we're going to import suppliers into our suppliers table, and we're going to do that from an excel spreadsheet. This is bit like importing data from the text file. You start off by going to the External Data tab on your ribbon. Then over to the New Data Source button. From there, go to the File menu and choose Excel. You then need to browse to your course folder and from there, select your supplier list. You could then choose how you want to import the Excel file or the records within it. And again, we want to append a copy of the records to the table table suppliers from Kate. Again, it's going to ask you to close and safe the table we copy open, wants to do the import soil. So yes, Microsoft dyskinesia, column headings as field names for your table doesn't actually give me the option here is just called a recognized them as field headings. I have no choice but to go to next. And it just confirms there that it's going to import it into the supplies table. Click on Finish, click on Close. If I go to the suppliers table, you can see that all those new supplies had been imported into my table. Okay, that's all for this lesson covers. So what I would like you to do is do the same thing in your database. And once you're done, I'll see you in the next lesson. 18. Sort Records in a Table: In this lesson, we're gonna learn how to sort data in the datasheet view. So I'm in the Products table and I am in the datasheet view. There are a few ways you can apply sorting to your columns. The most obvious way is to use the little dropdown that appears next to the field name at the top of the datasheet view. So if I went to product name for example, I can then choose to sort eight his ad or Zimbabwe. If I was in a numeric field, rather than giving me the option a desert, it would give me the option smallest to largest or Largest to Smallest. If I was in a date field, it would give me the options, oldest to newest or newest to oldest. Now, the other way you can sort is to right-click. For example, if I wanted to sort in ascending order of list price, I could right-click on one of the list prices and then choose smallest to largest. The other way you can sort is if you go to the Home tab on your ribbon, you'll see there's two sort buttons here, ascending or descending. So for example, if I click in the product name field, can say salt this data in ascending order. The other thing you can do is sort by more than one field. So for example, if I wanted to sort by category, but within each category by list price, I would need to select both of those fields. Category is already selected. I hold down shift on my keyboard and I select the list price field. And I did that by clicking on the actual name of the field at the top layer. And then on my home tab, I could say I want both fields in ascending order. So what you can see is, is we've got the categories in ascending order. But within each category, the list prices are also in ascending order. Now, that works well, if the two columns that you want to sort by are next to each other. But say I wanted to sort by category and wholesale price. So within category by wholesale price, if I select that field shift, select this field. You can see it also selects the list price column. So what I can do instead is move the wholesale price column next to the category column. And the way I do that as I drag the column heading over to the category column, then I can select both fields, and then I can sort in the way that I want. Ok, so there's some tips and tricks for you for sorting with, in the datasheet view, what I would like you to do is just to practice and play around with the sort options. And when you're done, we'll move on to the next lesson. 19. Filter Records in a Table: In this lesson, we're going to look at how to filter within the datasheet for you. So I'm in the Products table again. And we're going to use the same dropdown buttons that we used earlier on to sort films. We're going to use those same buttons to apply filters. For example, let's start with the product name field. And I'm going to click on the little drop down menu button. And I'm going to look at these options here for sorting. These are a list of all the unique values that access found in this column. So for example, if I wanted to look for a particular product, I could untick select all. And then for example, click, take Japan's click on OK. And it would hide all the other records just showing the cake pan record. If only go back to viewing all of the reference again, I can go back up to my little button which now has a funnel it. And I can say clear filter from product name. Now of course you can take more than one item in this list. I might say Apple Baker cake pounds. And that would show me two items returned by the filter. I'll clear the filter again. So that's like a text field. One thing you can do is apply a partial match filter to a TextField. I'll show you what I mean. If we go back to the drop-down list next product name, and instead of using these tick boxes here, I'm gonna go to text filters here. You'll see that we've got some options here, like Contains begins with a and ends with now I'm getting used to the Contains option here. And I'm gonna type in K and I to pick up all the knife products. So if I click on OK. You can see now we've got knives and knife products. Okay. And it doesn't matter where noise or knife appears here. It appears at the beginning. Here it appears that the end is just picking up that three letter sequence KNIME anywhere in the text string. And obviously, if I go back to text filters and I just wanted to pick up north products that began with the word knife I could choose begins with an n-type KNIME. And that would only return the one product. So clear that filter. So that was dealing with text filters. Let's go on and look at numeric filters. I'm going to apply a numeric filter to the wholesale price. If I go to the dropdown next to the column heading, let's say I was only interested in products that were above 25 pounds to buy. So if I go to number filters here, what I could do is say greater than and put him my criteria then 25 pounds. Click on okay, and you can see it returns for products. Another option you might have or you might want to use rather, is to go to number filters and do a between filter. I could say that I'm interested in products between 30 pounds and 40 pounds. So if I click on OK, I get a few less products and photo and you get one product that meets that criteria. So I clear my filter. So the other type of column we have in our table is a date column depth-first sold. And there are different types of filters, but they forget to take filters here, constantly useful ones up here. I might, for example, say I only want to see products that were first introduced before certain date. Let's say we want to see it before of the 12th, 2014. If I click on OK, that reduces the number of records that are currently showing. So clear that filter. Now there is another way that you can apply a filter without using these drop-down list, and that's filtered by selection. For example. Let's go back to the instance where I wanted to look for knife products. If I select kn and then go up to selection contains k, and then it shows the records that contain KNIME. I didn't have to write in the criteria. I could just select it. Let's just clear that. Clear from Filters. You can do it for whole words as well. For example, if I selected utensils, their advanced, sorry, selection equals utensils in fat, you can right-click into utensils and say equals utensils. Let me just clear and so you can see how that works by right-clicking to utensils equals utensils. So basically the idea here is that you can base the filter on whatever you selected. If it's the whole word, then you just need to right-click in to it. Or for colourless. If it's part of a word like k and you select the letters that you want to base the filter on. Go to Selection contains KNIME. So it's quite neat to think that it's clear that filter. And there's one more way we can apply filter and that filter by form. So to do that, what I do is I go to Advanced and I'd say filter by form. And here I just get back up online Table, no reference at all. But I can say here, let's say I want to see all like where products with a wholesale price that's greater than 20. So if I say greater than 20, and then if I go to Advanced apply filter, it would show you that one product. Ok. And obviously then I can just clear the filter or do that again Fourier, let's do another one. So I go to Advanced Filter by formulas, do something different. Let's say I was looking for utensils where the list price was less than 50 or greater than less due to less than 50. So if I apply filter sort, there, we have it, the utensil products that are less than 50 pounds less price. I clear that filter. You can see that some of the utensil ones are greater than 50 pounds, those excluded, those in the actual filter. Okay, so there we are. There's a summary of your filter options. While I suggest you do is you bend a little bit of time playing around with those filter options. And once you've done, we'll carry on with the next lesson. 20. Apply Formatting to the Datasheet View: In this lesson, we're going to look at some formatting options you could apply to your tables datasheet view. You don't have to use these options, but you might find them useful. So you need to be on the home tab of your ribbon. And if you go across to the text formatting group of buttons, the most useful option I find here is this alternate row menu. Here you can choose a color for alternate rows in your datasheet View. So if I choose a color here, and the reason why I think this is useful is I just think it makes the data easier to read. Other options, you can change the font. I choose a font in this drop-down list. May find a particular font easier to read and you can also change the font size. Just widen this column so you can see the dates in full. Can also change the color of your text. But you can't really see much benefit in that, so I'll keep it as black. And also, if you just find the alignment of data within a particular column, just makes things harder to read. You can always center or change the alignment in any way that you like. So by default this was right aligned and aren't going to center it. And I think that just makes it slightly easier to read in this table. Okay, so have a play around with the formatting options that I've shown you here. And once you are finished, we will move on to the next lesson. 21. Freeze Columns in a Table: In this lesson, we're going to look at freezing columns. Freezing columns is quite useful in a scenario where you have lots and lots of columns and you find yourself scrolling across the table and you lose sight of an important column that helps you reference the rest of the data that you're looking at in a record. So for example, if I wanted Product Name to always stay on the screen as I scroll to the right, what I would do is I add right-click on the name column. And I would say freeze Failed. Now it becomes the first all leftmost column in the table. But if I scroll across, you can see that it always stays in position and is always visible on the screen. To unfreeze the column, you just right-click and choose unfreeze all fields. What you will notice is that the position of the field has now permanently changed, but I can track it back into position. All I do is point the field header, the column heading, and hotel my mouse button and drag the failed to its original position. You can also freeze more than one field. For example, if I wanted to freeze product name and category, I would select both fields. Right-click and freeze fields. And then if I scroll across, you can see both fields stay in position on the screen. And again, to unfreeze fields, I don't have to even select both fields or either of those firms are fat. I can just right-click and phrasal fields. And if I then scroll to the right, you can see that those fields disappear over to the left of the screen. And again, if I wanted to move back to their original position, I'd select both fields and then direct them to their original position. I think actually we're right at the supply code and now they're in their original position. Ok, so I have a play around with freezing fields. You may find it a useful trick if you're working in the datasheet view a lot. Once you've done that, we'll continue onto the next lesson. 22. Build Relationships Between Tables Using the Relationships Window: In this lesson, I want to talk about relationships between tables. And I'm going to start off by going back to the Excel spreadsheet that we looked at at the beginning of this course, the camera of product list workbook. And you may remember that in this worksheet, one of the complaints I had against this later was that it was a flat database. In other words, the supplier name had to be inputted multiple times. Now what we've done in our Access database is we've split the data into two tables. So the question is, is, why is that beneficial? And also how we join the data back together. Now, I'm back in our Access database, the one we've been working in during the course. And I want to get to the products table and I'm going to go to design view. Now one thing we haven't mentioned in the course so far is this little key character that appears in the product code field name. And in fact for switch back to the suppliers table and go to design view. You'll also see there's a little key character in the supplier code field as well. The relevance of this key, we need to understand. It's actually called a primary key. If you see up here on the Design tab of your ribbon, you have a primary key button. And in fact, if I click on that primary key button, it takes the key away from that failed to comment again, it reintroduces. Now, the primary key field is really important in a relational database. The primary key field is used to create a relationship between two tables. Values in the primary key field must be unique. What do I mean by that? Well, let's go back to datasheet view. What I mean by that is these values here must all be unique. We can't have duplicates in this column. And if I cut back to design view, I just want to look at a property that we haven't covered so far called index. Now as soon as I make this field a primary key failed, what it does in the indexed property is it sets a value, yes, no duplicates. So field is indexed, which makes it quicker in terms of searching and sorting. But also it does not allow duplicate. We also have a supplier code in the Products table. And if I go to data sheet, you can see here I've given the supplier code for each product. This value here is going to be used to cross-reference. Supplier table. So in other words, if I go to the supplier table, what it's gonna do is look up these values or cross-reference these values here. The reason why the primary key, the supplier code in the suppliers table needs to be unique is because when our cross-reference from the products table, for example, SQP DBS, is going to look up this record here. I can't have more than one supplier with the same supplier code because that would just be far too confusing for access. And in fact, it wouldn't work at all. So in a relational database, you would always expect to have at least one field set as a primary key. But the question is, is how do I create the relationships between the two tables? At the moment, I've got the supplier code there. But it's not actually cross-referencing this supplier table at the moment. So the next step is to learn how to create the relationships between the tables. Now to do that, we go to the database Tools tab on the ribbon and you click relationships. But what you need to do in the relationships window is add the tables that you want to create relationships between. Now you should have the add tables pane appear on the right of your screen. But if I close that down or show you how to open it, if it doesn't actually appear. You click on this add tables button. You should automatically have arrived on the Design tab. But if you haven't clicked on design there, and then you click on Add tables. And then what I can do is just drag these tables onto why relationship window. I can always resize them so you can see all the fields. And healthfully in the relationship window, you can see which field is the product key. What we've got to do is create a relationship between this field here and this field here. So that in the product table, we are cross-referencing all the information about that product supplier in the supplier table. So what I'm gonna do is drag and it doesn't matter which way you do this. You can either do it from here to here or here to here. But I'm going to drag this field name over to the corresponding field name in the Products table. And you'll get this Edit Relationships window. Now in this course, we are not going to get into too much detail about all these options in this dialogue box. All I'm gonna do is click on Create. And you can see that we now have a relationship between those two tables. Now I'm going to close the relationship window and it lasts me. If I want to save changes to the layout, I'm gonna say yes. One thing to note, when you're creating relationships between fields. So for example. Well, I have created a relationship between the supplier code field in the product's variable and the supplier code field in the suppliers table. The field settings need to be very similar, especially when it comes to the datatype. So if I go to the products table design view, you can see supplier code has a data type of short text and the field size of ten. And if I go to the suppliers table, go to design for you supply code. You can see that it's data type of short text and this field size is ten. So those need to match those two options here at the datatype and the field size, make sure those are exactly the same. The other thing that is worth noting here is just another bit of terminology. We already understand that this field here is the primary key. But if I go to the products table and I'll go to design view. We also understand that this field here is creating a relationship between the products table and the suppliers table supply code here, supply code here. Now this is the primary key. And so as this, but this, this field here that is creating the relationship from the products table to the suppliers table is known as the foreign key. Now there isn't a pattern up here that was a foreign key, is just the foreign key because it is the field that links the two tables. Now what I'm gonna do is close these two tables and reopen the supplier table. And now I have done that. You will see that I have little plus signs against each of the suppliers. And if I click on that little plus sign, what it does is show all the products that supplier supplies will do that for the others. So although the two sets of data, products and supplies are in separate tables, because of the relationship we created a one relationship. Access has now joined the data together. So it knows which product is supplied by, for example, catering supplies to you. And this is a much more efficient way of storing the data. I'm only storing the supplier details once, but it relates to all the products that supplier supplies. Okay, so have a go and setting up relationships in your database. And when you're done, we'll move on to the next lesson. 23. Build Relationships Between Tables Using the Lookup Wizard: So far in this course, I've promoted the benefits of a relational database versus a flat database in so much as it's a more efficient way of storing data. So I only need to store each supplier or once in the supplier table. And I can refer to that supplier many times in the product table. But let's look at a potential problem. If I was to add another product. And I know this product is Floyd Bye catering supplies to you. At the moment, I need to know this supplier code off by heart when I come to the supply code field in the Products table. Now, to all intents and purposes, this is not a very practical solution. If you had a 100 suppliers, there's no way that a data entry person is game to know off by heart the 100 supplier codes. And even if they had it written down with a bit of paper, having to scale through a 100 and suppliers and look for their code just doesn't seem a very efficient way of entering the data. So we're going to look at a different way of creating the relationships that overcomes this problem. The first step is actually to undo the work we did on the previous lesson. So I'm going to go to the database Tools tab on my ribbon and go to the relationships window. And although this relationship is correct between the products table and the suppliers table, I'm actually gonna delete it temporarily. So ugly, click on that line and then press Delete on my keyboard. And it'll say, Are you sure you want to delete the selected relationship from your database? I'll say yes. Now the reason I'm deleting this relationship is because the next way I'm going to show you to set up relationships won't work if there is an existing relationship between the tables. So I can close this window. I need to be in the Products table and I'm gonna go to the design view. I'm in the supply Caufield. And essentially what I want is a drop-down list of suppliers to appear in this field. So rather than having to memorize each supplier code of by Hall, which is completely unrealistic. What I prefer or what I prefer from our users is a drop-down list of supply names. So they can just select the supplier name and it will automatically associate that supply name with the supply code. So this is how you do this. You go to the data type for the supplier code and you change it to look up wizard. The first step of the lookup Wizard says this. This was a crate, so lookup field, which displays a list of values you can choose from. How do you want your lookup failed to get its values. First choice, I want the lookup failed to get the values from another table or query. Well, that's exactly what we want to do. In previous lessons. We've looked at how to type in the values that you want to appear in the drop-down list. Here we're going to pick them up from the suppliers table. Go to next and I choose the table, but I want to get the values from. So that's the suppliers table. So I go to next, and then I'm going to choose the field that contains the values that I want to appear in the drop-down list, which is the supplier name. I click on this button to move that field into the selected fields box. Then I go to next. Then I can choose the order in which the values appear in the drop-down list. So I'm gonna say pi supplier name, and I can choose ascending or descending. Well, ascending makes sense in this scenario. So I go to next, and there I can see my supply names. Now let's just read this text at the top of the dialog box. How, why would you lot columns in your lookup failed to adjust the width of the column drag edge, right edge to the width you want, or double-click the right edge of the column heading to get the best fed. Well, let's do about double-click. Look at this little tick box CFI ON ticket. You can see that although I only included the supplier name in this drop-down list, it's automatically included the supplier code, which if you think about it, is the foreign key in the Products table that will create the relationship with the supplier table. Now although I don't want to show the supplier code in the drop-down list. Accesses correctly identified that this supply code needs to be part of the relationship between the two tables sought and hide that, but it is in the background there. Now go to the next. What label would you like for your lookup failed? Well, we'll keep it as supply code. And we're not going to worry too much about the other options here. In this step of the wizard, click on finish. The table must be saved before relationships can be crated. Save now, yes. For go back to the database tools and I looked at the relationships window, you can see that the relationship has been reinstated. I'll close that down. But now in the Products table, I'm going to go to the datasheet View. And here we are on creating my new record and I want to pick the correct supply code. Well, now what I can do is I can choose the correct supplier from a drop-down list. Just widen that column for you. So you can say. So rather than having to know the supply code, I can just choose the supplier name from the drop-down list will say this is just a normal frying pan. And that's utensils. So they have a trope diverse or what mom will say that's 30 pounds Wholesale, 50 pounds list price. And will say it was first sold to die. If I do control semicolon, it will return today's date, which for me is the third of July in 2020. And then I'll tab and it will come down ready to start a new record. Okay, so in your database, I'd like you to do the same thing. Use the lookup wizard to create the relationship between the two tables, but also to give you that added benefit over dropdown list of supply names in the supplier code field. 24. Create the Product Form Part 1: In this section of the course, we're going to learn how to create forms. We're gonna create a form for the product table and a form for the suppliers table. We'll start with the product table. And we're going to create something that looks a bit like this. Ok, let's close this down and let's get started to create a form. Go to the Create tab on your ribbon, and then go to form design. The first thing I'll do is save the form. So I got to my safe button top-left will pull this form. Products. A form is split up into sections. At the moment we're viewing the detail section. But what you can also do if you right click on your form is the form header and footer sections. So if I click on that, you'll see I've got an area for the form header. We've got the detail section, and then I've got an error f or the form footer. So the header and the footer sections of the form where your place content that is static, it remains the same as you navigate through the different records in your table. And the header, I might put the logo of the company and in the footer, I might put the date or something. In the detail section. You're going to put the fields that will show the data in the underlying table. And as you navigate between the records, the information in the detail part of the form will change. You will also notice if you right-click, you have the option to show page header and footer. Content that you put in the page header and footer will only appear when you come to print out your form. It's not that common that you'll get a printout a form you're more likely to print out a report. So we're not been a focus on the page header and footer, rather we're going to focus on the form header and footer. So I'll just close that page header and footer down. The next part of this lesson is to learn how to add fields to the form that you'll see on the Design tab, evil ribbon, you have a button in the tools group called add existing fails. If you click on land, you'll get a task pane on the right of your screen called field list. Now if I click on show all tables, it then displays the two titles I have my database. So in this field list, you can expand the particular table that you want to add fields from. And then you can drag them from the field list into the detail section of your fall. Don't worry too much about where your positioning these fields at this current stage in the course. Because in later lessons we'll learn how to align them and distribute them in really easy and quick way. So just get them on to the form for now and then save what you've done. And then once you've done that, we'll continue with the course and I'll see you in the next lesson. 25. Create the Product Form Part 2 - Placing Controls: In this lesson, we're going to learn about positioning fields within the detail section of your form. The first thing to notice is that the fields are made up of two parts. You've got the label for the failed, and then you've got the field itself. So the label will appear as product code in the fall. But this part will show the actual data in the underlying table. If you move, are the part of the field, whether it be the label or the actual field itself, you'll notice that they move together, they're joined. But there are situations where you don't want this class side-by-side layout. You may want, for example, the label to be above the field. Let's just move this down a little bit. If you want to move these elements or these controls as the cold independently, what you do is you drag the gray square and you can then see you can move them independently. The next thing that will be useful to learn is how to resize your controls. You can do that. If I just move this over here a little bit and do that by just dragging these little handles that appear around the border of the control, do here as well. So if you had something like an address field that was likely to contain a very long string of texts. You might choose to widen the control. Now I'm going to undo the changes I've made here, just using droves led to do that. Just so we get back to the original layout Santos, You want to resize multiple fields or controls at once. And the several ways that you can select multiple controls. One way is to draw a selection box around the controls. So for example, if I want you to change the width of all of these fields or controls them here, what I can do is start in the top left corner, how dire my mouse button and draw a selection box around those controls. You'll then see that they're all selected. So if I resize one, it resizes the mall. And also you can move them all as if they were one object. Then if I click into an empty space in the form, it deselects them so I can then move them independent of each other. Another way to select multiple fields or controls is to select the first filter control, then hold down Control, and then click on others. So I can then move those, or I could resize them. Another thing you will need to know how to do is to delete controls and fields of your form. To do that, if you select the label and press Delete on your keyboard, it just deletes the label. But if you click on the actual field. The control that's gonna show the values in the underlying table and press Delete. It, deletes the label and field control. If you want to delete multiple controls again, you can select them however you want. Or the by drawing a selection box or using the control method to select multiple items. And then again, just press Delete on your keyboard. Now we're going to get rid of the existing controls. In our form, ugliness Control a to select everything. And I'm going to press Delete. I'm now going to show you how to align and distribute controls will add to our table. One easy way to align controls is to use this grid line. In the background here is a little bit fiddling. But it is possible. You can see these fields are now nicely lined up and so are these labels. Now I want to show you another way you can align trolls. So I'm just going to move up the alignment a little bit more as much as lab. Let's do this right? And then I'll select the fields. Then on the arrange tab of my ribbon, I'm going to go to the Align button and then say aligned to left. What that does is aligned to the left most control that you had selected. You'll also notice that it's kinda mocked up. These labels are little bit didn't align though. So if I select those three, go to align left, it's aligned those as well. So that's a nice easy way to align things, especially if you're finding it a little bit fiddly lining up the controls against these gridlines. Another thing you can do is easily distribute controls. I'll show you what I mean by that. Directly stem here. So I want an equal amount of space between these controls here. What I'll do is I'll select all the controls. Then I'm going to go up to size and space. This is still only arrange tab size and space. And I'll say equal vertical. So under the spacing section, choose equal vertical. And you can see it's nicely distributed to the control, so there's equal space between them. Okay, so what I want you to do now is just to play around with the options that we have looked at in this video. For placing controls within the detail section of the form. You're going to be looking at resizing, deleting a lining and distributing. Once you've done that, we'll continue with the course and I'll see you in the next lesson. 26. Create the Product Form Part 3 - Designing the Form: In this lesson, I would like you to start to design the form following the layout that I've given you here. Doesn't have to be exactly the same, but similar would be good. And one thing that's going to help you in terms of learning how wide to make controls is the ability to switch between your view. So at the moment we're in Design View. But on your design tab, if you go over to the view button, you can switch to form view. And this is how the form will look to your users. And for example, if I scroll through my records, you can see that the supplier code, which is actually the supplier and need to change that as well. This isn't quite wide enough. So I get back to design view and I'll get to make that a little bit wider. Also change this to supplier. So better. Label. And see at the bottom here we've got the ability to go through records one at a time and you can just make sure that the fields are wide enough. So it looks OK now for supplier. But there might be other fields that are not quite wide enough. For example, product line. Say everything there is about to design view. Is might that little bit wider. And also date first sold needs to be made a little bit wider. So it's a matter of switching between the two views just to make sure you have the right layout and dimensions for your controls. So once you're happy, changing all those things, save what you've done and will continue with the course. And I'll see you in the next lesson. 27. Create the Product Form Part 4 - Formatting Controls: In this lesson, we're going to look at improving the appearance of a foam by applying some formatting to our controls. The first thing we'll look at is the labels. So I've selected the first one, hold down control or shift them hold down Control and I'm selecting the other labels. And we'll go to the format tab on our ribbon. And it'll make the labels bold, change the color to black. Now I'm going to select the fields themselves. A word about the tick, all the product sheet Field Format tab again, we can have a light gray background so that color or do there. But we're also take the border off of the field. So I'll go down to transparent here. And you can always check how it's gonna look by going to view, full view. So fairly happy with that. But to design view, and the other thing I'm going to do is introduce some rectangles just to partition of some of the failed have product code is supply code in one rectangle. And product shapes of the rectangle will go across here. And then the rest of them here. I am going to have a separate rectangle. It's worth doing that if you have a lot of controls on a form, it just helps to almost categorize the fields visually. So design tab, when you put this menu of controls and we're looking for the rectangle or hover over it. It says rectangle. What I do is I click on it and then I can draw a rectangle around the area that I want. Doesn't matter how you have it. And then we'll draw another rectangle, rectangle. And then I will draw again around these fields. And then making sure that those are aligned and Leo was quite good. Let's just look at what it looks like in form view. Maybe I need to move things up a little bit. So one of the central selection box around it all. And I'm using my up arrow key on my keyboard. Full view. I can now see before rectangle a little bit of a problem there. So I can move this up a little bit. So it's a matter of playing around with things until you feel happy. But that's the kind of look that I want you to create. Simple design, but a formatting on the labels and end on the fields themselves, and then a couple of rectangles. So if you apply those changes to your form, once you're finished, we're onto the next lesson. 28. Create the Product Form Part 5 - The Form Header and Footer: In this lesson, we're in the products form and we're going to work on the form header and the footer. And need to be in Design View. To do this, we're going to add a logo to the form header. So I'm gonna go up to insert image. This is on the Design tab of my ribbon insert image. And I'm going to browse to my course folder. And there you'll see the camera of limited logo. Select it, click on OK, and then just click somewhere in the form Header. We're then going to add some text to the form header. So again, on the Design tab of your ribbon in the Controls group, click on the Label button, and then click somewhere in your form header. And then the label control, we're just going to write a terminal products and will apply some formatting to perform it. To the format tab on the ribbon. Will make the font bold, changes color to black, and will increase the font size to 12. Just need to extend the width of electro. And now I'm going to reposition both of these items. Can be quite close together. And then I can reduce the height of the form Header. The last thing I want to do in the form Header is to change the background color at the moment is gray. I want it to be white. So click on the background of the form header and that will deselect any of these controls. And then on the format tab of your ribbon, go to the fill menu, background color menu. And from here we'll choose automatic. They can always check to see how this will look when you're formed by going to the Home tab View menu for you. So for example, you might want to move that logo over to the left a little bit. So Alliance with this text here, designed for you, click on that control and I'm using my left arrow key on my keyboard. So you can play around with the alignment of that. Back to design view. And now we want to put the date in the form footer. I'm going to do this is click on date and time. This is on the Design tab of your ribbon date and time. And will choose how I want the date to show. You might find the include time is also ticked. We don't want the time, so on. So I want the data in this format. Click OK. And you'll see that initially it puts it in the header. So I'm going to select control x, two identical form for motor control V to paste. Then I'm sort of gonna put that aligned with that grid line there. So let's get back to form VA. And that's what you'll form should look like by the time you finished this lesson. So what I suggest you do is that you apply the same changes to your form and once you've done, will continue on to the next lesson. 29. Create the Products Form Part 6: Adding Command Buttons: In this lesson, we're going to learn how to put buttons on our form. We're going to have navigation buttons in the header, which will allow us to navigate through the different records and the form. And we're also going to have some record operation buttons down in the form itself. One button will delete the current record and another button will create a new blank record. Let's see how we can do this. Need to be in Design View Home tab on the ribbon view, Design View. Now to add a button, go to the Design tab on your ribbon in the Controls group, you'll see a button button. So we click on that and replace my first Butler Pat here. This is going to be a record navigation button. I want it to find the previous records which use go to Previous Record. Next. It says, do you want text or a picture on the button? Now if you look at the sample up here or choose Text button, looks like that, but I could change this text if I want to or if I want to picture than the button looks like this. Now that's what I want to go for, that is Lera next. And then you're asked to give the button and name the three letter prefix for a partner's cmd command button. We'll call this previous product for Finish button for us. Please come away for the moment. And then we'll go on and we'll create another new button. Up to the button button. And I'm going to click somewhere here doing Record Navigation. Go to next record. From next. And with this button I get two pictures that I can choose from. Either an arrow right, or go to next on a site, go to next. Next. And we'll call this command next. Product. Not literal finish. And I've got my two buttons. Move this into position. We'll do a little bit of formatting on the button. I can select both buttons, click on one, shift, click on another. Then I'm gonna go to the format tab or my ribbon. And we'll change the background color, so we'll use the background color menu here. I'm going to go for a gray. And also I want to get rid of the border around the button will show you what the border looks like. Forgetting Home. View, Form View. See I've got that little blue border around the buttons. We get rid of that. So go back to design view. On the format tab, shape, outline, transparent. Show you what that looks like. A report around the buttons. But to design VA, right, that's positioned days will track them sort of overtook there than there are in line with the rectangles that I put on the main form. So that's double the navigation buttons. And they're going to deal with the record navigation buttons, which we'll have in the main part of the form, the detail part of the form. So Batoche my button button, somewhere in the main form. And we do record operations here. First one I want to create is delete record. Next, we can have text, which would look like this. Picture to picture options here. You have a text on this one and I'm going to input the text, delete product. Next. Promote Cmd, delete, product. Finish. Another new button. Record operations. Add. Next. I will say this is also text product. Next, command. New Product, Finish tray. So I've got my two buttons and get and make them the same width. The formatting that over ploy to these buttons up here, I now want to apply to these buttons so I can use the Format Painter to copy the formatting. To do that, I select one of the buttons. The already has the format to the Home tab of my ribbon. And under use the Format Painter. If I was just going to copy the formatting to one button, just single click on this button. But as I'm going to copy it to buttons and LA, double-click on it. Then I click on the delete product button. And then the Add new product, but go back and click on the Format Painter button to turn that off. Gouge cry a little heading for these two buttons. So that's Design tab on the ribbon. And I'm going to use label control up here. And in here I'm just gonna talk record operations. I'll get a little green triangle that appears there as an indication there's a possible error with the control. Once it's selected, you get this little arrow button for click on it. I can ignore this error is just saying it's an unassociated labels not associated to field. That's fine. Just a little Heading, four or two buttons so I can ignore the error. Now I'm going to borrow the formatting that we've applied to the labels for fields to this heading here. So I click on an instance of the formatting on what to copy, get home. Click on the Format Painter ones because I'm copying it to one label. Click on the label. I want to apply the formatting to. The next thing I'm gonna do is draw a rectangle around these controls is first aesthetic reasons. In the controls gallery, I'm going to choose the rectangle, shape. Draw little rectangle around the trolls. And then I'll just check how everything looks by going to the form VA. And you can say I need to move this in a little bit to the left. Back to design view. So I'm going to draw a selection box around those controls. And I'm going to use my left arrow key just to move all of those controls to the left a little bit. But Form View. And that's the common Look we're after. But you can play around with your own format and you can decide exactly where you want things placed on the form. But if you could follow this general outline of the layout of your form, that will be great. Once you're finished, we'll move on to the next lesson. 30. Create the Products Form Part 7: Form Properties: In this lesson, we're going to look at form properties. To do that, go to design view in your products form. And then on the Design tab, click on the Property Sheet button. The property she appears as a task pane on the right of your screen. In this drop-down list, make sure you have formed, selected, and click on the Format tab. The first property we are going to change is record selectors currently that is visible. Yes, there to show you what the record selector looks like. Essentially this element here. Now it serves no purpose in this form. So we're gonna get rid of it. So back to design view. And I'm going to set that to no. Seven looks if it's disappeared, it has. The next thing we're going to get rid of all these navigation buttons at the bottom here. Design view navigation buttons will set to no. I also want to get rid of the scroll bars. You see the scroll bars here. So I'm going to set those to know either. The last property I want to look at is the capture property. The caption property relates to the text that appears in this little tab up here. Currently, the text we have here is the name that we gave the form when we save that. But we can create a more user friendly name and we can do that fired the caption property. So still on the format tab, we'll call this product form. I have to actually close down the phone, reopen it to see that change. So save it, close it, reopened up. And now you can see it says product form. One more button I'm going to put at the bottom here. Just I close button to design VA, Design tab of my ribbon. Click on the button, button. Form, operations, close form. Next, I'm happy with the exit doorway graphic on that button. Next. Close, product form. Finish, formatting or power from this button. Format Painter. Click on that button. Let's go to form view. Slave. The form that clicked on that button, it would close it. But that is the form finished. If you can get to that stage with your form. Once you done, we'll move on to the next lesson. 31. Create the Supplier Form Part 1: Using the Layout View to Create a Form: In this lesson, we're going to create the suppliers form, which is going to end up looking something like this. To create the form, click on the Create tab on your Ribbon, and then click on the form design button. When we created the previous products form, we use the Design View to create the form. With this one we're going to experiment with using the layout view. And I'm getting used to layout view with supplier form because it's a slightly more straightforward layout. Now once you've created the blank form, go to the Design tab on your ribbon and click on the add existing fields button. And that will open the field list plane on the right of your screen. The control tables and expand table suppliers. We're going to add the supplier code field to the form. Now I'm gonna drop it anywhere in the form. You'll see that it'll automatically position itself in the top left-hand corner. Now do the same for Supplier name. Just drop it in and it appears immediately below the supplier code. We want to do the same for first name. But what we want is we want a gap between this field and the two fields above it. So with the first name field still selected, I'm going to go to the arranged hamper my ribbon. And in the rows and columns group will see that the, some buttons that allow you to insert rows above or below, columns to the left or to the right. So what you've got to imagine is, is that this is a table in which you can insert rows and columns. So I glue in above, it inserts a blind row above the first name field. Another method for adding a field is the double-click on the field name. So if I double-click on last line, you'll see it appears immediately below the first name field. Next I'm going to add job titles on double-clicking. And then I'm going to add email address and then phone number. The next field I'm going to add is adress one, but I'm going to leave a blank row above that failed. So I go up to Insert above. Then I'm going to add address to then city and county. Then postcode. Then I'm going to add notes, but I'm going to leave a row above it. So you can see that adding fields is much quicker using this particular view, which is the layout view. What we're gonna do next is save our form. We'll call this form suppliers. Look like. And what I would like you to do in your database is get as far as I have with this form. And once you're finished, we continue with the course. 32. Create the Supplier Form Part 2: Formatting Controls in the Layout View: In this lesson, we're going to look at formatting our controls within the layout view. Another difference you may have noticed between the Design View in the layout view is that the fields actually show the underlying data. And even though on designing my form, I can still scroll through the fields to see how they look in the form. Once it's finished. It's very different to design view. If you remember where you just get the field names, in this view, you get the actual values that are going to show in the field. So what you can do is to scroll through just to make sure that the field widths are the right width for your data. Our labels are filled. Labels are obviously not wide enough. In the design view, what you have to do is go through and select each label and then widen them together is much easier in the layout view. If I just select one of them, I can position my mouse pointer on the right age of the label and drag it out. And you can see that the actually drags out all of the labels in the form. If I want you to change the width of the fields themselves, then I can also do that and they'll all end up being changed the same width. So that's much easier than InDesign va. What's also easier in layout view is formatting all the labels or all of the fields. If I click up here you can see I get a little black arrow that points down. If I click, it selects all of the labels. So then I can go to the format tab or my ribbon. That'll make the labels bold or could change the font to black. And it's done Same with the fields themselves. So if I select one of the fields that move my whereas pointed down just above that field and click select all of the fields. What I'm gonna do, the filters, I'm going to give them a bike great background color. And I'm also going to take the outline also up to shape, outline, transparent. And I can always go to my form view to see how that's going to look. The other thing I can do is change the gap width between the controls. To do that, you need to select all the controls within the form that I can do that up here by clicking on that little button top-left that selects everything that I'm going to go to the arrange tab. And I'm looking at control padding in the position Group. At the moment it's set to narrow. If I set it to medium, you'll see that it increases the spacing between each of the controls. So I could go to wide and it would introduce even more spacing. I'm actually gonna go back to narrow. And then I'm going to show you also how to use the control margins. Now the margins of the margins within the controls, the space between the edge of the control and the text. So for example, if I went up to control margins and I said medium, you can see that there's a lot more space between the text and the edge of the control. But that gives me another problem because now the controls themselves are not high enough for the text. And that can be solved very easily as well. But I have to make that change in the design view. You're going to have to get use to switch it between the two views because they both offer functionality that you're gonna find really useful. So I go to the View tab, go to design view. I gotta make sure everything's selected again under a click up there, I'm gonna go to the arrange tab on the arranged hap. When I'm in Design View, I get a size of space button. So I click on that. And I'm going to say to fit, now it's done a good job of resizing the field controls, but not the label controls. So I'm going to go back to my layout view. I'm going to click into the form and then select all the labels by clicking at the top there. And then I'm just going to drag it out. And you can see that I fixed that particular problem. What I would like you to do now is to get as far as I've got in this form, just applying the formatting that I've applied to my four. And once you're done with, continue on to the next lesson. 33. Create the Supplier Form Part 3: Finishing Touches: In this lesson, we're going to introduce a header into our supply form. But before we do that, we'll add some rectangles to our form. We go to the layout view in our form. Before I add the rectangles, just going to change background color of these empty rows. I can select all of the films that once a select one, hold down control rather than Shift, select the others. And I can change the background color to automatic. The rectangles that we were using in the product form can't be added whilst we're in the layout view. If I look on the Design tab, there is no rectangle for me to use. In the layout view. I have to actually switch to the design view. And then if I go to the Design tab, we'll discover that there is in fact a rectangle that I can use. Before I add the rectangle is I'm just going to increase the width of the form. I can do that by just place my mouse pointer over, write h of the form and drag, drag out the form width, right? So let's do the first rectangle and we put it around these two fields. Then I'm doing a copy, paste control C control V. And I can track down there for these fields controlled via again, theorem over these fields. And one random notes field as well might make the notes field slightly higher. 3k. You may remember when we were creating the product form that we introduced the header and the footer by right-clicking somewhere in the form and choosing form header and footer. If you try and do that in the layout, you don't get that option. So once again, I have to move to the design view to show the form header and footer. So I'm gonna click into the form Header. And I'm going to add the logo on the Design tab. I'm going to click on Insert Image. The cause I previously inserted the camera off logo. It's already there so I can just click on it. Click somewhere in the header. And I've got my camera of Logo, mostly going to add a label. And we'll say this is camera. Suppliers. Lie a little bit of formatting here. Format tab, black text, bold, increase the font size to 12. And then I just need to make sure these are lined up for play. You can play around with these self until you think you've got the correct layout. I was go back to the Form View just to check that it looks like K. Kind of happy with that. Well, change the background color of the header. So, but the format tab back ground button automatic. And or decrease the height of the form Header just by dragging this bar up. Three buttons to include in the header will have add new supplier and button for the previous record, button for the next record. So if you remember, you go to design tab for that. Click on the button, button or click here. So unless I record operations, add new record. Go for text. Add new supplier. Next. Add new supplier. Finnish. Word about the formatting too much at this stage. Then two buttons for navigation. So we'll say Record Navigation. Go to previous record. The picture next command, previous supplier that is presented there to finish. Leave that like that for the moment. Now the new button next to a Record Navigation. Next record. Keeping the picture next moment. Next supplier. Pressing answer to finish. Supply some formatting to this button. So you get to the format tab. To the background, color to gray. We don't want any outlined on the button, so I go to shape, outline, transparent. I can use my Format Painter double-click on it. This is on the home tab. Click on both buttons. Now want to decrease the size of these buttons, but I want to make sure they're the same size. So what I'm actually, let's turn the format patron. First of all, what I'm gonna do is right-click on this button. And I'm going to go to properties at the bottom of the menu. That gives me the property sheet for this button. And I'm going to change the height and the width to 0.75. And I'm going to do the same for this one rather than trying to guess the sign. So I'm gonna do it accurately by this property sheet. So then I can do a bit of employment. Select all of those buttons, arrange a loin top. And I'm also going to visually allowing this button with the edge of the rectangle there. It looks about right? But the best way to check is to go to the Form View. And that's the kind of look we're going for. There's a few form properties that we looked at when we were creating the product form, like getting rid of this record selector. Also if we didn't want to show these navigation buttons down here, we could get rid of those and changing the caption that appears all the tab above the form. We're going to go to design view for that now in the prompt sheet, which is already opened and I'm going to choose form. And record selector is this property here, general trends to no navigation buttons and it would change to no caption. I'm going to change to supplier. And I need to save, close down the form and reopen out. And then I get the caption there. No record selector. You can see a works to navigate. If I wanted to, I could add a new supplier. Okay, so I'd like you to finish off your form in your database. And once you're done, we move on to the next lesson. 34. Create a Form Which Includes a Subform Using the Form Wizard: In this lesson, we're going to create a third form. And the third form is actually going to contain data from both our tables. If I just go back to the suppliers table, you'll remember that because we set up relationships between the products type on the suppliers table, I could expand a supplier and it would show me all the products for that particular supplier. Now what we want to do in our third form is duplicate this effect of having a supplier. And then underneath it, all of the products relating to that particular supplier. I've closed down all the objects in my database. I'm going to go to the Create Tab or my ribbon. And we're going to use on this occasion something called the Form Wizard. Now the first step, the Form Wizard, you're asked to select which table you want to get fields from for your form. And you can see you've got a drop-down list here. So we'll start with the suppliers table. And for this form, I'm not going to include all the field names, but will include the supply and name, the contact name, the email address, and the phone number. Then I'm going to switch to the products table. And I going to include the product name, the wholesale price, and the list price next. So the second step of the wizard asks How do you want to view your data? If I view the data by supplier, I get the supply fields in the main form and the product fields in a sub foam. If I chose to view my data by products, all of the fields would end up in the same form without a sub form. The reason there is a difference between the views is because of the relationship we have between the tables. The supplier table is on the one side of the relationship, and the product table is on the many. So the supply code can only appear once in the suppliers table, but many times in the products table. Therefore, if I choose to view the data by supplier, I get the supplier fields in the main form. It appears once, but it relates to many products in the subforum. Next, you get two options for viewing your data in the sub form. A tabular or datasheet. Tableau is like a table datasheet is like a little spreadsheet. We're gonna go for datasheet next. And I'm going to rename my tables, my forms rather so form supplier with products. And the four letter prefix for a sub four is F sub supplier with products. So I normally make the main part of the name the same so I can easily identify that this is the sub form. For this form, I want to modify the forms design. So I'm going to click on that radio button and I'm going to click on Finish. Is my form. It's in design view. I can make any necessary changes of literally this default heading in the form Header. Design tab or my ribbon, insert my logo. I'll add some buttons. Record Navigation, go to previous record. So we'll pull this command previous. Supply products. We'll add another one. Record Navigation, next, recode. Promote. Next supplier. With products. Finish. So let's just change the position of these little buttons and format them held down shift a slept both. Format tab or my ribbon, change the background color to gray. I'm gonna get rid of the outline around the buttons, shape, outline, transparent. And then I'm going to change the background color of the header itself. So I click on the background, format. Background color automatic. Down in the detail part of the form I get a slight. These labels. Format tab on the ribbon where the text bold, change the font color to black. Select the fields themselves. Change the background color to a log per, i, get rid of the border. Now this is the sub phone screen show the products for this supplier. Will get rid of the labels or click on the label, press Delete on the keyboard. Then I'm going to direct the sub form over. And then I'll see what it looks like. The form view. So this is the effect you have. You've got the supplier details here. For navigated through. The suppliers. Can see you get the different products that relate to that particular supplier. A little bit more formatting, I think for my phone, not quite finished yet. Let's select these fields. Let's make them all the same width. So if I go to the arrange tab, size and space soils to why this form VU, that looks a bit better. It will move the form down a little bit. I'm using the down arrow on my keyboard. A little bit more space. Maybe I'll make this slightly less high. Just kick it back to form view to see what your changes like. Don't like the fact that I've got record navigation buttons both for the sub form and the main form. To take those off, we're actually going to have to go into the sub form itself. Can you see that there is an object for the sub fall? And for double-click on that, it opens up. I'll go to design view. Do I want to save changes to the form, form suppliers with products, yes. And actually closed that form so I could add the sub form to the property sheet. The property sheets not visible gated design Property Sheet. Go to the format tab. Navigation buttons. No. So then I'll save that sub foam, close it down, reopened the main form. You can see the navigation buttons have gotten there. I also want to get rid of this record selector. So I need to be in the main fall for that view designed for you. Again in the Property Sheet format tab, record selector. But to form view, you see now is gone. This form, I want to be read-only. I don't want people to make changes to the supplier information or the product information. Now to achieve that, I'll get back to Design View, Property Sheet or make sure form is selected their data tab. And I've got three options here, allow additions, deletions and edit, change order those two, no, save. But to form view, you'll see now that don't have a new record button down on the navigation buttons, still navigate between the products. But icon add new ones and I can't make changes typing away here and can't make changes to the data either. Just spotted problem with this list price can't see the value. So we will get back to design view. So let the subforum could just extend its width to form view. And now we can see the prices maybe slightly too wide. Spent too long on this. Because I'm kind of happy with that bit. In your version, you can play around with the exact size of that. And you can get as far as that with your form and save it. We will then be ready to move on to the next lesson. 35. Create Your First Select Query: In this part of the course, we can then look at creating queries. So I've closed down all my objects again to the Create Tab, who my ribbon. And for all our queries we're going to use the query design button. So I get an empty query and I should have, you should have the add tables task pane on the right of your screen. If that doesn't appear, click on add tables on the Design tab that will toggle on and off. So the ad tables task pane shows the list of tables in your database. And our first query is going to be based on the products table. So in LA, drag that onto the query window. This is called the Query Design grid. And the idea is that you can put fields down into this grid. And the fields that you include will be shown in the query. So how do I put these fields onto the Great, well, there are a number of ways you can do this. One way would be to double-click on a field name. So for example, if I double-click on product code, it appears in the grid. The other thing I can do is I can drag field down into the grid, or I can actually select a field within the grid. So if I go to the next available column, I can choose a field name from the list. I can add multiple fields at once. Or just extend the height of this table window. Say I wanted to add category, wholesale price at list, facilitate the first field known ODA and shift. It selects everything in between as well. And then I can drag onto the grid, just gonna delete these fields. So I select them. Now, what's the wire selected them? I place my mass points are above the first filled and wanted to select. And I get the little black arrow WHO Dima mouse button drag across, press Delete on your keyboard. You can also select multiple fields that are not consecutive in the list. So for example, if I select ID, product name, troll, keeping control health data, my keyboard, I can select non consecutive items and then I can drag them on to the grid again or delete them. Another thing you can do is if you double click up in the title bar of this little window that selects all of the fields. And then you can drag them onto the grid on the Design tab of my ribbon. I've got a view button. So if I click on that at the moment I'm in Design View. Good to datasheet view. And it's a view of the title is exactly the same data that's in my table is not a copy. It is just a view of the original data. The idea with a query is, is that you want to create a view of that table that includes either specific fields or specific records, or you might have a combination of both. So I'm gonna select all these fields and delete them. And we'll add specific fields to our grid. So double-click my supply code. I've got my product name, category, list, price. Let's look at the different rows within the grid. The first row is your field row that just gives the name of the field that is included on the grid table that shows you which table the field has come from. Especially important if you're including fields from more than one table in your query sought. So I might sort this data within my query based on list price, let's say ascending. Now I can decide whether I want a field to actually show in the query. But why would I want to add a field and then decide not to show it? Well. For example, on this criteria row here, we are going to specify that we only want to show utensil products, products that belong to the utensils category. If I run this now, you can see that I get specified filleds and the specified products for go back to design view and tick this button and run it. You can see they're all utensils. But is there any point in showing that field when I could name this query? So if I went up to save here, and I'll call this query utensil products. If I close this down, someone who opens this query or uses this query safe or report already knows that they are viewing utensil products. I'm much better. I'm ticking that. There's this down. So next time I run it, it doesn't show that field. But I already know that these are the utensil products. Okay, so I want each crate back query for me at these fields to the grid. You're going to sort by list price in ascending order. You can see that that's the case. They're, they're in ascending order. And you're only going to show utensil products. So you put that utensils criteria in the Criteria row. If you can create that query for me and save it, will then be ready to move on to the next lesson. 36. Create a Select Query That Includes Two Related Tables: In this lesson, we're going to create another query. The difference with this one is it's going to use two tables. For we start though, I just want to go to the relationships window in our database. So if we go to database tools, relationships. And I want you to remember the fact that these two tables are related via the supplier code field. And if I open up the Products table, which we're going to include in our query. The supplier code field currently shows the name of each supplier, but it's getting those values from the supplier table. What's stored in this old is these supply codes is just the lookup produces a dropdown which allows you to show the actual supplier name because it's a lot more user friendly, but the relationship is between the supplier codes. Now, we're gonna have to be mindful of this when we create our queries. Just close this down. So I'm gonna create a query in design view and I'm going to add the products table. I'm going to add supply code, product name, category, and list price. Now if I run this query, what I want to do is only show products from detrimental limited. So I'm gonna copy that control C and I'm gonna go back to design view. And I'm gonna paste that value under the supplier code column in the Criteria row, I'm going to click outside of it. And what you will notice is that axis always puts quotation marks around text string when it's criteria. Now if I run this, I don't get any results because what's actually stored in that field, the supply code field is the supplier code, although it's displaying the name of the supplier. So what I need to do is find out what that supply code is going to open up the supplier table. And detrimental limited is SQP DES Capybara. And I'll paste it into the criteria around. Before I run it, I'm going to close down the supply table. And now I'm going to run or view the datasheet of my query and you can see that it works. That can be a little bit confusing when you see a value in the column, but you can't use it as the criteria. And it's because of the relationships between the tables. You can get around this problem though by adding the supplier table to our query. I'm going to double-click on the supplier table there. And what I'm gonna do is add the supplier name to our query, track the in-between those two columns. So we're gonna get rid of that now in fat, not click on it and delete it. And I'm going to view copy detriment limited Q2 design for you and paste criteria into the Criteria row. And now if I run it, it does work. And the reason why it's worked is because I've used that field from the suppliers table as opposed to the products table, which actually holds the supplier name. And what you can see is that axis is now drawn a line between the two tables to show that there is that relationship. So I've got detriment limited products, but I'm only interested in products within the utensils category. In a query you can add more than one criteria. What I'm gonna do is add utensils in the category column on the same row. As detrimental limited records will only be returned by the query if both of these criteria are met, the supply name is detrimental limited, and the category is utensils. Let's run the query or you can use datasheet view. And you can see that I only get four results. But to Design View. And I'm going to save the query or call this query fisherman utensil products. Okay? And I'll close that query down. I would like you to create that query for yourself, introducing both tables and putting those two criteria in on the same row. And once you're done, we continue on to the next lesson. 37. Create a Select Query That Uses And/Or Criteria: In this lesson, we're going to create another query and we're going to look at all criteria. So Craig tab on the ribbon where we design and we're adding the products table. I'm going to have product name and category. Initially, I'm just going to look at utensil products. So if I run this, I get more utensil products. But in addition to utensil products, I also want to return bike where products? Bike where down on the next row, underneath the Criteria row, which is labeled. All. Good way of seeing how this works is to imagine that these are essentially separate queries. Row one, the Criteria row would return in the records with utensils in the category field. Wrote to the all row would return any records with bike where in the category field, and then it combines those two reference. So if I run this, see I've caught by quire and utensil products fat, all these rows underneath or are all, all rows. So I could continue to add categories if I wanted to. Let's save the query. We're going to call this query utensils. Or like where products. For a move on, I'm going to add a product to the products table. So I've opened up the Products Table. Cmr f 00 to two. Catering supplies to you. Baking pan by content, rover will say that's a bike where products 25 pounds to buy, 50 pounds to slam. 13th of the seventh, 20-20, close it down. Now if I run this query, we can see that baking Tim product is included in the query. I want to add more criteria to my query. I'm only looking for these products from a particular supplier are going to add the suppliers table. And then the supplier name is run it. And I'm going to copy catering supplies to you. But to Design View, and I'm going to paste that into the query. So let's see what results again. Now interestingly, if you look at that, although all the utensil products are from catering supplies to you, I am getting bike where products, as well as from catering supplies to you as it might baking ten that I just created. Most of getting bike where products from direct baking supplies. Even though you might query, I have specified catering supplies to you. You may have guessed why this is happening. It goes back to this way of looking at this grid, is that each row is a separate query. It isn't, but that's a good way to say it. Any record that meets these two criteria, this is and criteria because it's on the same row. So if the categories utensils and the suppliers catering supplies to you that's returned by the query. Then it goes down to the next row, the or own. It says already records bike where products, no specific supply name. So essentially it's returning backward products from any supplier. I only want buy products from catering supplies to you. So I need to copy this down onto this run. And I would just get that one bike where product now that we just created, there is a shorter way of writing this criteria. What I'm gonna do is get rid of the second row altogether. And I'll go to crank the all criteria within a single row. Quote you tensors. And I'm going to say all like where or run it, I get the same results. So that is a lot more efficient way of creating that query. Okay, I'm gonna save it. I would like you to get that far with your query. And once you're done, we continue on to the next lesson. 38. Create a Select Query That Uses Comparison Operators: In this lesson, we're going to look at using comparison operators within a query. I Angular Close last query that I was working on and under the Create New Query Design View. And I'm adding the products table. I want product name, list price. Didn't the Criteria row gonna write greater than 50? Greater than 50. And if I run it, say, I only get the products that have a list price that is greater than 50. If I wanted to say greater than or equal to 50, I'll put an equal sign between the greater than symbol and the 50. And the does then include my baking ten. You'll see that without the equals by contained wasn't included. With it. It is will save it. We are a 50 or more. Close this query down. We're going to create another one using a comparison operator. Let the products table again. I'm going to add the product name and date for a solid. And I want all products that were first sold before 2016. So we say less than 01012016. Notice that it puts these hash symbols or PAM symbols on the other side of the dates. That's completely normal. Run this. There we are. Those are all the products that were first sold before 2016. So I can save this query. First. Before 2016. A case or two queries you've got to create where? One for products that a 50 pound more and then the second one, products first. So before 2016, if you get both of those queries completed, we'll move on to the next lesson. 39. Create a Select Query That Uses Between And Criteria: In this lesson, we're going to create a query that uses between and criteria. So consecrate tab on the ribbon. Query Design dilemma in the Products table, product nine. And take for sold. We only want to see products that have a date first sold in 2015161718. For that in the Criteria row, we're gonna right between the first day of 2015 and the last day or 2018. And we get those little hash symbols around the dates. That's normal. And I'll run it and there we are. We have those dates. Fayyad, list price. I might say that all those products, I'm only interested in list prices between 2550 pounds. I would write between 2550, so it's inclusive of those values, 2550. If I run it, you can say that I get those particular products. So that was the point of this lesson. Just to learn that between Ann and syntax or slave that query. We're just gonna call it between. And easier to refer back to the point, OK, if you want to get that far in your particular database, once you're done, will continue onto the next lesson. 40. Create a Query That Uses Wildcard Criteria: In this lesson, we're gonna learn how to create queries using wild-card criteria. Great tab on the ribbon. Query design. I think the Products table and all we need for this is the product nine. Here's an example of world called criteria, an asterix in and then pan. Now the asterisk stands for any number of characters. So this reads as any number of characters before the three letters. P-a-i-n basically means enzyme pan for click outside the cell. You can see that the prefixes, the criteria that I taught him with like and puts the criteria in quotation marks. If I run this criteria now, you can see that it's returned. Anything that ends in those three letters, PAN. Design View. If I had written p n star, that would mean that the product nine would need to start with PAN. And if I run that, I don't get any products because none of my pan products start with the word pan. But for example, just to show you, if I said I want you to all products beginning with the letter S star and run it. Then I get those five products. So you can have Begins with or ends with. But you can also have contains. What we're gonna do is we're going to look for any products that are knife products. And the way I'm gonna do that is put the three letters KNIME between two asterix characters, click outside it and run it. And what you'll see is wherever KNIME appears somewhere in the product line, then it returns that character. So I get noise and noise products. This is a really useful way of specifying partial criteria in your queries. I'm cannot add date for sold. We get rid of that criteria there. You just want to show you that you can use these wildcard characters within dates as well. If I wanted all products that were sold in 2018 BY can use wildcard characters for the first two parts of the diet. So The day is an asterix, the month is an Asterix, but the year is 2018. When I run it, you'll see you'll get those to 2018 dates for 2019. It would work in the same way, 2019. So can be used for dates as well as for text fields. What we'll do is we'll save this one here. So you've got an example of a query products. First. So in 2009, team will close that down and we'll try another one. So you put something to refer back to the products table again, product name. And we'll do the balm of noise. So that was a contains criteria. Any products that contained the three letters KNIME together in that sequence or run it just to make sure it works. Does we can save that as query color. Now there's nothing stopping you from creating more wildcard queries than I have done here. But if you can at least create those two once you're done, we'll continue on to the next lesson. 41. Create the Products Report Part 1: In this lesson, we're going to create a report for the products table. And it's going to end up looking something like this. And forget, a report is an output from the database, something you can print out or create a PDF from an attached to an e-mail. Okay, let's get started. Create the report. Go to the Create tab on the ribbon. Click on the report design button, and you should get the field list come up on the right of your screen. If you don't go to the Design tab, click on add existing fields and it should appear to get the two tables that you've got in your database. And if you expand the products table, we want to add the following fields to our report. The first field is product coats or double-click, adds it to the detail section of your report, then product name, then category, and then list price. What I'm gonna do is select all those fields, drag them up to the top of the detail section. And I'm going to view the report as it is. So you've got the Design tab, you view button report for you. So you can certainly see one record. Well, if I scroll down a bit more, you can see there's the second Rehfeld. There's the third ruffled, not quite the design we were after. So let's go back to design view. One thing we could do is close the gap between each record. If I scroll down here, you can see there's your page footer divider. So if I drag that up and then get back to your report, view, this a little bit better because at least we don't have a massive gap between each record. But in the little preview I gave you earlier on of the report that we wanted to create. What we had is column headings, product CO for that name, category, list, price, and then each record shown as a separate row underneath those headings. That's the look we want to create. So going back to Design View, to create that look, we need to move the field labels into the page header. They'd think that would be nice and easy to just sort of select a label and then drag it up into the page header. Then you'll see it also drags the actual field with it. So we can't do that. I'll undo that controls said, well, I'm going to do is select those four labels and then cut them Control X, click up into the page header, control V to paste. That leaves the fields in the detail section. Okay, I'm going to click into an empty space in the header. And we've got a column. Move these around about. Product code will have the first filled heading, then will have product name. It's going to be quite wide and will have category. And then we'll have a list price. You can see that the report automatically widens to incorporate that list price label. Underneath those headings, we can put the actual fields. Product code, there will be slightly less wide product name needs to be a bit wider. Category. Preposition under the heading. And then lastly, list price. Thinking might move that over a little bit more. And then once they're all in position, we can go and look at our report view. And that's much more of the coin and look that we're after, still a little bit of work to do though. But to design v_a, again, what I can do is drag this page for 8A divided upper bed. You can see that immediately decreases the gap between each record. I can do it even more. In fact, let me get rid of those borders around each field. Well, I held down shift to select multiple controls within my report. And then I'm going to go to the format tab or my ribbon shape outline. Transparent. But a formatting on the Headings again, holding down shift. And I'll make the font bold. Going to change the font color to black. Let's get back to home port view. Let's look in more and more like the design that we were after. Masculine line underneath these field headings. Just to make that a little bit easier, I'm going to drag down the details section a bit just to give me a bit more space and fat what I can do because I've got loads of space at the top here. Neutral selection boxes Tang around them and I'm going to drag them up. And then I'm going to line underneath the headings. So for that, I'm on the Design tab of my rhythm. I mean the controls menu. I'm going for line. Now the trick here is to hold down shift if you want to create a straight line. So I'm going to start out here. Oh damn my mouse button. Drag across. Here. Let go. We get a nice straight line. See what that looks like. Noise slowing across my report. Now I can close that gap up abet report view much more like the look I was after 12. A little bit of formatting here in terms of alignment. And call left aligned. Format. Left align. That looks better. I want to change the background color of these alternate rows to a light blue. Click into the detail section. Format, will turn a row color, choose a light blue. But my report view. And you can see that it's changed the background color of those alternate rows. At this point, I'm going to save my report. So I got to the save button top-left there, and we'll call this report product left K. And that's as far as I'm going to go in this lesson. If you could get as far as I've shown you in this video with your report. And once you're done, we'll continue on to the next lesson. 42. Create the Products Report Part 2: In this lesson, we're going to learn about adding content to the report header will also look at some Page Setup options. At the moment we've been working in four go to design view. You'll see we'd been working in the detail section. And also in the page header section. We haven't entered anything in the page footer, but the Pate headroom flutter, as the name suggests, is where you put content that you want to appear at the top and at the bottom of each page. So it makes sense that you'd have the column headings at the top of each page. If the report went over more than one page, you've also got the report header and footer. Now in the report footer, we're gonna put the camera company logo. And we're also going to put a heading for the report. The report header will only appear on the first page of the report and the report footer would only appear on the last page of the report. To edit the report header, you first of all need to display it. And to do that right-click anywhere in your report and click report header and footer. So here's the report header and we're going to put the company logo Intuit Design tab on the ribbon, insert image. You either need to browse for the logo or if it comes up in the image gallery, just click on it there. Click somewhere into the header, struck that page header, divide her up a little bit. Mostly going to insert a label control. Generally we'll credit or heading product list. The formatting Format tab or my ribbon mic, bold. Color to black, increase the font size 12. Extend the width of the control over a little bit. Maybe upper bed, reduce the height of the report header. And then we can go and see what that looks like. You need to change the background color of the report header. So back to design if you go to Format. And here I can change background color to white. Back to my Form View. And that's the kind of look on after I think I might have the date up in the top right-hand corner. So back to design view. Design tab on the ribbon Date and Time button. I don't want to include the time and the date format shown there is falling at the automatically appears top right of the report. Let's see how that looks. Let's move that over a little bit. We might report slightly narrower. We could also move that down a little bit. Anyway, you play around as much as you like with the layout of things. So that's the report header. We're now going to look at Page Setup, settings. Design View. Click on the Page Setup tab. See column margins menu here. So if I went to Normal Form View, this is slightly bigger margins on either side. Let's go for wide margins again. So Tajik can only really see these things in print previews of Achieves Print Preview message here the section width is greater than the plate width. Okay, well we can fix that in a moment because you've got coil wide margins of either side, does actually nicely show all the data is just the the sections in reality on a wider than they need to be. So close print preview up here. Now to get rid of that problem with sections being too wide, what we can do is actually just this width here, just by dragging in the right side of the report. Let's have another go print preview that we didn't get that message come up that time. So that, that's working quite well. Let's close print preview now, but page setup, if you want to specify custom margin settings, rather than using the margins menu go to Page Setup. And then you can specify left, right, top and bottom margins. So for example, if I want the left margin slightly wider than the right, to set that to 25. I'm going to get a message. In the sections are too wide. I didn't not desecration, but you can see on this side, there is a wider margin then on the right-hand side, but my date is no longer showing, so please print preview. And I'm going to draw that data in a little bit with that control there. But print preview looks better on your clothes. One last thing we'll do on this report is just change the caption as it appears in this little tab here. We did the same for forms. Close down the existing fields tab, and I'm going to open up the property sheet in this drop-down list. I'm gonna make sure that report is selected. Go to the format tab, caption product list. Under the cyber top-left. Closer. Reopen it. And I get that nice caption at the top of the report there on its little tab. Remember, you can switch between these three different views that we've been using, design view, report view. And then okay, so what I would like you to do is to complete your report as I've shown you how to do it there. And once you're done, we'll move on to the next lesson. 43. Create the Suppliers Report Using the Report Wizard: In this lesson, we're going to create a report for the suppliers. And the report we're going to create will look something like this. Okay, let's get started. We'll begin by clicking on the create tab and we'll go to the report wizard in this lesson. In the first step of the report wizard, you need to choose the suppliers table. We're going to add specific fields to our report, starting with Supplier name, first name, last name, job title. We want email address. And then we want phone number. Next. We're not going to apply any grouping to our report. So next, what we do want the suppliers in ascending order of supply line. Next, the layout we're going to go for in this report is tabular because we just want a simple table because we have quite a few columns. We're gonna go for landscape orientation. And I have this option down here, just a field width, so all filleds fit on a page. Next, I'm going to name the report. So report suppliers. And I want to preview the report so I'm gonna finish. And it does a pretty good job of creating the report for me. But I'm going to tidy up a little bit, close print preview. In the report header. I'm going to delete the default heading. And I'm going to insert the camera off logo design tab on the ribbon. Insert image, camera of Logo will put it here, mostly going away at a label that's from the Label button in the control group. And the label for this report is supplier list. But the formatting Format tab or my ribbon. My textbook 022 black, joins the font size to 12. Little label box a little bit bigger. Let's move that up there and little bit. Contract the page header divider up. And last thing I need to do on the header is to change the background color. So Solon the Format tab, go to the background color, drop-down and choose automatic. Let's deal with the page headers now. Select them all holding down shift, format tab or my ribbon, I'm changing the font color to black, going to make the headings bold. Well, I'm also gonna do in the header is add a little line drawn down the detailed divided. They're just dragging it down. Design tab on my ribbon and the controls group or choose the line control. Remember to hold down shift to draw straight line, straight across the report. And then I'm gonna move it up with my help Harrow my keyboard and drag detail, divide her up a little bit. And now I'm just going to preview how the report will look. So in the View menu, report, view. And there we are. There's one line, pretty happy with all the column headings. And I'm pretty happy with the report header. But to design VA on problem I did notice was with the alignment of the email address field. And I'm gonna make that left alliance, the Format tab on my ribbon, left aligned. We've also got to do that for job title. Format tab again, left align. Like gets all the alignment sorted out. Quite a bit of a problem here. The email field is not wide enough. So design view. Now what I'm gonna do is reduce the width of some of these fields selected or didn't shift both the FirstName label and the field. We're just going to reduce the width. Same with lastname. Just reduce the width, the bet, and then drag over, say my job title, right over a little bit. Then email, try go over a little bit and make it a little bit wider. Now you'll see all the email addresses. We can be nice to have a little line underneath the report. Back to design view. And what have to do is to place that line in the page footer. I'm going to copy the line that I've got in the page header. So I've selected that they're clicked on it. Control C, down to the page footer, control V. And there we have both of my lines. And you'll notice also that by t4 access puts the date and the page number in the page footer come down to a line, some of these elements now, just going to draw the date in a little bit. And then I'm going to select the date, supplier name, field. I'm holding down shift supply Name Label. And then my two little controls in the report header. I'm going to go to a range, a line, line to the left. Let's see how that looks. Everything's lined up now. Maybe the logo needs moving across a little bit. So click into an empty space on the logo. Use the left arrow button on my keyboard, back to full view. You can see that looks a little bit better. One more thing to do and that's to change the shading on the alternate rows. That moment is gray. I wanted blue back to Design View. Clicking to the detail section. Select where it says details there. Format will turn a row color, choose a light blue. And there we have it. Something that is worth watching out for is the little green triangle that is appearing up here in designing VA. It indicates that there's a potential problem with your report. If I go to this warning button, you can see that the report with these greater than the page width. And I go over here and I'm going to reduce the size of both the phone number label and the field. And I might move the page numbering in a little bit as well, and then reduce the width of the report. Now I can see that there's a warning is gone and I could always get back print preview just to check that things look good. To notice that each time I click on the preview of the report, it zooms in and out. But when I zoom out I can see that everything looks fine. But closer Print Preview, and save the changes to my report. Last thing to do is to give the report a prop caption Design tab on the ribbon Property Sheet button. Make sure report is selected in this drop-down list format, Tab Caption, property supply report. Save again, goes down the report, reopen it, and you get that captioned appearing in the Report tab. In summary, in this lesson, we learned how to create a report by using the report wizard as opposed to creating the report from scratch using the report designed button, the report wizard makes it easier in the initial stages of the report setup. I would like you to create the same report in your version of the database. And once you're done, we move on to the next lesson. 44. Create a Navigation Form Part 1: In this lesson, we're going to start to create our navigation form, which is gonna look something like this. In fact, it's going to appear automatically when we open the database, the navigation form, we'll open up our product forms and reports and our suppliers forms and reports. Let me just show you how it works. Or click on a button, will open up a form. Click on add a new product. It will open up the product form, but it won't show existing data, is just ready to accept new data and new referred for click on products report, it opens up products report preview mode. And if I click on this button here, it allows me to export the product report to PDF format. I won't do that here. As well as buttons for the products forms and reports will also create buttons for the suppliers forms and reports. Okay, let's get started to create the form. Go to the Create tab on the ribbon, and then go to form design. We need to show the form Header, right-click into your form and choose form header and footer. In the form header, we're going to insert the cameras off loco and the Design tab of your ribbon. Go to Insert Image and select your camera of Logo. And place it in the header, will change the background color of the header to white. Click in the background there, get to the format tab, go to the failed button and choose white. Let's go ahead and add some buttons to the details section of the form, but to the design tab. And in the Controls group click on the button button. And we'll create our first button about here. Now this button is going to open up the products form. We want form operations, then open form. Next. Choose the form that you would like to open foreign products next. And then it asks you whether you want the form to open showing all records or to show specific data. We want the form to open showing all records. Next, text score is going to appear on the button. We're just going to say View products. Next. Give the button the meaningful name command. Oh, plan. Products form. Finish. Okay, we'll resize our button. Drag it into position. Let may care about the height of a square and three squares wide. Little bit of formatting Format tab on my ribbon. Change the background color to gray. Takeoff ANY outlines on the button, shape outline button on the format tab. Transparent will save a form. And we'll call this form navigation. Let's see how the formula works. Home tab on the ribbon view button, fall view. And I can click on the button to see that it works. And it does indeed open up the product form. Let's close that down back to design view. I would create another button that opens up a different version of the product form. And in that new version, you're not going to be Ubuntu view existing records. You are only going to be able to add a new record. And to do that, we need to create a copy of the form. I'm right-clicking on the form products, the navigation pane, and I'm gonna copy. Then I'm going to right-click and I'm gonna paste. It's gonna ask me to name the copy of the form. I am going to call this form add new product. And then I'm gonna open that form up, will have to make several changes to this form, will go to the design view. And the definitely don't need delete product. But I'm going to keep at new product. I'll just make this little rectangle a little bit shorter. And the property sheet, which if it isn't open, we'll show you how to reopen it. As a reminder, Design tab on the ribbon Property Sheet button. What you want to do is make sure that form is selected in the dropdown. Then go to the Data tab and look for the data entry property. We need to set this to yes and save our form. A thick will also change the caption for or form format tab and will change the caption to add new product. And then we'll save the form, see what the formulas like in full view. And what you'll notice is, is that it's not showing any of the records. Try and navigate, in fact, tells me that I can't just add a product code, CMR f 00 to three. I won't add any other details for the product and not close the form. And you'll see that it adds that new record even though it's not complete to the products table. This new form can only be used to add new products, not to view existing products. Okay, now I've created a form and we'll create a button for it or my navigation form, view, Design View. Design tab on your ribbon. In the Controls group click on the button button. And I'll add a button to the detail section of my phone. Go for formal operations. Open form. Which form would you like to open form, add new product. Open the form to show all records, even though it's not going to have any records. That's the option we want. Next. Text. Add new product. And we'll call this command open. New product form. Finish. Let's resize that a little bit. And I'll borrow the formatting from this button using the Format Painter selected the button, go to the Home tab on the ribbon. Format Painter, click on the new button, and it'll look exactly like the other button. The next button I'm going to add to the form will open up the products report in print preview mode. On top of my ribbon controls group button, button, click into form. Report operations, preview report rather than open report. Next report product list. Next text, and we'll just call this products report from next command. Open Products report. Finish. Again, I need to resize and format part of the Formatting using the Format Painter. And all three buns, let's assign one more button for the products. Just going to extend the width of the formula for Design tab or my ribbon button, button. Click on report operations. Send report to file. This is the button that will allow us to export a report to PDF, for example. Next, choosing the report product list next. And I'm going to choose a picture for this one. I'm going to choose output the text. Next. Moaned. Export, product report. Move that and copy the formatting. Going to create a rectangle around these buttons. For aesthetic reasons. Then nothing can move to whole lot. Withdraw a selection box, use the down arrow on my keyboard. And then I'll put a little label at the top here. I'll type products. But a Formatting Color accidentally press the Format Painter. There are Kluckhohn and again, that's the product's part of the navigation folk pleated. Let's just see what that looks like. Form view, or click on these buttons. That's what product form. As my add new product form. Products report opens up the report nicely, is click on the Export button. So what you will do here is you choose how you would like to export the file. And then when you click on okay, it would allow you to browse to where you want to save the PDF. I won't do that here. Cancel. Let's save the changes we've made to the form. And that is as far as I'm gonna get in this lesson, the next lesson we'll do the supplier section. But for now, I would like you to get as far as I have with this form. And once you're done, we'll move on to the next lesson. 45. Create a Navigation Form Part 2: In this lesson, we're going to create the buttons for the supplier forms and report. We're still in the navigation form. And I'm gonna go to design view, Design tab on the ribbon. Click on the button, button in the control group. And the first one we're going to create is going to open the suppliers form, chosen form operations open form. Next, suppliers form. Next, open the form to show all records. Next, text. View suppliers. Next, command, open suppliers for soils and format. The Next button is going to open a version of the form that will only allow you to add new suppliers. Copy the form. Right-click on the form in the navigation pane. Copy. Right-click paste creatinine for the form. So form at new supplier. Open up that form. Go to design view. Will leave the former as it is over in the property sheet. Make sure form is selected in this drop-down list, then go to data. And to only allow data entry in the data entry Properties select yes. Will also change the caption from the Format tab. And we'll say this is Add New. So if the foam close that form down and we'll create a button for that form design tab on the ribbon button button. This time we're opening up the new supplier form. Tx will be out new supplier. Next, new supply fault. We position it and resize it. The next one, we'll open the supplier report in Preview mode. Design, button, button, port operations, preview report. Next. Suppliers report next, text. Supply report. Next, command, open supply report, finish, reposition and format. And then the last button will allow us to export the supply report to PDF. Port operations. Same report to file. Next suppliers report, we gave picture output text. Next. Export. Suppliers report. Finish. Reposition. The formatting. I want a rectangle around these buttons. So I'm gonna copy this rectangle, control C, control V. And I'll move the whole thing down. And I wanna labels or copy this Control-C, Control-V. And we'll say this is suppliers. Let's see how the formula works. Let's check buttons, work. Supply form at new supplier foam, supply report, export to PDF. They're all working. We do a little bit of an ointment. Wanna get this logo aligned with these little subheadings. That looks a bit better. And also get rid of this record selector and the navigation buttons at the bottom. So design view, make sure that form is selected in the list. If you fall record selector, no navigation buttons, No. Struck it. Form view. That's exactly how I want to also give the form caption. Pool this navigation form. Save a closer reopen it. Navigation form. Can you please get as far as that with your version of the navigation form? And once you're done, we'll move on to the next lesson. 46. Create a Navigation Form Part 3: In this lesson, we're going to make further improvements to our form. Quite a bit of a typo up here you may have noticed, meant that first of all, navigation. And we want to change the way in which these forms open up, especially the forms that are launched far these buttons. And in each case we're gonna go to the other tab and change the pop-up property to yes. And that means that the forms will always open up in front of any other open forms and the format tab, we also want to change the auto center property two. Yes. So save that and I'm going to do it to all of the forms that are navigation form opens. Do one more in this video. Design View. Should go to the other tab when you set your pop-up setting to yes. And then new format tab set auto center to yes. Save. I'm not gonna go through every single form in this video repeating the same settings. But in your database, you will need to do that if you can also do it for the reports that are opened fire, the navigation form. Once you've done those things, if you view your navigation form in form view, you'll notice that the view button is grayed out to switch back to design view, when you're in pop up, you need to right-click on the form and go to design view. Go back to form view. And you'll see that when I click on these buttons now, the objects are automatically centered and appear in front of my navigation form. The next thing we're going to do is set this navigation form to automatically open. When you open the database. To do that, go to File Options, current database, display form. Select your navigation form. Click on okay. It says you must close and reopen the current database for the specified options to take place. So I'll do that, close the database, reopen it. And you'll see that the form automatically opens if you want to hide this navigation pane from your users, because you have all the relevant buttons on your navigation form. You can also do that. Go to File, Options, current database, and down here, untick display navigation pane. Click on, okay. Okay. Close. File. Open, and the navigation pane has disappeared. So you have more control over which objects your users can open. Ok, so I would like you to get as far as that in your database, setting, the forms and reports to open in popup mode and auto centered. And also setting the navigation form so that opens automatically and hiding the navigation pane. 47. Student Project: Teala Training Database: In this part of the course, you're going to create another relational database. The files that you need to complete this project are the te la training database files. These ones down here. What you want to do once you've downloaded them is opened up. This Word document here t less training, database exercises. And this Word document will take you through the task that I want you to complete with these particular files. The tasks very much mirror what we've been doing with the camera of database. And if you complete this particular project will really help you embed the knowledge that we've gone through during the course. Please feel free to upload screen grabs of the work that you've done so that myself and other students can comment. But for now, we'll say is good luck with your task and I hope it goes really well.