Microsoft Access 2007 2010 part 3 (Query & Reports)

so this is the third video in the series of Microsoft Access 2007 2010 in the first video we talked about tables and in the second video we talked about liberal of tables and also about form how forms are used to enter information in the table so I'm still continuing using the same contact list table we created so I'll just show you here I've got my table here contact list and also had created a form forms I usually use to enter information in a table and appear on the top I can see there's my form I can close that and there's my contact list table with all the stuff that I had entered in this video I'm going to talk about query and we're going to talk about report so query is another word for search and in Access the queries can get very powerful I'll talk about the simple types of queries and how you can use it to find information and then I'll show you at the end of the day when you are working somewhere they just care about the report so how do you create a report so from the create tab there is an option to create a query wizard so I can click on that simple query is what we're going to do click OK again you get to choose which table you want to use if you had more than one table I can add all the field if I think you know what I don't care about the website I can remove that notes I don't need if I don't want the contact ID I can remove that if I wanted to so you can choose what you want and what you don't want I can click Next now that's the name of the query contactless query or whatever you want to call it so if the squarey was doing something particular you could give it that name I'll just call it query 1 for now now at this point if I just choose the open option it will just show me all the results from my table I will rather modify it because so that I can get what I exactly want click modify click finish now this is what a karate looks like these are all the fields and where they have the check mark it means that when I run the query whether it should show me that information on our so if I remove the first name checkmark I will not see the first name in the list when it is created and I'll talk about a little bit so say for example I go to relation and in the criteria line I just type the word family and then there's the run button here in the left-hand corner I click it now I only see the results based on family can go back now say if I say you know what don't show me contact ID and I click run now I don't see the contact ID field I can go back to the design view by clicking the button the left-hand corner under file put the check mark here I'll just remove the family I could do even more like I could come to telephone number and I can type like 905 if that should work nope I need to put the quotations like 905 and star so the star the asterisk that defines that it's a wild card so match 905 but after that I don't care so the same way if I change the area code to 404 that means with the star it means match the first three after that I don't care so I'm saying like 404 star run now I see all the results with 404 so that's in the criteria section the same way I can add another the element to it and I can say you know what in the city I put the bracket and I type what city so that will prompt me with a question what city and I type the answer so say if I run it and it's asking me and I type the word Toronto click okay now I see all the results matching Toronto em so if I want to do the takes I can type the word between and then in the bracket I put a question so say I say start date and bracket and day - close bracket so what I'm saying is that whenever it runs this query it will ask me the question so all right give me the starting date and then give me the ending date and it will give me results based on those dates so say if I'll run it and I type 0 1 / 0 1 / 1950 that's my start and 0 1 / 0 1 / 1985 so I shouldn't get any dates after 1985 of January let's click OK and there's my results and I do not see any date beyond 85 so this is a really good way to get results based on exactly what you want between start date and end date for this one what I'm gonna do is I'm just gonna put a question here and I'm gonna say what relation so every time I drunk I can type friend to get friends results and then every time I run it again I get family ok so I'm just gonna hit save close it and I'm going to rename this right click rename and I'll call it query relations so that it's easy ok you can also right-click on it you can go to the design view you can open it same thing with the contact list right click design view or open same thing with the form so that's a query so you're trying to create a query that will allow you to get your results that you want and you can even create reports based on this query which I'll show you or have results doing things and there are more there is more to query than just as there are other type of queries which will go and do even more things so you can find it into the Help section or you can go on the internet and find them in more information there's the Help section here where you can go and learn more about queries and everything that you wanted to know so the last thing in the elements of access we've done the tables queries and forms but the last thing is the report so I'm going to go to create and in the report also there is a report wizard so I'm going to use that now you see I can I wanna this time around I just want to choose a recreated report on a table not a creating cyclic table I can add all the fields and say I don't know D I don't want B notes I can remove that say I don't want the postal code I can remove that click and remove whatever you want keep what you don't want and I click Next now up here a lot of these things don't worry about it initially just leave the defaults as it is in this case you're kind of using giving some things more priority or more power but you don't have to worry about it you get the basic report next you can sort it if you want it to like say you wanted to sort it by relations instead of contact ID you could do that you can choose whether you want it portrait or landscape in this case I'll choose landscape because I have a lot of fields click Next that's the name I can just say report and I can express fine I click finish and you see now all my information shows up in a nice way on the report the only problem is you see a lot of the information like contact ID is missing birthdate is not proper so I just need to work with the layout so I'm just going to close Print Preview now this is the design view and you can work from here but I think it's easier to work from the layout view so now this is where I can start making changes so say the birth date I'll just start moving I've got all this space here that I can use for the birth date and all of these things so I just click here for first name and I'm holding the shift key so I can click everything so it keeps the relation I'm just going to click on relation and I'm going to reduce so now I've got all this room click here hold the shift key click here and then just I can move this too far first name shift hold it now the contact ID I can make it a little bigger which is easily available again the relation I can just reduce it to make more room now for the date I hold the shift key click on the bottom part now my dates are fine and I can just move it just a little now if I wanted I could just click here shift hold so that the website feel if I want it move it inside okay so that's the way you go about rearranging your report in the layout view you see appear to the first name extended now you have access to like if you wanted to make all of the center with the alignment which is in the home button or even in the format I can make it center right align all of those features that you know from word you can do bold italic if I wanted to make the contact ID bold put a color on it I've just done that and from the design tab I can go back to your report view so this is what my information will look like when I print it so it's easier to work with in the layout view to make changes that you want you can hit save and I can hit close I'll create another report based on the query create report wizard it says query query relations add all the fields I'll remove certain things now I think in this file already remove the stuff click next next next I can make it landscape next and I can just put the word up here that will make sense to me that what is this report based on report relations so now when I hit finish it's going to ask me the question and then if I type your friend then I only get the results based on the query friend so right there now the same thing I have to go back I can close this view close the Print Preview and go to the layout view and now I can start making changes here I'm holding the shift key to select both of them so that I can make room as I move it so that's the way you go about rearranging it so make sure that all the wordings are clear you can rename them if you want it reduce the size first and then you can increase it and then I can also go to format and make it Center so it's moves it away from them now I can hit save design view report view and that's what my information will look like if I close this every time I run this report relations it will run the query so if I've added more records it will give me the new records also so this is the basic idea behind access in terms of tables queries forms and report I'm going to do another video in which I'm just going to introduce the idea behind relational database where you have two different tables and they are linked to each other and just work with that and just show you the same elements just from a different point of view but relationships thank you for watching and watch out for my next video on relationship relational database

Channel: Amir Parmar

Views: 325,036

Rating: 4.8292136 out of 5

Keywords: Microsoft, Access, Query, Reports, access 2007 query, access 2007 reports, access 2010 query, access 2010 reports, Microsoft Access (Software)

Id: LSyUZ1drzBg

Length: 13min 51sec (831 seconds)

Published: Fri Sep 02 2011