hey guys and welcome to the next video in the January 2024 Series in this video we're going to be looking at the second part of paper B where we're going to be looking at the second form where we're going to use queries to create information for fields and hopefully get it so that we can see all of the gardeners and give them the reviews that they're entitled to so let's look at the question we're asked to create an input form that allows someone to add a garden or r view the form must not include any validation for any Fields the form must not include any automated routines to save the data there must be a combo box to select Gardener and when the gardener is selected information must automatically be displayed on the formont we're also told of a specific set of data that must be displayed to them and that there must be a combo box to allow the person to add a new grade review and then finally there must be a message displayed to say whether the average monthly meeting should be applied or not to that Gardener so let's move into the cre of this form first of all we're going to create a blank form when we go into the blank form we're able to add controls that we want to do so in the design view as I'm here you'll see that I'm just stretching out the form area so that we can add all of the controls we need the first control that we're going to need is going to be a dropdown box this drop- down box will allow us to select from values that are in tables or in query queries that have been created or we can type our own in this instance we're just going to click on cancel and we're going to go in and we're going to edit and control this drop- down Box by using the property sheet in the property sheet under the all tab you'll notice when you scroll to the very top we have the name of C combo zero we're going to change this to be CBO to represent combo and then we're going to put Gardener well once we've done that we're going to then choose the control source for this information that's going to be displayed inside of the actual combo box I'm just going to change the label here to make it so that it is representative of the gardener so we essentially go into the property sheet and edit the caption element to be Gardener we can make this bigger and we can make it smaller and we can use the handlebars to move them around if we need to the next bit is going to be one of the other values that we need to put into our form which is going to be showing information about the most recent review that person has you can see that this overlaps here so I'm just going to click on the larger handle and drag the label away from the actual text box itself we're going to add another column here which is going to allow us to show the number of reviews that they have had in the past so we're just going to take out the provided label information and just type in number of reviews as the label again we've got overlapping here so I'm just going to click on it and drag it over to the left a little bit and then the final one of the required displayed elements for a text box is going to be the best review so we're just going to change the label here to best review don't worry about the Unbound elements of this we will be providing some features inside of here and some formulas so that this will give us the Vital Information we want and we're going to do that later on once we've created some of our queries that we're going to have to create which you're going to add one more label in here and one more text box and this is going to be the next review or the new review date and then we're going to add a combo box and this combo box and we want to typee in the values that we want and as we've been shown inside of our brief for Part B we must put in review grades between one and three so I'm just going to put those in the value boxes there for us and then I'm going to click on next once I've clicked on next I'm just going to give it an appropriate label so we're going to be using CBO prefix again because is a combo box and then we're going to do review now if we view this we should notice that there's nothing in the gardener and the only thing that's really displaying in think is the combo review box and all of the fields are empty now we are asked to make it that when we choose a gardener that all of those information boxes are filled so we're going to go back into design view but first of all we're going to save it using the frm prefix and we're going to put on ADD review so now we go back into the design view for this form the next thing we're going to need to do is create the query we're going to use the query there going to call from Gardener and the review tables and we want to know the gardener ID and then what we're going to do is we're going to create three new areas or fields that aren't necessarily inside of the table set that we've got they will however refer to some of the fields inside of the tabls but we'll do some other calculations using some of the features within access so the first one we want to look at is most recent review if we put that in and then a colon that's going to be our heading inside of this query and then we want to select the review date now this is obviously going to come from the TBL review then we need to create another new field and in this instance we're going to do do number of reviews and then we're going to put colon and then we're going to put in the review grade and then finally we're going to put in our last field which is going to be looking at the best grade and again colon after that and then we're going to look at the review grade again these two are very similar to each other but if we go up to the toolbar inside of this query we can allow an additional row to be provided which is totals in here we get a few more options we can Group by but what we're interested in doing you want to see the maximum review so that's our total to Max we want to do a count on the number of reviews that have been done and then we want to find out what the best grade was by reviewing the total for the minimum now if we run this you'll see that we should have 31 records and we've got accounts going on of how many reviews there are the best grade that they've achieved and then the most recent date of them if we go back into the design view on that you should see if we unclick the totals that we'll have more records than we normally would see at the 31 we can see that there are multiples for the idea of 31 and six 16 and 10 so what we're doing with these totals elements is we're grouping them by the IDS we are then looking at the most recent review and we're looking at counting up the number of reviews that have been and the best overall grade so now that we've done that we're just going to put in some criteria here now this criteria is going to link to the combo box that we've created inside of our form we need to go and Target that so if we do a square bracket and then put in forms you'll notice that the query will allow us to Target forms and then if we put colon you'll notice that we can choose which form we want to select so that's going to be form add review if we do it exclamation mark on the end that we can then choose what control we want to select which is combo Garder now at the moment because we haven't got a record selected on our combo box we're going to have no results here so we're just going to save this and we are going to use this later on inside of our form so click on Save we're going to use the same prefix as we have done before which is QR Y and then we're going to put form and then click okay now I'm going to close this query and then I'm going to go to the review notice I still can't choose any value on the gardener we need to make it so that this person can be selected so we go back into design view we're going to edit this control here in the property sheet if we go go to data and then go to go to row source and click on the three buttons at the end it takes us into a query Builder we want to choose the gardener table so select it on the add tables element once it's selected what we want to do is we want to add the gardener ID and the surname so just double click on them and these should be added to our query criteria at the bottom of the screen we're going to click on x and then click on yes so now we're back into the design view if we go to the form view you'll see that we should now have a dropdown with a load of IDs now these IDs are not very useful for us because we don't know who is who so what we need to do is go back into design view and change the order of the ID and The Gardener's name inside of the career design so go on to the row source and you'll notice that we are bound to column one so go to the control source we're going to drag the c garden ID to the second column and then we're going to close that and then if we run it you'll see that we should now see the ID or the name not the ID however if we press any of these it's going to add the name rather than the ID into any of the tables that we referring to so go back into design view and we need to change the column that it's bound to to number two because that's where the ID is now if we click on The View everything should be as it should be however at the moment when we select this none of the data is being pulled through so this is where we start to add in some of our additional formulas into the form itself click on the text box area for the most recent review and we're going to use what's known as a domain lookup or a dlookup in here we're going to put equals dlookup and then we're going to put in a bracket and then we're going to say what we're going to use in order to get the value from so we're going to go do most recent review and then we're going to put a speech mark and then a comma and then we're going to say where inside of a speech mark that information is coming from so it's coming from our query that we've created so QR y for close the speech mark close the bracket now this should be that when we run this now if I choose that drop down I will get a value because it's looking up the domain lookup for the value and pulling it through so I'm going to go back into the actual design view and I'm going to add the other conditions we need for the number of reviews so we're going to go equals dlookup and then we're going to do a bracket speech mark and then the next criteria so number of reviews again as we've done before in between speech marks followed by a comma and then we're going to put some speech marks and we're going to say where that's coming from so it's coming from the query form we're going to close the bracket and we're going to move on to the best grade and again we're going to go inside of here and we're going to say this is going to equal very similar to how you do in Excel spreadsh sheeet this text box is going to equal the dlookup and then when we're going to do the bracket speech mark and best grade close a speech mark put a comma in and then again we're going to Target that speech mark QR y form speech mark and then finally close the bracket we now go to the new review date and we can use this we can get the system time so if we put equals date and then put some parentheses so Open Bracket close bracket and that should just pull through the system date for us we obviously got inside of our CBO review or combo review the IDS because we put those in there already as a drop down now I'm going to add one last text box that I didn't add earlier on and this is going to be where we put in that value that if it is lower than a specific value then we put a message to the user so for example if it's lower than two then sh message and we use an if if statement here so I if bracket and then a square Brack bracket and then we're going to Target the combo box for review or CBO review and close that square bracket and if it's equal to three then so comma then and speech mark the value that we want to display and we're going to put in there something like a range monthly meeting once we've typed that I'm going to close the speech mark put another comma and then we've not got an else statement so we're going to put two speech marks so that's blank and then close the bracket we're going to get rid of the label and then we're just going to drag it around so it makes it look bit prettier I'm going to review this and see what happens so we can see we're pulling data through on all of the areas and oh I've noticed that my name element where it's saying about monthly ratings is not displaying this could be because of one thing and one thing alone that we've named this or not named this property as CBO revieww as you can see I'm going into the review of the property sheet and yes I need to make sure that that is CBO review once I've done that and I go back into design view as soon as I then choose the review to be three we should get a message pop up now notice when I do cycle through the different gardeners that the data doesn't actually change that's because it's pulling through the information as of that query at that time we need to add an additional function so that this automatically refreshes every time you choose a new Gardener back into design view we'll go and then click on Gardener and then we're going to right click and we're going to build the event and we're going to create a new macro so macro Builder and we're going to choose on here from the options for a requery now it's normally down the bottom and you will see here that it says requery that's the only thing we need to do and this should when we run it or when we change the actual Gardener it should automatically re rerun the query to make sure that the data is represented of those individuals inside of the form so let's just cycle for a few and you can see the dates changed the number of reviews and we can see that if we change our own grading that the message box at the bottom will display or not if need to be so I'm just going to drag around these to make sure that they're customized slightly and so that they are not having just CBO review we get rid of the CBO because we will lose marks if we don't address this once you've done all of this you need to make sure that there is an appropriate heading on top of the form and making sure that it is presented in a way that is nice e