Microsoft Access 2016 Tutorial for Beginners – How to Use Access Part 4

to be able to sort your records by a field for example customer name let's say I want all my customers sorted alphabetically those that begin with the letter A's up at the top followed by those that begin with the letter B's C's and so on you can do it one of many ways one way is to just go ahead and click anywhere within that column that you want to sort the records by by that field customer name and then come up here on the Home tab to the sort and filter group and there you go you can see DA's will be up at the top and it will go down all the way to the Z's that's known as ascending or you can flip that and have all those customer names that start with the letter Z up at the top and then go backwards all the way up to the letter S which would be at the very bottom so if I go ahead and do descending let's see if it checks out I got the lowest end of the alphabet that I go the customer names begins with the W then it goes backwards ours all the way up to the B's hey it worked so you can do it that way and then notice that you get this little arrow pointing down that's to let you know that if you're not figuring it out over here that it's sorting well the Earl is telling you it's being sorted but which way down means descending and it also lets you know that that feels being sorted so if you're looking at your records and let's click off someone you're like how can this is all jumbled the customer names they're not aged as oh look at that we got a little indicator to let me know that that field is being sorted so if I don't like that and I want to do it ascending ly a disease there's additional ways you can sort the fields here instead of coming up here and clicking on descending or descending you can click on that little arrow right there and there you go a to z and z to a do a to z and it flips you can see the arrows now pointing up so we're sending up to the upper end of the alphabet the B's that's as high as I go know A's no customer names that begin with letter A and then the other way is to go ahead and right-click anywhere within that column and hey look at that we can go the other way now if you want to revert back to what it was before you start doing all the sorting and then just come up here and say remove the sort how about numbers so if I come over here in the customer ID and I want to sort it ascending what does that mean well for A to Z that means the small number is gonna be up the top and then as you get down to the Z's you get larger if you want to flip that and go from the largest number at the top then choose descending so right now it's sorted it looks like 30 1000s to the 32s ascending so if I do descending then the largest numbers are gonna be up at the top the 30 mm and go all the way down to the smaller numbers 30 1000s and then the smallest of the 30 1030 1365 and then of course you get your sort arrow let's remove that now you can do more than one sort called consecutive sorts so for example if I want to sort the customer name field and the state field keep in mind that you have to do it one of the time but whatever is the most recent sort is going to take precedence so that brings up a good point if you had three or four or five consecutive sorts one field after another the last sort that you do takes precedence over the second-to-last sort which takes precedence the second faster over the third-to-last then forth the last in any case let's keep it simple and do two and so if I do customer name and I click on the drop-down arrow and I say ok sorta A to Z there we go and then I come over to the state on a saying ok let's go ahead and sort that e to Z it looks at the most recent sort and says ok that takes precedence and then after it takes precedence it looks within this sort here and it says are there any duplicates because if there are I'll revert back to the sort before and you can see that it's still sorted it's keeping us sorts as much as it can after we sort by the most recent and I say as much as it can because if there are no duplicates in here then there's nothing to sort over here for example do I have any duplicate states here of course I've got Utah so if I come over here well it's hard to look at that let me go ahead and click on the row headers and select all those states you Tom so we can look at the highlights and ok of those four states are duplicated in that sort it reverts to the sort before and it should sort it ascending Lee let's check it out have h LM and are hey it works it's ascending so let's go ahead and remove that sort how about if we do it the other way how about if we flip this and see if it works the other way Zita a course it will so we're doing this descending but now let's go ahead and keep the state the same ascending a-to-z so we've got our utah's right so instead of sorting after it finds duplicates over here from the A's down to the Z's because we flipped it to descending first after found the duplicates that aren't resorts to that sort which is descending the lower end ours all the way up to the F's and then of course if we add a third sort that we did first before this one and then that once the last one if it found any duplicate names and would go to that first sort the third sort and sort whatever it could if we just had two duplicates in that first sort the first of three and then of course we can go ahead and remove the sorts now remember to find out how many records are in a table look down at the record navigation bar and you can see we've got a total of 47 now if I don't want to see all the records just say those employees who work in the state of Utah I could sort it and it would group them all together as sending Li where the A's are up at the top and they use down at the bottom you can imagine if you had thousands of records had more than just two states to work with then it's just easier to filter or what's called filtering or you can set the criteria to view only those records like those employees who work in the state of Utah and you can do it one of many ways one way is just to come up here on the Home tab to sort and filter group and click on the filter which looks like a funnel and I don't know where it's got the filter somewhere maybe at that point or you're poor stuff into it an accessorize this out and just bring this in and right now I just want to see those employees who work in the state of Utah so when I click on it it's pulling up the employee ID and why because that's where I'm at so I click cancel and I want to focus on the state field and then I can come up here and click on filter and filter just well if you had a lot and you just want to choose one instead of unchecking all of them you could deselect all and then just check the one or if you only want a few then you just uncheck a few and then when you're ready go ahead and click okey-dokey and there we go just Utah now a couple of things happen the least of which is that filtered out Arizona and pulled in Utah and then you can see up here next to the state got that little funnel lets me know that that field is being filtered other indicators are down below on the status bar it says this is being filtered and also you can see it's being filtered here and then you've got the toggle filter button that means that if you want to go back to the way it was before you filtered it going late a second I want to see what it looked like before click on it and it takes us back to the way it was and you can click on it again and hey it's toggled now we can go back to our original filter now let me go ahead and save it what would happen if I closed out of it and open up the employee's table again double click and it's not filtered but it remembers the toggle so I can come up here and click on toggle and it goes back to to Utah oh isn't that fancy you can also toggle it down below by clicking on filtered it's unfiltered now but if I click on unfiltered it goes to filtered and if you don't want that filter to be toggled in anymore and then to get rid of the toggle you can just come back up here to that field that's got that little funnel click on the drop-down arrow and then say you want to clear the filter from the state and there you go no more toggle egde now you don't have to come up to the top on the ribbon to be able to filter for example if I want to be able to filter by Arizona how about just a right-click and when I right clicked on that field a Z it pulls up where I can have it just equal AZ left click on it and it only pulls in AZ always in that nice let's go ahead and click on the drop down arrow and clear that and then right click on a Z again or maybe Utah doesn't matter one of the fields now it equals Utah so if you just want to see you Tom there you go and does not equal Utah contains only UT or it doesn't contain UT in the field you can have text filters and then you can say well it ends with and then go ahead and type in the letter T for Utah any case let me click cancel how about numbers how does it work with numbers let's see which one of my fields are numbers zip code could be a text field so let's come up here and right click on the tab go to the design view okay employee ID is a number field Department number is a number field and the weekly hours is a number field ok let's go to the weekly hours right click and go back to the datasheet view okay we got to save it first alright yes and then go over to the weekly hours and see what it looks like when you right click on 40 so you can filter by selection again saying ok since I right clicked on 40 I can filter by 40 where equals 40 or it doesn't equal 40 less than or equal to 40 or greater than or equal to 40 or do your number filters or it can be less than select less than and then type in a number of course you get out of the scope of 40 if you say less than 35 and hit enter well less than or equal to 35 cuz just pulling up 35 and let's go ahead and click on the drop-down arrow and clear the filter for weekly hours and see that we want to filter more than one field okay let's go ahead and filter by state like if I go ahead and click like in the field Utah of course I can right click but you also have the option to filter by selection up here on the Home tab in the sort and filter group selection click on the drop down arrow and of course it's like right-clicking on the field so you can right click I I prefer doing it that way equals Utah doesn't equal contains or doesn't contain Utah in the name of that field unless equals Utah let's say that I want to come over here into the first name and pull up all those whose names because I talked to an employee today and it was Matt it was with an M Matt Mary or I can't remember there are so many M's to look through him to scroll but I don't begin with the letter M so what we can do is we can go ahead and right click over here anywhere and so you want to do a text filter and say that it begins with hey there we go select that type in M hit enter cool we narrowed it down oh is Monte now we were able to narrow that down and so now you can see we've got two filters funnel there for first name and a funnel for state and of course you can toggle back to the way it was before it was filtered toggled he remembers both filters now if you have two or more filters and you want to clear them of course it's for me very inefficient to click on once a clear this one click on the other one clear this one that's rather annoying instead let's go ahead and click off and then just come up here to the sort and filter group click on advanced and say you want to clear all filters oh that's nice okay last thing let's see is set up a filter and you really like it you don't want to reapply the filters again but you have other filters that you like certain situations you want to pull up as well that you don't want to create those filters over again either what you can do is you can create your first filter and save it as a query create your second filter and that's right you can save as many different versions of your filters as queries that way you don't have to recreate the wheel as it were when you set up your filters so if you got a complex filter that includes a lot of different options and different fields let's go ahead and as far as this example goes let's keep it simple you can create your own complex filter let's say I want to go ahead and filter for the last name and first name and maybe for the state well let's just go ahead and do it for the state here of Arizona right click and say it equals Arizona and then right click over here in first name and say the text filters begins with I will do em again for Arizona this time hit enter and that's easy to recreate but if yours is more complex you don't want to keep recreating it over and over again once you have it set up the way you like it go ahead and save it as a query how do you do that well with your table here selected you want to go backstage file go down to save as and then go to save it as an object click on save as and then come over here and click on save as so we can actually get the window to save it as a different object not as a table we already got a table you want to save it as a query so it can take a snapshot of our filter and put it into the query here and instead of copy of this time a table it's going to be a query qry three that are prefix as we talked about in earlier training video the purpose for that and we'll say in Arizona begins with mm-hmm click okey-dokey and hey there we go it pulled up the query in the datasheet view just like a tables got a datasheet view of the front view and so it's got only those in Arizona that begin with the letter M all that's nice so I can go ahead and click on employees and it's right there as well cool so I've got this one let me go back to the query and close out and then if we want to go ahead and change this up by clearing all the filters again advanced to clear all filters and then we'll keep it simple here because I think you're getting the idea and you go ahead and you do your filters equals Utah I know this is very simple but you do more filters right and then you want to go ahead and save it again file save as save as object save as to get it to where we can change it to a query and a three-letter prefix to identify that it's a query and you can even type in filter for Utah UT and then click okey-dokey and hey there we go now we have to so I can keep creating those as many as I'd like and let me go ahead and close out of here so when I clear the filters here for my table or maybe this is the one that I use the most so I leave that one the toggle but I want to quickly find up all those employees and Arizona that begin with the letter mmm go ahead and double click on that and hey it pulls it right up now we haven't covered this yet but to give you a heads up with queries if you're kind of curious that of course when you right click on a table you go to the design view of the table we talked about that but when you right click on a query and go to the design view well here's a heads up there's the table down below or all the fields from this table that are going to be displayed in the datasheet view and down below you have the criteria that says has to be like or begin with M for only those employees that work in the state of a Z isn't that cool like that you're excited to learn about queries let's go ahead and close out of here and now you've got as many different filters that are called queries as you want to set up or save from your table your filter as a query before we talk about sub data sheets let's quickly go over or do a review of what data sheets are as you recall in an earlier training video there are only two objects in Access that have data sheets and they are your tables and your queries now by default when you open up either one it goes to the datasheet view you have a sheet that contains your data that you can edit or add additional records to and you can see there's the customers table that I have open and how do you know it's a table well by that little icon and how do you know you're in the datasheet view by what you see up here on the Home tab and the views group has its opposite meaning that if you want to go to another view like the design view that has a pencil and ruler when you click on that flips it over now it flips back to the datasheet view with the datasheet icon so that means you're not in it it means if you want to view it then you got to click on it so you can see there's the design click on view and we go back to the datasheet view and the same with our queries double-click default opens up in the datasheet view and then you get the design button click on it different design than your tables and then to go back to the datasheet view of the query click on it and we're back let's go ahead and close out of the query here now that you know what data sheets are you can actually have what are called sub data sheets and the definition is that they are data sheets nested within data sheets or like tables within tables but that are related and can be expanded to review related data now I don't know if you notice this but when I open up my customers table I get these little plus signs here that means that if I click on any one of these it's going to open up and find the related table actually not find there actually is a related table to the customer table but which one is it or how many related tables do I have to the customers table well let's find out let's click on this one and it opens up related table and this isn't the only table but we'll go over that in just a minute but it picks one of them and this one is the let's see order ID is the label for this column order date I'm thinking it's the orders table because in there when you double click you have the order ID the order date and so on let's go ahead and close out of that and so what's cool about this is that if a customer calls in like happy town play world and I'm already and their sub datasheet for the orders that they made and they want to make a new order well I can just come down here by default we've got the order date I can change that type in the item number and go ahead and type in the quantity and save it or if they call back and need to make changes you can easily come and do that here instead of seven for order ID 22 let's type in eight then I can click on the pencil and it saves it to eight so order ID 22 when I open up the orders table I scroll down to 22 is it seven no it's eight and vice versa if I make a change here and I change it back to 7 hold down the shift key and hit enter to save the record and I close out of here is it oh it went back to seven so what you're doing one affects the other vice versa because it's related and of course you can come down here and expand additional ones for like roof it and they got quite a few orders they're cool and instead of opening up each one at a time if you want to expand all of them at one time then come up here on the Home tab go to the records group click on more go down to sub datasheet and we don't get the option to expand all of them and the reason being is because our sub data sheets don't have sub data sheets you could but well not what we're doing here we're keeping it simple and so what I need to do is if you want to expand all of them I just have to go ahead and click out of one of these sub data sheets and to the main data sheet any record in the main data sheet and then come back up here and say more sub data sheet to expand all and there you go and then of course to collapse them all make sure you're within the main data sheet one of the records there and then back up here more - sub - sheet to go ahead and collapse now in order for this to work because by default access will automatically or should automatically pull this in a related table well the keyword is that it has to be related so the customer table is related to the orders table and if you recall in an earlier training video when you come up here and click on the database tools tab go to the relationships group and click on relationships hey there we go our customers table is related to the orders table and it's a one-to-many as opposed to the one-to-one relationship with their billing information so when it comes to contacting our customers and Bill them I just want to talk to one person and not many somebody who takes care of all their billing that we can go ahead and get them to pay up through that one person and not many so he got the one-to-one now if I want to go ahead and go back to my data sheet for the customers table and say no no no I don't want it to go to the orders table I want it to go to the billing info table that's what I want to see as a sub data sheet okay to do that let's go ahead and close out of here come back here and we already have a sub data sheet related well if you want to remove it then just go ahead and come up here click on more go down to sub data sheet and remove and also if you open up your table and you don't see the plus signs but you know that you have your tables related well just doing it I'm doing here and you'll be able to hook up to the right table but again make sure it's related so the only two tables that are related are the orders and the billing info so we're going to hook up to the billing info here instead of the default orders and to do that let's come back up here click on more go down to sub data sheet and say you want to insert a sub data sheet now you can have sub data sheets based upon tables queries and we just have one or both and it lists well both tables and queries but let's keep it simple and focus on the tables and then down below you have the fields and that you link to in order to be able to identify the primary key to the foreign key field so you can link the tables and have the well in the main datasheet the sub datasheet or the related table pull up as the sub datasheet and so here if I want to be able to have it related to the billing info then I need to select that because if I don't have it selected then I'm not going to get what I want down here it may be something else like orders so the master field is the table that I'm currently in that I want to link up the child as a sub datasheet into the main table and so when I click on the drop down arrow I got the customer ID right but that's for the orders table if I want to link it to the orders table and pull the orders table in as a sub datasheet don't want to do that want to do it for the billing info and then click on the master and you get a list of all the fields but I want to do it based upon the primary key in the foreign key field so I can have it relate and linked just right here you got the other fields you can link to if there is another field that's named differently and that's why I have my foreign key fields so that's the primary key field in my table here the main table the foreign key field again named the same name as the primary key because if not and I go to the billing info and I'm trying to find the customer ID foreign key field that has the same data type and type of data as the primary key field in the customers table unless you can remember that's gonna be hard but that's easy right customer ID - customer ID so it's that simple click okey-dokey and we're done so when I go ahead and click on one of these plus signs it pulls up the information as a sub data sheet from the billing info table to the main table here the customers table and what's interesting is that you get a plus sign to that and why because it's a one-to-one relationship it keeps going back and forth so for like Ghost Hunters America the customer name we bill Brett maroon over at Ghost Hunters America and we contact this person but when I click to expand that it says that this person is the contact person over at the customer name here at Ghost Hunters America and the contact person for Ghost Hunters is Brett and Brett is the contact person at Ghost Hunters America and it just goes on and on and on and so I'm glad I had that one-to-many relationship as the default unless I want to change it as I just showed you how you can do it here in any case if you want to go ahead and close out of this just keep in mind wherever the cursor is at and I say close meaning that maybe you want to collapse this I mean you can do it manually here by clicking on this little minus sign but wherever you're at here and you come up here to the more and you see sub datasheet and you want to collapse it just collapses everything from that point below so if you want to collapse everything well all these sub data sheets within the main datasheet then you got to get into the main datasheet just any one of these records it doesn't matter then come up here and click more go to subtitle sheet and see you want to collapse all and we're back to where we started now we're in the customers table let me go ahead and close out of here and we won't save the layout we'll just say no so when I double click and open up the customers table and I expand it it goes back to the default which was the orders table instead of me changing and saying no let's go ahead and link up as a sub data sheet to the billing info so keep that in mind that if you want to save the layout then in our case it was the most recent link that we connected with which was the billing info table now having said that lets go to the orders table double click now in the orders table I don't get a plus sign and why is that because in a relationship a one-to-many it doesn't go backwards for many two one and give you a plus sign to expand it it's not gonna list it just think of it in a one-to-many relationship that one comes first or one-way viewing too many but never many back to one but nevertheless you can go ahead and do it and it will insert it it's just not done by default in a one-to-many relationship where the many relationship in this case the order table will list it so if you want to force it because by default it's not showing it a sub datasheet back to the one relationship in this one-to-many relationship that is from the orders to the customers then go ahead and click anywhere within here of course and come up here to click on more go down to sub datasheet to sub datasheet and we want to go to the customers and now the name of the primary key field here the master field is always going to be the current table that you're in or a query and so the primary key field customer ID perfect and then the link child field or the customer table the sub datasheet that we want to insert where the datasheet as a sub datasheet into the main datasheet is also a customer ID I mean access is gonna find it default for you unless of course you want to change it but I'm not gonna do that mess with that so I don't have to click on the drop-down arrows and find it unless of course you have it listed under different name in which case it should still find the same data type or type of data and be able to pull it up but it just makes it easier for me to go how customer ID to customer ID the primary key to the foreign key field in the customers table click okey-dokey and let's go ahead and expand it to find out who made the order order ID 5 and it was Ghost Hunters America and I can expand that and find out that Ghost Hunters America had not only this order that they made order ID number five on this date but while there it is number five but one before that order number four and all the rest of the orders and then of course you can expand that and it just goes in a never-ending loop so let's go ahead and click out of that and come up here and do something more which is going to be sub datasheet to remove to get us back to something less where we have no sub datasheet in the orders a query is based upon one or more tables of data and/or other queries and it can be used to filter in only those fields that you want to see and you can organize those fields into any order like what field comes first second third and so on as well as performing calculations on those fields well those fields have to contain numbers of course and so if we want to create a query based upon like let's say this table let's double click to take a look at the table real quick and let's say that I just want to see in my query not all of these fields just the asset tag and the date received and the reason why I want to use a query because it's more efficient just to pull up two fields at least in two ways one way is because when you open up the table is pulling up additional fields it'll slow down your network or your computer especially if you have like oh gosh a lot of fields here that it's gonna pull in all that data for but also maybe slow you down because if I want to focus on the asset tag and the date received I got this guy that's in the way so if I got to go from I'll pick a number here and go on 0:08 this columns not right next to it is it this one's in the way now you don't have to create a query if you want to well do some organization within the table here like as far as maybe putting the date receive field next to the asset tag so you can go ahead and go from one field to the next and not have to jump field and you can do that one of a couple of ways you can either right-click on the field the column header the label for that calm the field name here manufacture because when you right-click on it it selects the entire column and it allows you to hide that field cool let's go ahead and click and drag and select the others and right-click on the selection there to hide those as well now that's fine but only for that session and what I mean by that is that well if I go ahead and close out and it says you want to save this layout in other words keep the fields that are visible visible and the other ones hidden hidden you could say yes so when you come back and double-click well there you go but what if you want to change this now and go okay that was fine for that session but now I want to see a few more fields or all the fields and then from time to time come back to viewing just these two well you can go ahead and do all that work or the other option is to create a query based on this table and take a snapshot of it or set it up that you want to be able to see just those two fields and call it query one fields one and two or however you want to name it and then for maybe a third field go ahead and save a second query based upon this table that shows just those three fields so that way I can just go ahead and double click query one to see those two double click query two to see three fields and not have to tweak it just to go back to the way I want to see it or to see other fields so it's just for that session as it were now to go ahead and unhide the fields just right-click on any column header here and go down to unhide you can see all those that are not checked are the ones that are hidden so we want to check those to bring them all back close out and then the other way instead of hiding the fields is you can go ahead and click and drag and move the columns around by clicking on the column header holding down the left mouse button and dragging over and there you go so that way you can just look from this column to that one and not have to jump over one but again it's not very efficient if you want to be able to just view these two fields here and pull up only those two as opposed to the rest well it's more efficient as far as you're not having your eyes wander or trying to connect the fields when they're just right next to each other now if that's not a selling point for you to want to use a query well you can base a query upon two or more tables in other words I've got my manufacturer number here but I don't know the name of the manufacturer so if we're having problems with these computers that seem to be number three well what's number three I'd have to open up the manufacturers table right to find out the name that the ID is tied to so I'll double-click number three is macron so that can be very annoying instead I can create a query based upon these two tables to just pull in the name if I don't want the manufacturer ID and I just want to see the name so I'd get a spreadsheet like this but in a query of course that just pulls in on these like let's say the asset tag the date received in the purchase price and then have over here the manufacturer name so I'm not pulling up any extra fields from this table or any X from the manufacturers now you could say wait a second what about the sub datasheets can I just go ahead and insert the manufacturers table into this one as a sub datasheet so in that sub datasheet training video that we went over you get a little plus sign you can click on select for this record right here I could click on it to expand it and for number three it would show down below macron well yes but remember the sub datasheet pulls in everything and so if I had a lot of fields in the manufacturers it's not as efficient especially if I don't need to see additional fields but maybe just the manufacturer ID and name well that's all it's got but I'm saying if it had more if you don't want to pull in those additional fields and be more efficient then again we're back to the query so let's go ahead and create a query based upon these two tables the computers and the manufacturers and like I said let's go ahead and pull in the asset tag the date received the purchase price and the manufacturers name now we want to make sure that these tables are related because when I create the query based upon these two tables if there's nothing that links them up and that relates them then there's nothing preventing like this record here that says three while they are related because we got the foreign key field here saying that okay which record is this ID go to but you can see right here that's the foreign key field the manufacturer let me hover over to the right of that column header until I can get arrows pointing in opposite directions and double click really fast manufacturer ID so there's the foreign key manufacturer ID which is the primary key the manufacturer ID well you don't see it here and they double click really fast in the manufacturers table let's just go ahead come up here to the database tools tab relationships group click on relationships and there we go we've got the computers table the manufacturer ID and you can see it's related to the primary key field over the manufacturers tables so we're good to go because if we try to create a query based upon two tables that aren't related well you're gonna get a field that you pull in from this table if this wasn't related that's not synced up with anything over here but because they are related then whatever manufacturer number is on record let's see number one it's going to have the correct name here that we're gonna pull over from the manufacturer field as opposed to just not being related and being jumbled so great now you can either have them related directly when you create your query or indirectly so I could have the department's table create a query based upon that and also include the manufacturers because even though they're not directly related well indirectly through all these tables it will see it and bring it up so maybe for this department HR we only use computers or give HR the computers manufactured by macron and so that would be good to know and be able to pull up really quick now when we create our query this brings up a good point if I just want to see the manufacturer name the asset tag the date received in the purchase price I don't have to include the ID because that's all done behind the scenes once we have it related as you see here it'll keep the records organized and know that for record one if it was ID number three macron even though I don't pull in the ID to see my query it will know that macron is for record number one because it's already taking care of it and keeping it organized behind the scenes so that's really nice let's go ahead and close out of here outside of this and we'll save the layout that save that as well and to create our query come up here click on the create tab let's go down to the queries group and you got two ways of creating a query you can do it by wizard or by design design is from scratch by wizard Microsoft has what are called wizards in it that it's going to ask you a bunch of questions based upon your answers it's going to create in this case a query for us so let's go ahead and click on it and there we go and so the first question the query is what kind of query do you want to create you can see down below that this wizard creates a select query well a simple query is called a select query and there are other action queries well we've got a few here like the crosstab finding duplicates are unmatched and there's additional queries but I want to keep it simple so the wizard is going to create a select query from the fields that we pick and those fields can be based upon two or more tables and or other queries and you can see over here I just had the four tables it won't be any other queries because I don't have any other queries let's go ahead and click okay the second question is is which fields do you want in your query so in the image here you can see you got a bunch of their different data sheets representing tables or queries that you want to pull some fields from and put it all into one data sheet well in the datasheet of this query that we're creating and then down below you can choose from tables or queries we just have tables when you click on the drop down arrow you can see the four here comes from four over here so we got our computers and down below all the fields that are available from the computers table and over here what fields you want to add to our query we want to select and you can do that one of a couple of ways you can either select one here and click the arrow to add it over to the right or if you want to move it back click on the arrow to move it back or if you want to add all of them click on the double arrows add some alamos put them all back or you can go ahead and just double click like double click on the asset tag I don't want the manufacturer ID so we can skip that and even though we don't add it it'll still keep track of it the records together like what manufacturer ID and name goes to record one two and three so this is just for us what we want to see let's do the date receive double click and the purchase price now I want the manufacturer name but it's not in this table so we have to go to the other table manufacturers to be able to add the manufacturer name double click and there we go so I have a total of four fields from two different tables the first three from computers the last one from the manufacturer so let's go ahead and click Next and it says would you like to add a detail or summary query now why is it asking me that it's because of the purchase price field it's a number field so I can either show the detail of all those fields or I can get a summary of the purchase price field and you'll notice over here that you get different images so if I do the detail it says if I'm pulling in numbers from one table or query and numbers from another then it keeps them separate the details but if we want to go ahead and add them together or sum up or get the average then what it does is it takes them you can see the numbers here let's see five and seven and it puts them over here what's five plus seven twelve and then those two fields adds them up eight plus one is nine and so you can get the summary options well it's right there summary options the only field that has numbers that I've added to my query is the purchase price field that allows me to go ahead and get a total or get the average or the lowest number in that purchase price field or the highest number and down below I can count the records and the computers get a total count there but I'm gonna go ahead and click cancel we'll do that later on I just want to keep it simple we'll just stick with show every field of the record not just get the totals or the average for those fields or whatever other option that we just went over in the summary options and click Next as finally what title do you want for your query gives us the default and I don't like this one because it gives me the name of the table but at the end it says it's a query and remember when I'm working behind the scenes as we'll go over in later training videos in design view of other objects this can fool me because I'm looking at this going home it's a table and I'd select that thinking it's a table if I don't come over here and go oh it's a query which is it so what I'm gonna do to keep it simple I'm just gonna get rid of thee and Suffolk saying it's a query and type in the three letter prefix qry saying that okay great now I know looking at the beginning of it it's a query but what's the name of it computers as I look over here that's a table now what's the name of the table computers so when I'm in the design view and I see the names of two computers here I can just look at the prefix and go oh that's the query and that's the table and then down below that's all that they need so do you want to open up the query to modify it you know change the design make a few tweaks or you just want to go ahead and view the details the data they're in let's go ahead and just view the information click on finish in the datasheet view of our new query hey you can see up at the top the name of it qry computers and it's been added over here in the navigation pane under the name here queries cool and again it's easy to detect even if we didn't have the three order prefix here because it will put it in the queries here as opposed to the design view where it's not going to be this organized and say hey this is a query and that's a table all you get is the name so that's the only thing that will differentiate between the two names here being the same computer's is the prefix table for table and query for query and so pulled in everything I wanted the asset tag did receive purchase price and manufacturer oh is it the ID well we know it's not the ID but again if you can't see the entire label for the column the column header just hover over to the right hand side until you can see arrows pointing in opposite directions then either click and drag or to do a best-fit double click and it fits it to the longest text within that column click to you best fits on all of them because that's the best thing rate and let's go to the design view now if you come up here on the create tab you see over here we don't have the option to change it's only on the Home tab that we can change views so there's the design view or if you don't want to change tabs and keep it on the create tab that's fine just right-click on the tab to go to the design view in there you go it's divided into two sections the top section is where it contains the tables that this query is based upon and then down below in the grid the fields that are viewable in this query selected of course from the tables up above now if you can't see all the fields you can go ahead and scroll but I don't like scrolling so I'll hover over the bottom border until I can see arrows pointing up and down click and drag and there we go and you can see the relationship that we saw in the relationships window that it's here as well the relationship carries over they don't divorce or break apart and so you can see right here that the manufacturer ID the primary key goes to the foreign key field here and then down below you've got the name of the field in the top row and then below that the table that the field has been taken from so the asset tag is from the computers the date receive computers computers that one's the manufacturers and if you want to be able to add the manufacturer ID because maybe you're thinking well I like to see the number kind of memorize these things well you can either come down here and click in the field and click on the drop down arrow and it lists all the fields that are available up above including the asterisks and the asterisks means that when you select that it'll include all the fields here you just don't have to add like you see down here each field separately you just put the asterisks down here and it will automatically add all the fields for you and we'll go over that in the later training video but if I want to add the manufacturers ID you can click on the drop down arrow and say ok we want to go to the manufacturers table now this one says man you facture manufacture I can't see the end whether it's just manufacturer or it's the ID so that's not helpful for me right here so I'll click off the other way is to go ahead and either click and drag it down and add it or you can come up here and double click of course if I do that again well we've got to manufacture IDs that's not gonna work so if we make it a boo-boo then go ahead and you see that thin gray bar up at the top when you hover over it you get this black arrow pointing down when I'm down below it don't get the black arrow so go back up above when you see the black arrow click and it selects the entire column then hit the Delete key on the keyboard and it gets rid of it click delete now doesn't matter which table the manufacturer ID comes from no because it's the same thing so I can do it from the manufacturers table then when I'm done I can come up here on the related contextual design tab because I'm in the design view and to get to the front view or the datasheet view to view the data that are in these fields here come over here to where it says results and I can click on the view button or I can click on the fun run button the difference between the two is that the run of course will take us to the datasheet view that'll actually perform the actions specified in the query so more specifically you see here what it says select query that is this query type you can select other query types like make a table based upon this query so you can take the records and create a new table based upon this query and when you click on run takes us to the datasheet view but it also takes what you have in here and through a couple of steps it will create a new table or you can take the records that's found in this query and add it to another table or you can go ahead and do an update like maybe multiply the purchase price by a percentage you can do a crosstab or do a delete saying okay I want to go ahead and delete certain records within this query so down below you've got a criteria field so you can say okay in the asset tag I just want those asset tags that are less than a certain number and then the rest I don't want to see they're obsolete and let's get rid of them and so when you select delete query and you click run or any of these other queries in quick run besides the Select it'll take you to the datasheet view and it will delete the records update them add them or make a table we'll cover that in a later training video but because this is a select query it does nothing but just takes us right to the datasheet view so that's the fun run button datasheet view let's go back to the design or you can just click on the datasheet view button same view and well that one's kind of huge isn't it if we double click and that's the best fit but that's going towards the largest text and the call Nets the name of the column here manufacturer ID and all my goodness look at that nice double click really fast and there you go let's click on this one drag it over here so they have manufacturer ID and since we know what's the ID we can go ahead and collapse that so we can just go pull let's do man and then click off and then when we're done if we want to go ahead and close out I'll say do you want to save the changes yes double click we're back to our fancy query as you recall in the previous training video we created a query by using the query wizard here I want to show you how you can create a query from scratch or by design or you don't have to go through all the steps in the query wizard to get what you need so let's come up here click on the create tab go to the queries group and there it is query by design sounds like some fashion company that's doing your query it's by design let's go ahead and click on it creates the query but before we can get into it it says wait a second remember your queries has to be based upon either one or more tables and or queries so it has a list here of all the tables that we can add to our query do I have any queries of course not you can see none over here and then both that just shows the tables so to be able to add a query to the query here or a table to the query you can do it one of many ways you can either go ahead and double-click to add it there you go or with it selected you can go ahead and click Add of course now I have two here and I just need one not a duplicate let me click on add again so I can show you that every time you add more than the original you have the original name then the original name plus one as it were and then plus two or the second copy of the original and so to get rid of these duplicates here let me close out you can do it one of a couple of ways you can either right-click on the title bar and click on remove or you can click on the title bar and hit the Delete key and there you go we're back in fact let's go ahead and remove that as well and then to bring up that table window again so we can add a table or a query or a combination of them to our query you can either come up here on the design tab to the query setup group and click on show table same window or let me close out right click anywhere in this gray area up in the top section here and go down to show table and so let's just go ahead and well the other way you can go ahead and add your tables or queries or both is if you want to add more than just one at the same time you know do a block selection use the shift key so if we want to select everything from this pointer let's do that point down to the bottom hold down the shift key and then click the bottom and it selects everything in-between and then you can click add or if it's nonlinear then just go ahead and select one hold down the ctrl key and click any others and then click Add and there you go I'm just gonna look at employees double click add that and close out and so your queries divided into two sections you have the top part that has a list of while all the tables either from a table or a query and from those you want to be able to pick those fields you want to add down below into the grid or into this bottom section the grid here those fields you want to show up in the datasheet view when you view your query so let's go ahead and well I can't see all the fields here without scrolling and I don't like scrolling so let me hover over the bottom border of this table until I can see arrows pointing up and down click and drag and I don't get enough room so if that annoys you and you want more room and you got the split bar here that when you hover over it you can see arrows pointing up and down go ahead and click and drag either up or down in this case down to give us more room and then click and drag the bottom border and that's nice now I feel better and then to go ahead and add the fields here from your table to the grid down below you can see each row has its own name so here's a list of well this rows for all the fields for the name of the fields that have been added this will show what table they're coming from if we have more than one table and then these other fields we'll cover in a later training video how you can sort that field alphabetically ascending lead ascending lee condescendingly not kidding on that last one and then whether or not we want to show that and the criteria go ahead and let's double click which is one way to add a field down below to the grid the employees last name so you can see the name of the field and from what table it's pulling from and the criteria you can say well all those employees whose last name begins with the letter M like I said we'll cover that in a later training video so to keep it simple to add those fields down below to the grid you can double click to add them or you can go ahead and click while let's do the next adjacent one you get the arrow for the field here remember keep it in that row for the field that you want to choose click on the arrow and hey you can go ahead and let's do first name now you can do that or you can come back up here to the table and instead of double-clicking you can click and drag so if I choose weekly hours let me hold down the control key and do also an hourly rate so when I click and drag those selections and go to the very next available field the third one and let go it adds in both and so from like wait a second I want hourly rate before weekly hours what you can do is you can hover over that thin grey bar up at the top above the field here or the field row until you see a black arrow pointing down because if you move down off of it you don't see it so move so you can see the black arrow click on it it selects the entire column and then you want to make sure that you get the pointer there because if you move down you get the i-beam it thinks that you want to edit the name of the field here or change it we don't want to do that so we want to go back up to the top where that grey bar used to be that's now highlighted in black until you can see the pointer and then you can click and drag that guy over and let go and hey that's nice we reordered it so not hourly rate comes before weekly hours and if you made a mess or a boo-boo here and you're like I don't want this well just click on that thin gray bar again above the field that you don't want so you can select it hit the Delete key and it's gone or you can let's click on this one select it and do multiple selections by clicking and dragging to the right or left hitting the Delete key and now they're all gone and let's go ahead and add those fields back again let's do lastname hold down the ctrl key select first name link the hours our view rate and click and drag to the first field and there you go lastname firstname weekly hours hourly rate will leave Mazzio's I won't move them around and then to be able to see this in the datasheet view the data within those fields you can come up here on the design tab to the results group click on the View button or the fun-run now remember I talked about this in the previous training video some of this what I've already talked about I've covered but let's go over it one more time the Run button performs a specific action or inaction query because we have a simple select query there's no action except just to view the data but if we want to make a table add data to another table update a field like maybe increase the cost of a field multiply it by percentage or others crosstab or delete and other additional action queries well when you click run it does two things it actually performs the action here if we want to update or append information to another table and it'll take us to the datasheet view so if I click run datasheet view because it's a select query well there's no action except just to view the data here go back to the design or click the view button which brings up a good point if you have an action before like this one's them probably the most dangerous one or among the top dangerous that if you set your query saying I want to see those records that contains specific information and you want to delete those you may want to check first before you delete them that those are the records you want to delete so if you select delete before you click run and delete them you can click on the view button without running it and checking it first and going oh yeah those the records I want to get rid of go back to the design then click run and then I'll go ahead and delete all the records and then take you to the datasheet view to show that hey I deleted it in any case in this case it's a simple query a select query so we click on the View button we're back here lastname firstname weekly hours hourly rate and you can also move these fields around you can click on the column header select it the entire column and then click on the column header again and move it well in front of weekly hours so now our rate comes before weekly hours when I go back to the design view is our tea rate before weekly hours no it's just in the datasheet view not in the design so for you on the back end you may have things set up the way you want to see it as the designer or maybe somebody on the front end doesn't like it and so you're like fine I'll set that up for you so it doesn't mess with my back-end view of the way I want to see things and we probably ought to save our query before we get too far and so let's come up here click on the Save button and the three letter prefix qry for query and we'll just do a e for employees and then hit enter and there you go it adds it over here and now it's saved so when I close out I can come over here double-click to open it up opens up by default the datasheet view let me close out and if it doesn't open up in the datasheet view because maybe you got an action query or something else going on or maybe you don't have it set up correctly which we'll cover later on you can right click on it and open it up in design make your changes in fixes before you actually view it a couple more things I want to cover and let's go back to the datasheet view now any changes that you make here will update the table which makes sense because it's based upon a table so we're looking at max klinger here and he's got the weekly hours of 40 if I come in here and this makes it nice and I change it to 30 it'll update the table because how annoying this would be if I go to all this work and go hey while I'm here let me go ahead and make a change because if it didn't allow me and make the change and I have to go oh it's just right here I'd have to go to the table pull up the employees table make the change so you can see I made the change from 40 to 30 there's the pencil you can click on it and it will save it or hold down the shift key and hit enter so what you're doing this one because it's based upon the table here employees when I double click let's look at max oh boy we gotta scroll over to find his there we go 30 will update the other and vice versa so if I come in here and I go now let's go back to 40 hold down the shift key hit enter then I close out hey it's back to 40 and then finally let's go back to the design view you got these fields here this little asterisk right up above I know you were asking me about it because now is the time to cover it let's go ahead and get rid of these fields down below click and drag and hit delete and what that does is when you add the asterisks down below it adds all these fields to your query but it puts them in that one field because while in previous versions of access you only get so many spots for to add fields to your query so if you have a lot of tables from tables here another queries that you're adding you're adding a lot of fields and you're running out of room well I'll just go ahead and let's double click it adds the whole table all the fields from that table you want to see alrighty click on the fun run button there you go it's all there let's go ahead and go back to the design view so that way you can go ahead and save some space and not have to you know double click on every field or click and drag those fields down below the only problem with this is that when I want to do a custom calculated field like I want to create a field here that takes one of these fields like the our tier 8 and multiply it by 10% because everybody gets a 10% pay increase I can't do it because it wants to look at that field separately from while the asterisks that we added that put all the fields together so we'll learn about the simulator training video where you can create your own custom field here and say here let's write a formula or function that will take this field and multiply it by 10% and it won't do it unless you add that field down below into the grid even though you've added technically by the asterisks down below all the fields it has to be its own separate field so we'd double click here then come over here and we would write the function or formula that would actually see that field as a separate field because if you try to write that function or formula without that field there even though it's there well it's not going to work and you'll see that as you recall in an earlier training video we learned how to apply filters to our records within the tables where we can pull on some and hide others now those same filter features are also available in queries so if I open up a table you can see up here on the Home tab in a sort and filter group we got our filters I go ahead and close out of that so when I open up a query the inventory query double click Home tab same thing the sort and filter grew so you can go ahead and use the general filter the advanced filter filter by selection or of course you can go ahead and right-click on the cell that you want to filter by like the number 100 so it equals 100 and we have a total of three records and then you got the toggle and filter feature to go from what's being filtered back to when it wasn't filtered to going back to being filtered again and then let's go ahead and click the advanced to clear all filters so we can't do any toggling Jenny more and so what I want to do when it comes to filtering records is say that well these are pretty simplistic filters but when it comes to queries there's a more advanced way of filtering your records and that's using the criteria field to find that field we got to go to the design view of our query and you can do that by right clicking on the tab going to the design view what a mess I'm so embarrassed well let me go ahead and clean this up click and drag the split bar down there we go ha I feel better now I can see on the bottom part of my computer's table and so speaking of which you can see that my query is based upon these four tables here and then down below on the grid from those tables are the fields that I want to see in the datasheet view of my query and that includes these two from the department's table those two from the employees one from computers a couple from manufacturers and then more from the computers so you don't have to group your fields together by table which makes it nice and our queries here and I didn't as you can see let me go ahead and click and drag the scroll bar so and go all the way back to the beginning to my first field here the department code and you can see down below we've got the criteria row so if I want to set the conditions or the criteria and that a field has to meet before it can pull in those records like for the department code then I have to click in that criteria cell for the department code as opposed to like maybe for the department name but let's keep it simple because we just covered it by filtering by selection right-clicking and saying it has to equal 100 so instead we can just type it in here type in 100 after you type in your criteria you want to exit the cell by hitting the tab key so access when you do that can go back and check the cell the contents there in make sure everything's goes because if it doesn't like it it'll give you an error and say what are you doing so so far we're good now if I come back in here and I type in something that it may not catch like some text because well the department code is a numeric field when I hit the tab key you see how it didn't give me any errors but when I come up here to view the results of my criteria here click on it it says hey the data type is a mismatch you got numbers and you're trying to filter for a text ain't happening so we'll click okey-dokey and let's clear this out type in a hundred hit the tab key then come back up here let's view it hey there we go we got our three records let's go ahead and right click go back to the design view and let's do something a little bit more fancy how about if we do multiple criterias so we'll leave this one here that it has to meet that criteria 100 and in addition it has to meet let me scroll over here Oh like the purchase price let's say that it has to be less than $1500 because any computer that we bought that was less than $1500 is probably a cheap computer and so it's probably time to upgrade ok that's my way of thinking in any case to have it less than 1500 you want to use the less than symbol so hold down the shift key hit the comma on the keyboard there's less than and then type in 1500 after it and then you want to hit the tab key because upon exit it'll check your work and make sure everything's ok and it looks like it is now if you're like ok it's got to be less than 1500 so it can't be 1500 right what if you want it to be less than or equal to so to include 1500 to do that you want to type in your after the less-than or greater-than symbol because if you type it in before hit equals hit the tab key and it's gonna have a problem okay so we got to delete the equals before and I begin after the less than symbol and then hit the tab key and it loves me let's go ahead and view the results of this by coming up here clicking on the View button hey we have one so it has to be a hundred for the first criteria that we set and the scroll over and see if it meets the ooh that was close it's not fifteen hundred it's less than fifteen hundred and if it was fifteen hundred that would be okay because we said that it can be equal to fifteen hundred or of course less than so that checks out let's go ahead and right click go back to the design view and let's do another one let me go ahead and clear the criteria delete it and let's do it from the manufacturer let me make sure I'm in that criteria row gets a little bit challenging if you're way over here and you're like criteria is it on this row well what you can do is that you can go ahead and scroll over so the field that you're looking at has a label right next to it okay that's a little bit easier isn't it and for the criteria for the manufacturer let's say it's got to be the name global type it in hit the tab key now if it's text that you're typing in it'll automatically add the quotes around it which by the way you want to put quotes around it but what's the point telling you when it does it for you so it'll add it around it in case if you're wondering that way it's focused on everything in between the quotes the text there global now it's not case sensitive because if I open up my manufacturers table double click I got an uppercase G not a lowercase so that's good to know I don't have to use an extra finger to hold down the shift key to make it uppercase just type it in so long as the spelling is correct then you've got it so it's gonna meet two conditions now Department code 100 and the manufacturer has to be global come up here click on the View button and we've got one it meets the hundred and its global oh that's fancy let's go back to the design view here and let's try something else let's say that the Department code well it still has to equal a hundred but let's say it doesn't have to equal both a hundred and global it can be a hundred or it can be global because maybe we have a manufacturer that isn't in the department 100 but another department that's global so what we can do is we can delete that and come down to the or row and type in global hit the tab key so it can either equal 100 or global let's go ahead and click on the view button and do we have them all equally 100 of course not because it says it can either equal 100 and if it doesn't it's either 100 or global oh that's fun so that way we can pull in all the rest and coincidentally this one meets both conditions it's a hundred and its global let's go back to the design view and let's do something a bit more how about if we say it can equal global or in addition to the hundred criteria it can be either that or global or how about we also add or spy tech so if I click after global hit the spacebar type in or and then type in spy tech hit the tab key puts the o the lowercase o in uppercase so the conjunction is or and then it puts quotes around the tech spy tech so now it can equal 100 for the department code or it can be global or it can be spy tech so if it meets any one of those three criterias its gonna pull it in let's go ahead and come up here click on the View button ok so it met some of them well the 100 here it pulled in the 100's and then if it didn't meet that it could be either that or we gotta come over here global or spy tech is there any that's not global or spy tech well it's Sun Belt but it does equal the other criteria which is a hundred so it's gonna be pulled in so you can just go crazy with all these horrors and setting up your criteria but it's a good start so let's go ahead and click on the design button to go back and you see all these checkboxes here if you want to be able to set criteria for a field but you don't want to see the field like let's scroll back to the beginning here like the Department code I don't want to see that I just want the criteria to pull in those departments that have 100 for the code you can go ahead and uncheck it click on the View button Department codes not there but the department name is so remember it has to equal 100 or global or spy tech that one son bailed so it's got to have the department code of 100 we can't see it but it's doing its job behind the scenes so that's cool let's go ahead and go back to the design view and one another thing that I want to show you is that you don't have to be in the or field to do an or so for example like the department code it could be 100 space or space 200 hit the tab key oh it likes that didn't give me any problems and you can see that it went from the lowercase o to the upper o so looks like it's ready for him so we've got Wow 4 criterias that it can meet it can be 100 or 200 or global for the manufacturer or spy tech let's go ahead and click on the View button oh you know what let's go back to the design view I do want to see this that would be helpful let's go ahead and click on the View button so there's the department code so it can be 100 or 200 and if it's not 100 or 200 then it has to be spy tech or global well there's two here that's not spy tech with global in its Sun Belt so if it's Sun Belt then it has to have either 100 or 200 for the department code and so let's go back to the design view now before we go any further I want to clear out all the criteria and the quickest way to do that is to go ahead and select the entire row and you can do that by coming over here to the row header for criteria but go right up and just over that line until you can see a black arrow click on it it selects the entire row hit the Delete key and it clears out everything all the criteria now up until this point we learned how to set criteria for the fields that contain the data type of text or numbers how about a date well not with me I mean a set criteria up for a date field to do that let's go ahead and find one oh there we go I scroll over date received so let's say that all the computers that we received before certain date are now outdated and we need to find out who has them and you know buy them new ones so to be able to type this in like all those computers that we received before January 1st 2008 go ahead and type in the less than symbol less than means that anything comes before the date that I'm about to type in which is 1/1 / oh wait now if you want to include that date you want to go ahead and type in the equals just after the less than symbol or if it was greater than comes after it don't type it in before as you recall in an earlier training video access doesn't like that it's got to come after and then after you type it in you hit the tab key to make sure that access accepts it and you can see what it did after I hit the tab key it added the 204 2008 and also it added pound symbols around the date now let's go ahead and see if we have any records that comes prior or before January 1st 2008 come up here click on the View button and we got a total of 22 let's scroll over here there's nothing that says 2008 it saw before it great how about the other way let's go ahead and go back to the design view and flip this around and see if we have any computers that we purchased or receive after January 1st 2008 so hit the Delete key and flip the symbol so it's going greater than or hold down the shift key and hit the period on the keyboard hit the tab key it accepts it beautiful come up here click on the View button and we got a total of two records confirm it okay both of them come after January 1st 2008 great let's go back to the design view and let's kick this up a notch how about if we want to find the computers that we received between two dates ooh that's pretty Trixie us come in here and delete the contents that are in and this is how you do it so if you want it to come after a certain date but before another will type in the greater than symbol and then the date that we want it to come after so greater than 6 / 1 / oh 6 so it has to come after June 1st of 2006 hit the spacebar and then type in an so the an conjunction means that it has to meet both conditions or criteria remember how we did the or or it can be either that or this or that well now it has to meet both it has to come before June 1st of 2006 and then we do less than or and less than 1 / 1 / oh wait hit the tab key and change the lowercase a to an uppercase safer and great and if you can't see it and you need to make changes you can click inside and use the arrow keys or better yet you can right-click on the cell and go to zoom to zoom in and opens up the cell makes it huge so you can go ahead and work with the contents therein a lot easier than that little thing so you see it has to come after that date but before the other date it's got the an there can't be or it's got to meet both criteria z' so let's go ahead and click okey-dokey and take it for a test drive and let's come up here click on the View button and then scroll over to the dates so it has to come after June 1st of O 6 and well that squeak by just by a few days there so it did come after June 1st of o 6 but it has to come earlier than January 1st of 2008 and all of these do so great let's go ahead and go back to the design view and let's change this up a bit so if I want to go ahead and work with the contents therein of course you can right click and go zoom zoom zoom or if you're a keyboard person and you like keyboard shortcuts hold down the shift key and hit f2 well make sure you're in the cell here then hold down the shift and hit f2 and it does the zoom feature as well so instead of using the less than greater than symbols we can go ahead and delete those and instead let's go ahead and use between yeah type in the word between that comes before the first date and then of course we're still going to use the conjunction and because it has to meet both dates so between this date and that date so without using the less than or greater than symbol as I can't nail the exact date of June 1st or January 1st but it's got to be in between so you can do that and go ahead and click okey-dokey hit the tab key go ahead and click on the View button and scroll over so in between those dates let's go back so you can do it that way back to the design view and in addition to that how about if we don't want to see anything listed between those two dates so not that's the operative word not let's go ahead and click before between and type in not spaced hit the tab key puts in an upper case now let's go ahead and click on the View button to find in all those computers that weren't received between June 1st of 2006 and January 1st of 2008 and of course that comes before June 2006 now that one comes after January 1st of 2008 so we're good there and okay looks like we're not seeing anything that's not between those two dates June 1st 2006 and January 1st of 2008 just what comes before and after those two dates now for some reason you have blanks in a cell like let's come up here and delete Bob and then go ahead and hold down the shift key and hit enter let's say that I accidentally deleted it or I simply didn't type it in and I want to find all those records that contain empty values in the first name field because maybe we want to get rid of them purge the database clean it out because they're just well they're obsolete they don't work or maybe go ahead and do some investigation and find out who this person is their first name well you can see it's right there that's easy but let's say we had thousands of records and we want to find all those who don't have a first name let's go ahead and go back to the design view and oh we better clean up our criteria here because we don't want anything else but what we want to look for those records that don't have a first name so let's get rid of this click and drag and delete it and let's go ahead and click and drag this all the way to the beginning there we go first name so to find all those records that have an empty value and the first name nothing in there you want to type in no then hit the tab key and it says is null or is empty so when I come up here and click on the View button if found well that record that doesn't have anything in the first name field let's go ahead and go back to the design view so you can do it that way or how about you want to find or search for only those records that do have something in the first name field so maybe we want to do some marketing and we just don't have time to sort through all those records that don't have an address for in this case a first name to call the person and say mr. Appleman you know it sounds more personable by first name so what we can do is we can come in here where it says is null and type just before null and Ont like not hit the spacebar hit the tab key so you're saying is not empty so let's come up here click on the View button and here's all the rector's that has something or that isn't empty in the first name field okay let's do this another way let me go ahead and go back to the design view and clear this out and just go back and let's give Bob his name back Bob welcome back Bob hold down the shift key hit the enter key and he's back let's go back to the well before we go to the design view you can see in the department column let's do another example as far as searching for those records that don't contain like let's say in the department name the word IT so as opposed to being empty or not being empty let's say we don't want to find those records that have IT so let's go back to the design view and go to the department name the criteria field there and type in less than greater than when you put them back to back like that that means it's not equal to and then of course hit the space bar and type in IT so you don't want it to equal the IT department name hit the tab key puts quotes around it and then let's come up here click on the view button and in the department name do we have any eyeties no because it cannot equal that in order to meet the condition or the criteria to be seen here cool so I don't have to focus on the IT dudes let's go ahead and go back to the design view and one last thing that I think you might find helpful let me delete that and let's go to the last name field you can actually use the less than or greater than symbols for text well more specifically for letters of the alphabet what I'm gonna show you so if I type in the greater than symbol and the letter K for the last name it means that anything that comes after those names that begin with the letter K hit the tab key to make sure everything's goes and then come up here click on the View button and for the last name is there anything that comes after K well it includes anything that begins with letter K so we don't have to type in equals but if we did wouldn't work anyways so includes anybody whose last name begins with the letter K and that comes after it so let me sing that song hijk LMNOP qrs tuv oh it worked great how about if we flip it let's go ahead and right-click go to the design tab and say instead of going anything that comes after how about anything that comes before at the upper end of the alphabet hit the tab key click on the View button and anything that comes before that begins with the letter K ABCD efg hijk doesn't include the case only the greater than symbol includes or is equal to that letter the letter K thanks for watching hey as a quick reminder if you like my video please give it a thumbs up you can also click on me and subscribe to my channel to get notified with the latest videos and four great specials on my products please see the description below this video

Channel: Kirt Kershaw

Views: 16,961

Rating: 4.9078341 out of 5

Keywords: microsoft access 2016, access 2016, access, 2016, microsoft, beginner, how to, basic, table, records, filter, subdatasheets, datasheets, query, design, criteria, wizard

Id: KfLRVthFNfY

Length: 79min 58sec (4798 seconds)

Published: Fri Jul 27 2018