Structured Query Language - Department of Computer …



CS&E 1111/1112 Pre Lab 9 Download from your Lab 8 folder on Carmen: Research Papers.xlsxALL BEGINNING FILES MUST BE DOWNLOADED FROM Carmen OR THEY WILL NOT BE ACCEPTED.creating queries to view data in your databaseThe database has been created, and records have been added. Now it is time to actually use the information to keep track of your client’s charges and payments. We know that we can open up each table and look at the records, and that works fine with a small database. But, what if the database contains 1,000 clients? How can we keep track of who has paid and who has not. Maybe we want to send out bills to our clients, or flyers to previous clients informing them that our services are still available. We can do this by using queries to view subsets of our data. A query is a question you ask the computer. For example, you can ask the computer to show you all clients who owe you money. The computer will show you a view or subset of your information based on the question you ask it. In a query, we call this subset a dynaset. Remember, all the information you view from the query is coming from your database. You are just seeing a piece of it. Not only can you ask the computer to show a subset of your information, but you can also tell the computer to display only certain fields when displaying this dynaset.Structured Query LanguageSo, how do you get the database to create this dynaset? You do this by speaking the database’s language. If someone asks you a question in Spanish, and you don’t know Spanish, you can’t very well answer them, can you? In the same vein, you can’t ask the database a question in English and expect an answer. The database doesn’t know English! The database only knows how to communicate using 0’s and 1’s. That’s it; a group of zeros and ones is the basis of the database’s alphabet. Thus, a language was created so we could have a friendly conversation with the database. This language is called Structured Query Language, or SQL. Therefore, in order to ask your database a question, you are going to have to learn the language called, Structure Query Language. Great, you say, I don’t have the time or the inclination to learn a new language just to communicate with a box of circuits and wires. Well, that is just fine because most databases have a way for you to communicate with them through a cute storyboard called a graphical user’s interface or GUI. You use the GUI to ask your questions, and the database management system or DBMS, creates the SQL for you. MS Access 2010 and the QBE GridEach database has its own GUI that is used to create a query. MS Access 2010 uses the Query by Example grid (QBE grid) to allow us to talk to the database. Let’s use the QBE grid to ask the database some of our own questions, thus creating a database query.Suppose you want a listing of your Clients, but you only want the listing to show the ClientID, First Name, Last Name, and Phone Number. How could you create this list? You would write a query to show a view of your database that will show all your clients by the ClientID, First Name, Last Name, and Phone Number. We will create this query in MS Access 2010 by following the steps below.Download the Research Papers.accdb you downloaded from your Lab 8 folder on Carmen.Open the Research Papers database by double clicking on the Research Papers.accdb file.Click on the Create Tab, and then click on the Query Design Icon. The Query By Example (QBE) Grid now displays on the screen. This is where you will create your queries.414909074930000297751515925802. Click on the Query Design icon002. Click on the Query Design icon-43624510947401. Click on the Create tab001. Click on the Create tab73533025146000Double Click on the Client table selection, and then click the Close Button.340995029838651. Double Click on the Client selection001. Double Click on the Client selection2442210312991500273367535020252. Click on the Close button002. Click on the Close button3672840384746500Double click on the ClientID field, First Name field, Last Name field, and the Phone Number field.Click on the exclamation mark ( ! ) to Run (display) the query.15468604406902. Click on the exclamation mark ( ! )002. Click on the exclamation mark ( ! )396875626110002822575867410 Double click on the following fields to add them to the grid.ClientIDFirstNameLastNameHomePhone00 Double click on the following fields to add them to the grid.ClientIDFirstNameLastNameHomePhone1832610194183000183261014693900018326101248410001832610135509000The query displays the view of the database that you requested. You have limited your view to only the fields in the Client table that you added to the QBE grid. Remember, this is just a view of your database. You can make changes to any of these records in this view and it will make the changes in your database. Now we will close the query and save it. After the query is saved, it becomes a virtual table and this query can be used to create reports, and forms, or it can also be used as input to other queries.2260603117215Don’t worry about the order of the records in this dynaset.Just make sure all 16 records are displayed.00Don’t worry about the order of the records in this dynaset.Just make sure all 16 records are displayed.Click on the close button to close the query, click on the Yes button, type in the name Client Phone Numbers Query, and click the OK Button to save the query.163639535445702. Click on the Yes button002. Click on the Yes button259842025895300028619457531101. Click on the Close button001. Click on the Close button486537089027000157289512350750018364206972300026365205441953. Type the name, Client Phone Numbers Query003. Type the name, Client Phone Numbers Query6108702393954. Click on the OK button004. Click on the OK buttonNow you can double click on the Client Phone Numbers Query you created and the dynaset showing the list you created will display. In addition, the list will show any new or updated records you have added to your database.Double Click on the Client Phone Numbers Query, and then close the query.-723901624330NOTE: When completing this lab, don’t worry if your sidebar doesn’t look exactly like the display. 00NOTE: When completing this lab, don’t worry if your sidebar doesn’t look exactly like the display. 8185159467850019837407334251. Double Click on the Client Phone Numbers Query001. Double Click on the Client Phone Numbers QueryInner Joins in a QueryOk, so that was easy, right? But, what if we want to display a list of each payment that has been made by our clients? The list should probably display the ClientID, First Name, Last Name, Payment Amount, and Payment Date. In order for us to display this information we need to use two different tables in our query; Client, and Payments. Ok, so let’s do it!Click on the Create Tab, and then click on the Query Design Icon to display a new Query By Example (QBE) Grid.Click the Client table selection, click the Add Button, click the Payments table selection, then click the Add Button. Finally, click the Close Button.-48704529851354. Double Click on the Payments selection004. Double Click on the Payments selection348234029851353. Double Click on the Client selection003. Double Click on the Client selection244221031311850016554453274695003672840384873500273367535032955. Click on the Close button005. Click on the Close button73152029146500-44005511347451. Click on the Create tab001. Click on the Create tab414528078930500297370516325852. Click on the Query Design icon002. Click on the Query Design iconLet’s take a close look at this QBE grid. Notice there are two tables displaying with a line attaching them by ClientID. This line shows that there is a relationship already established between the Client table and the Payments table. This relationship was created by you when you set up the relationships for the database. You should remember two major points when creating queries.Only show the tables on the grid that will be used in your query. Adding extra tables to the QBE grid may cause the dynaset to display incorrectly.All tables shown in the query must either be directly or indirectly linked in order for the query to run correctly. Usually this is the case, but there are some scenarios where you will have to create the relationship on the QBE grid yourself.Double click on the ClientID, FirstName, LastName, fields on the Client table to add them to the grid. Double click on the Amount and PaymentDate field to add them to the grid.Finally, click on the Run Button to display the dynaset of the query.321564015570200038023801365250009455152233295Double click on the following fields to add them to the grid.ClientID, FirstName, LastName, Amount and PaymentDate00Double click on the following fields to add them to the grid.ClientID, FirstName, LastName, Amount and PaymentDate312674013646150012039601249680004851404298950016351252444752. Click on the exclamation mark ( ! )002. Click on the exclamation mark ( ! )120396014789150012039601364615001203960125793500Notice the dynaset displayed below. The list shows the information for clients who have made a payment. A record is displayed for each payment made. Again, we can make changes to any field in the list and the changes will also update the database because the dynaset is just a view of the database. After we save this query, as long as we don’t delete it, we have created a new virtual record set that exists as long as the query exists. You may have noticed the dynaset does not include all the records from the Client table. This is because the query we created will only show matching records from each table. For example, record J7525 Karen Day, is on the Client table, but there is no record for her on the Payments table. Therefore, her record will not display on the dynaset. When tables have a line joining them on a query, the tables are said to be joined by the field the line connects to on each table. By default, joins of this type are called Inner Joins. This simply means that the dynaset, like the one shown below, will only display matching records from the two tables listed on the QBE grid. What if we want all the records from the Client table, regardless if there is a matching record on the payments table? We would use an Outer Join when creating the query. Outer joins will be discussed in Chapter xx.2184402423160Don’t worry about the order of the records in this dynaset.Just make sure all 11 records are displayed.00Don’t worry about the order of the records in this dynaset.Just make sure all 11 records are displayed.Click on the close button to close the query, click on the Yes button when asked to save the query, type in the name Client Payments Query, and click the OK Button to save the query.141922534544002. Click on the Yes button002. Click on the Yes button259080026111200026638257162801. Click on the Close button001. Click on the Close button49911008877300016598906896100024599905365753. Type the name, Client Payments Query003. Type the name, Client Payments Query149987012503150053784517786354. Click on the OK button004. Click on the OK buttonThus far we have created queries that list all the information in our database based on the fields we add to our Query By Example grid. But, what if we want to create a list of information that is based on a certain set of criterion? We can do this by adding the criteria to the QBE grid. For example, if you wanted to make a list of all your Clients who live in Ohio the query would be created as follows.Click on the Create Tab, and then click on the Query Design Icon to display a new Query By Example (QBE) Grid.294322515240002. Click on the Query Design icon002. Click on the Query Design icon41148006807200070104030607000-47053511493501. Click on the Create tab001. Click on the Create tabDouble click on the Client table selection and then click the Close Button.354711029857701. Double Click on the Client selection001. Double Click on the Client selection3682365386715000274320035217102. Click on the Close button002. Click on the Close button2506980313182000Double click on the ClientID, FirstName, LastName, City, and State fields to add them to the grid.Add the criteria “OH” in the criteria row underneath State field on the QBE grid.Click the Run icon to run the query.117348017379950011734801654175006197604485005When typing in any non-numeric criteria, always surround the criteria with quotes.00When typing in any non-numeric criteria, always surround the criteria with quotes.332613039852602. Type in “OH” as the Criteria002. Type in “OH” as the Criteria438912035286950011163302673353. Click on the Run icon003. Click on the Run icon51117544259500-2279652256155 Double click on the following fields to add them to the grid.ClientID, FirstName, LastName, City and State.00 Double click on the following fields to add them to the grid.ClientID, FirstName, LastName, City and State.117348013874750011734801272540001173480128079500The dynaset created from this query will show the ClientID, First Name, Last Name, City, and State for all clients in OH.Close the query, name it Ohio Clients Query, and click the OK button8477253967480Don’t worry about the order of the records in this dynaset. Just make sure 6 records are displayedJust make sure all 6 records are displayed.00Don’t worry about the order of the records in this dynaset. Just make sure 6 records are displayedJust make sure all 6 records are displayed.40379651598295Ask me about this00Ask me about this32378651791335002563495261937500139192034626552. Click on the Yes button002. Click on the Yes button49364908794750026092157080251. Click on the Close button001. Click on the Close button267652555225953. Type the name, Ohio Clients Query003. Type the name, Ohio Clients Query149098061906150052895567189354. Click on the OK button004. Click on the OK button1876425567563000Practice using different Criterion in QueriesAdding criterion to the query allows us to view as much or as little information in our database as we need. This is the beauty of a query using different criterion. But remember, we have to talk to the database using its language; therefore there is a certain way we have to add the criterion in order for the database to understand. Here are some rules to follow when you are adding criterion to the QBE grid.If the criterion is text or any special characters, you must surround it with quotes on the QBE grid. For example, if you want a list of all Clients from Washington, you must put “WA” in the criteria row as shown on the grid below.FieldClientIDFirstNameLastNameStateZipCodeTableClientClientClientClientClientSortShowxxxxxCriteria“WA”OrOrNow, let’s try it.Click on the Create Tab, and then click on the Query Design Icon to display a new Query By Example (QBE) Grid.Double Click the Client table selection, and then click the Close Button.358711533699453. Double Click on the Client selection003. Double Click on the Client selection2463165355981000264731537960304. Click on the Close button004. Click on the Close button3587115414147000358711553594000241554013792202. Click on the Query Design icon002. Click on the Query Design icon64960529464000-52197011379201. Click on the Create tab001. Click on the Create tabDouble click on the ClientID, FirstName, LastName, City, and State fields to add them to the grid.Add the criteria “WA” in the criteria row underneath State field on the QBE grid.Click the Run icon to run the query.10782301532255001078230145605500274320043230802. Type in “WA” as the Criteria002. Type in “WA” as the Criteria38061903866515004159253187700010210801435103. Click on the Run icon003. Click on the Run icon1078230137795000107823012636500010782301148715001078230115697000-3232152132330 Double click on the following fields to add them to the grid.ClientID, FIrstName, LastName, City and State.00 Double click on the following fields to add them to the grid.ClientID, FIrstName, LastName, City and State.Close the query, name it Washington Clients Query, and click the OK button5283203994150Don’t worry about the order of the records in this dynaset.Just make sure all 7 records are displayed.00Don’t worry about the order of the records in this dynaset.Just make sure all 7 records are displayed.2620645264541000144907034886902. Click on the Yes button002. Click on the Yes button49936409055100026663657340601. Click on the Close button001. Click on the Close button132905510229850036703015513054. Click on the OK button004. Click on the OK button19335756019800027336754489453. Type the name Washington Clients Query003. Type the name Washington Clients QueryNow, you do this one!Write a query that lists the ClientID, First Name, Last Name, City, and State for all clients who live in Chicago. Name the query, Chicago Clients Query. If your query is correct, you will see the following dynaset.2203451657350Don’t worry about the order of the records in this dynaset. Just make sure all 3 records are displayed.Just make sure all 6 records are displayed.00Don’t worry about the order of the records in this dynaset. Just make sure all 3 records are displayed.Just make sure all 6 records are displayed.If the criterion is numeric, you type the number only on the QBE grid. For example, if you want a list of all Clients who made a payment of $200, you will type in 200 on the grid in the criteria row.FieldClientIDFirstNameLastNameAmountTableClientClientClientPaymentsSortShowxxxxCriteria200OrOrNow, let’s try it.Click on the Create Tab, and then click on the Query Design Icon to display a new Query By Example (QBE) Grid.Double click the Client table selection, and the Payments table selection, and then click the Close Button.8636034759904. Double Click on the Payments selection004. Double Click on the Payments selection20085053673475002442210358521000356616033953453. Double Click on the Client selection003. Double Click on the Client selection356616056134000239458514046202. Click on the Query Design icon002. Click on the Query Design icon62865032004000-54292511633201. Click on the Create tab001. Click on the Create tab3566160416687000262636038214305. Click on the Close button005. Click on the Close buttonDouble click on the ClientID, FirstName, LastName, and Amount fields to add them to the grid.Add the criteria 200 in the criteria row underneath Amount field on the QBE grid.Click the Run icon to run the query.7181854539615When typing numeric criteria no quotes are needed.00When typing numeric criteria no quotes are needed.263461539884352. Type in 200 as the Criteria002. Type in 200 as the Criteria36976053531870001257300145859500125730012566650012573001363345001257300124841000342900013633450030676851363345001054102232025 Double click on the following fields to add them to the grid.ClientID, FirstName, LastName, and Amount.00 Double click on the following fields to add them to the grid.ClientID, FirstName, LastName, and Amount.11734801003303. Click on the Run icon003. Click on the Run icon56832527559000Close the query, name it Payments of $200 Query, and click the OK button2242863920981Don’t worry about the order of the records in this dynaset.Just make sure 2 records are displayed.00Don’t worry about the order of the records in this dynaset.Just make sure 2 records are displayed.2626995262382000145542034671002. Click on the Yes button002. Click on the Yes button49999908839200026727157124701. Click on the Close button001. Click on the Close button29686255454653. Type the name Payments of $200 Query003. Type the name Payments of $200 Query156400512211050060198017494254. Click on the OK button004. Click on the OK button216852569850000If the criterion is a date, you enclose the date with pound signs (#) on the QBE grid. For example, if you want a list of all Clients who made payments on 3/8/2008, you type in #3/8/2008# on the grid in the criteria row.FieldClientIDFirstNameLastNameAmountPaymentDateTableClientClientClientPaymentsPaymentsSortShowxxxxxCriteria#3/8/2008#OrOrNow, let’s try it.Click on the Create Tab, and then click on the Query Design Icon to display a new Query By Example (QBE) Grid.Double click the Client table selection, and the Payments table selection, and then click the Close Button.3846195432181000438658041313105. Click on the Close button005. Click on the Close button121539035261554. Double Click on the Payments selection004. Double Click on the Payments selection22860003295650002449830314007500357378029502103. Double Click on the Client selection003. Double Click on the Client selection71247029337000-45910511366501. Click on the Create tab001. Click on the Create tab412242074295000295084515862302. Click on the Query Design icon002. Click on the Query Design iconDouble click on the ClientID, FirstName, LastName, Amount, and PaymentDate fields to add them to the grid.Add the criteria #3/8/2008# in the criteria row underneath PaymentDate field on the QBE grid.Click the Run icon to run the query.5448302244090Double click on the following fields to add them to the grid.ClientID, FirstName, LastName, Amount, and PaymentDate.00Double click on the following fields to add them to the grid.ClientID, FirstName, LastName, Amount, and PaymentDate.327406039579552. Type in #3/8/2008# as the Criteria002. Type in #3/8/2008# as the Criteria448246535013900031775401375410003220720158051500374332513754100013030201375410001303020126047500130302012687300013030201470660004768854679950010820402927353. Click on the Run icon003. Click on the Run iconClose the query, name it Payments Made on 3-8-2008 Query, and click the OK button2463803886835Don’t worry about the order of the records in this dynaset.Just make sure 2 records are displayed.00Don’t worry about the order of the records in this dynaset.Just make sure 2 records are displayed.2573655260223000140208034455102. Click on the Yes button002. Click on the Yes button49466508623300026193756908801. Click on the Close button001. Click on the Close button151066561067950054864066351154. Click on the OK button004. Click on the OK button27051004845053. Type the name Payments Made on 3-8-2008 Query003. Type the name Payments Made on 3-8-2008 Query211518567691000Now, you do this one!Write a query that lists the ClientID, First Name, Last Name, Amount and PaymentDate for all payments made on 1/16/2008. Name the query, Payments Made on 1-16-2008 Query. If your query is correct, you will see the following dynaset.6483352253615Don’t worry about the order of the records in this dynaset.Just make sure 2 records are displayed.00Don’t worry about the order of the records in this dynaset.Just make sure 2 records are displayed.If the criterion is a range, you use the keywords Between/And on the QBE grid. For example, if you want a list of all Clients who made payments between 1/1/2008 and 3/8/2008, you type in Between #1/1/2008# And #3/8/2008# on the grid in the criteria row.FieldClientIDFirstNameLastNameAmountPaymentDateTableClientClientClientPaymentsPaymentsSortShowxxxxxCriteriaBetween #1/1/2008# And #3/8/2008# OrOrNow, let’s try it.Click on the Create Tab, and then click on the Query Design Icon to display a new Query By Example (QBE) Grid.Double click the Client table selection, and the Payments table selection, and then click the Close Button.3674745407225500421513038817555. Click on the Close button005. Click on the Close button117411534315404. Double Click on the Payments selection004. Double Click on the Payments selection22447253201035002282190297878500340614027889203. Double Click on the Client selection003. Double Click on the Client selection389826564833500272669014916152. Click on the Query Design icon002. Click on the Query Design icon71247030289500-45910511461751. Click on the Create tab001. Click on the Create tabDouble click on the ClientID, FirstName, LastName, Amount, and PaymentDate fields to add them to the grid.Add the criteria Between #1/1/2008# And #3/8/2008# in the criteria row underneath the PaymentDate field on the QBE grid.Click the Run icon to run the query.-21907532931102. Type in Between #1/1/2008# And #3/8/2008# as the Criteria002. Type in Between #1/1/2008# And #3/8/2008# as the Criteria372999034823400037299901356360004635504489450010687052736853. Click on the Run icon003. Click on the Run icon31642051356360001289685135636000128968512414250012896851249680001377952225040 Double click on the following fields to add them to the grid.ClientID, FirstName, LastName, Amount, and PaymentDate.00 Double click on the following fields to add them to the grid.ClientID, FirstName, LastName, Amount, and PaymentDate.32073851561465001289685145161000Close the query, name it Payments Between 1-1 And 3-8 Query, and click the OK button2305053907155Don’t worry about the order of the records in this dynaset.Just make sure 11 records are displayed.00Don’t worry about the order of the records in this dynaset.Just make sure 11 records are displayed.2549525263588500137795034791652. Click on the Yes button002. Click on the Yes button49225208959850025952457245351. Click on the Close button001. Click on the Close button148653561404500052451066687704. Click on the OK button004. Click on the OK button26809705181603. Type the name Payments Between 1-1 And 3-8 Query003. Type the name Payments Between 1-1 And 3-8 Query222250071120000Now, you do this one!Write a query that lists the ClientID, First Name, Last Name, Amount and PaymentDate for all payments between 100-200. (The criteria under the Amount field should be, Between 100 and 200)Name the query, Payments Between $100-$200 Query. If your query is correct, you will see the following dynaset.5226052439035Don’t worry about the order of the records in this dynaset.Just make sure 6 records are displayed.00Don’t worry about the order of the records in this dynaset.Just make sure 6 records are displayed.So far the when we have input a criterion, we have assumed we are looking for information that is equal to the criterion, but what if we are looking for information that is either greater than or less than the criterion? We would specify this in on the QBE grid. For example, we would like a list of all Clients that paid $200 or more. The QBE grid would look as follows.FieldClientIDFirstNameLastNameAmountPaymentDateTableClientClientClientPaymentsPaymentsSortShowxxxxxCriteria>=200OrOrNow, let’s try it.Click on the Create Tab, and then click on the Query Design Icon to display a new Query By Example (QBE) Grid.Double click the Client table selection, and the Payments table selection, and then click the Close Button.2434590307911500355854028892503. Double Click on the Client selection003. Double Click on the Client selection3741420420179500428180540112955. Click on the Close button005. Click on the Close button72199533655000-44958011798301. Click on the Create tab001. Click on the Create tab405066568199000287909015252702. Click on the Query Design icon002. Click on the Query Design icon2397125323469000132651534651954. Double Click on the Payments selection004. Double Click on the Payments selectionDouble click on the ClientID, FirstName, LastName, Amount, and PaymentDate fields to add them to the grid.Add the criteria >=200 in the criteria row underneath Amount field on the QBE grid.Click the Run icon to run the query.5251452228215Double click on the following fields to add them to the grid.ClientID, FIrstName, LastName, Amount, and PaymentDate.00Double click on the following fields to add them to the grid.ClientID, FIrstName, LastName, Amount, and PaymentDate.93535532962852. Type in >= 200 as the Criteria002. Type in >= 200 as the Criteria3063240348551500130873514547850037490401359535004826004521200010877552768603. Click on the Run icon003. Click on the Run icon31832551359535001308735135953500130873512446000013087351252855003226435156464000Close the query, name it name Payments >= $200 Query, and click the OK button3949703898900Don’t worry about the order of the records in this dynaset.Just make sure 4 records are displayed.00Don’t worry about the order of the records in this dynaset.Just make sure 4 records are displayed.2597150261429500142557534575752. Click on the Yes button002. Click on the Yes button49701458743950026428707029451. Click on the Close button001. Click on the Close button2270125559689000182880012782550027285954965703. Type the name Payments >= $200 Query003. Type the name Payments >= $200 Query8661402825754. Click on the OK button004. Click on the OK buttonNow, you do this one!Write a query that lists the ClientID, First Name, Last Name, Amount and ChargeDate for all charges on or after 5/1/2008. Name the query, Charges On or After 5-1-2008 Query. (The criteria under the ChargeDate field will be >= #5/1/2008#) If your query is correct, you will see the following dynaset.4229103064510Don’t worry about the order of the records in this dynaset.Just make sure 13 records are displayed.00Don’t worry about the order of the records in this dynaset.Just make sure 13 records are displayed.We can also specify multiple criteria on the QBE grid. Let’s say, we would like a list of all Clients who made payments of $250 or more, OR made payments of less than $100. The QBE grid would look as follows. Notice the criterion is in separate rows. This indicates an OR scenario.FieldClientIDFirstNameLastNameAmountPaymentDateTableClientClientClientPaymentsPaymentsSortShowxxxXxCriteria>=250Or<100OrThe grid could also use the keyword OR.FieldClientIDFirstNameLastNameAmountPaymentDateTableClientClientClientPaymentsPaymentsSortShowxxxXxCriteria>=250 or <100OrOrNow, let’s try it.Click on the Create Tab, and then click on the Query Design Icon to display a new Query By Example (QBE) Grid.Double click the Client table selection, and the Payments table selection, and then click the Close ButtonDouble click on the ClientID, FirstName, LastName, Amount, and PaymentDate fields to add them to the grid.Add the criteria >=250 or <100 in the criteria row underneath Amount field on the QBE grid.Click the Run icon to run the query.27622532924752. Type in >= 250 or < 100 as the Criteria002. Type in >= 250 or < 100 as the Criteria133985014509750037801551355725005137154483100011188702730503. Click on the Run icon003. Click on the Run icon32143701355725001339850135572500133985012407900013398501249045001879602224405 Double click on the following fields to add them to the grid.ClientID, FIrstName, LastName, Amount, and PaymentDate.00 Double click on the following fields to add them to the grid.ClientID, FIrstName, LastName, Amount, and PaymentDate.32575501560830003094355348170500 Close the query, name it Payments >= $250 or < $100 Query, and click the OK button2635253914775Don’t worry about the order of the records in this dynaset.Just make sure 5 records are displayed.00Don’t worry about the order of the records in this dynaset.Just make sure 5 records are displayed.2625725261683500145415034601152. Click on the Yes button002. Click on the Yes button49987208769350026714457054851. Click on the Close button001. Click on the Close button229870055994300061468017354554. Click on the OK button004. Click on the OK button157734012071350027571704991103. Type the name Payments >= $250 or < $100 Query003. Type the name Payments >= $250 or < $100 QueryNow, you do this one!Write a query that lists the ClientID, First Name, Last Name, City, and State field for all clients who live in either in Columbus or Cincinnati. Name the query, Clients in Columbus OR Cincinnati Query. If your query is correct, you will see the following dynaset.5162552269490Don’t worry about the order of the records in this dynaset.Just make sure 6 records are displayed.00Don’t worry about the order of the records in this dynaset.Just make sure 6 records are displayed.Let’s say we want a list of all Clients that paid $100 or more after 3/3/2008. The QBE grid would look as follows. Notice the criterion is in the same row. This indicates an AND scenario.FieldClientIDFirstNameLastNameAmountPaymentDateTableClientClientClientPaymentsPaymentsSortShowxxxXxCriteria>=100> #/3/3/2008#OrOrNow, let’s try it.Click on the Create Tab, and then click on the Query Design Icon to display a new Query By Example (QBE) Grid.Double click the Client table selection, and the Payments table selection, and then click the Close ButtonDouble click on the ClientID, FirstName, LastName, Amount, and PaymentDate fields to add them to the grid.Add the criteria >=100 in the criteria row underneath Amount field on the QBE grid.Add the criteria >#3/3/2008# in the criteria row underneath PaymentDate field on the QBE grid.Click the Run icon to run the query.If your query is correct, you will see the following dynaset. Name the query, Payments >= $100 And Payment Date > 3-3 Query.Now, you do this one!Write a query that lists the ClientID, First Name, Last Name, Amount and PaymentDate field for all clients who have payments greater than or equal to $200 with a payment date after 3/3/2008. Name the query, Payments >= $200 And Payment Date > 3-3 Query. If your query is correct, you will see the following dynaset.We can also specify compound criteria on the QBE grid. Let’s say, we would like a list of all Clients that made payments of $200 or more, on or after 2/1/2008 or all Clients that made payments less than $150 before 3/1/2008. The QBE grid would look as follows. FieldClientIDFirstNameLastNameAmountDateTableClientClientClientPaymentsPaymentsSortShowxxxXxCriteria>= 200>= #2/1/2008#Or<150< #3/1/2008#OrNow, let’s try it.Click on the Create Tab, and then click on the Query Design Icon to display a new Query By Example (QBE) Grid.Double click the Client table selection, and the Payments table selection, and then click the Close ButtonDouble click on the ClientID, FirstName, LastName, Amount, and PaymentDate fields to add them to the grid.Add the criteria >=200 in the criteria row underneath Amount field on the QBE grid.Add the criteria <150 in the OR row underneath Amount field on the QBE grid.Add the criteria >=#2/1/2008# in the criteria row underneath PaymentDate field on the QBE grid.Add the criteria <#3/1/2008# in the OR row underneath PaymentDate field on the QBE grid.Click the Run icon to run the query.If your query is correct, you will see the following dynaset. Name the query, Payment Compound Criteria Query.1047753129915Don’t worry about the order of the records in this dynaset.Just make sure 9 records are displayed.00Don’t worry about the order of the records in this dynaset.Just make sure 9 records are displayed.Now, you do this one!Write a query that lists the ClientID, First Name, Last Name, Amount and ChargeDate field for all clients who have charges equal to $100 and the charge date is after 5/15/2008 OR have charges equal to $250 and the charge date is on or before 3/15/2008 . Name the query, Charges Compound Criteria Query. If your query is correct you will see the following dynaset.4464053027680Don’t worry about the order of the records in this dynaset.Just make sure 7 records are displayed.00Don’t worry about the order of the records in this dynaset.Just make sure 7 records are displayed.We can also create a query with a fuzzy search by using wild cards, (An * is considered a wildcard character in a database). Let’s say, we would like a list of all Clients whose last name begins with S or D. We would use the keyword Like and * to create this query. FieldFirstNameLastNameTableClientClientSortShowxxCriteriaLike “S*”OrLike “D*”OrYou query could also look as follows:FieldFirstNameLastNameTableClientClientSortShowxxCriteriaLike “S*” or Like “D*”OrOrNow, let’s try it.Click on the Create Tab, and then click on the Query Design Icon to display a new Query By Example (QBE) Grid.Double click the Client table selection and then click the Close ButtonDouble click on the FirstName, LastName fields to add them to the grid.Add the criteria Like “S*” in the criteria row underneath Last Name field on the QBE grid.Add the criteria Like “D*” in the OR row underneath LastName field on the QBE grid.Click the Run icon to run the query.If your query is correct, you will see the following dynaset. Name the query, Last Name Wildcard Query.4375152947035Don’t worry about the order of the records in this dynaset.Just make sure 6 records are displayed.00Don’t worry about the order of the records in this dynaset.Just make sure 6 records are displayed.Now, you do this one!Write a query that lists the ClientID, First Name, Last Name, for all clients whose Last Name begins with Da . (The criteria will be Like “Da*”) Name the query, Last Name DA Wild Card Query. If your query is correct you will see the following dynaset.1968502738755Don’t worry about the order of the records in this dynaset.Just make sure 3 records are displayed.00Don’t worry about the order of the records in this dynaset.Just make sure 3 records are displayed.You can also sort the information you display on your list. For example, you can sort the list in ascending order by state as shown in this QBE grid.FieldClientIDFirstNameLastNameCityStateTableClientClientClientClientClientSortAscendingShowxxxxxCriteriaOrOrNow, let’s try it.Click on the Create Tab, and then click on the Query Design Icon to display a new Query By Example (QBE) Grid.Double click the Client table selection and then click the Close ButtonDouble click on the ClientID, FirstName, LastName, City, and State fields to add them to the grid.Add Ascending in the Sort row underneath State field on the QBE grid.Click the Run icon to run the queryIf your query is correct, you will see the following dynaset. Name the query, State Sort Query.13093703154045The dynaset is sorted in State order.00The dynaset is sorted in State order.You can also sort the information you display on your list using two fields for the sort order. For example, you can sort the list in ascending order by city, and then by state. The main sort has to be to the left of the minor sort. Therefore, because we want to sort by State first and then by City, we must show the sort in the order below. Notice, we are only sorting with the second City field. We are not showing that City field in our list.FieldClientIDFirstNameLastNameCityStateCityTableClientClientClientClientClientSortAscendingAscendingShowxxxxxCriteriaOrOrNow, let’s try it.Click on the Create Tab, and then click on the Query Design Icon to display a new Query By Example (QBE) Grid.Double click the Client table selection and then click the Close ButtonDouble click on the ClientID, FirstName, LastName, City, State, and then add the City field again to add them to the grid.Add Ascending in the sort row underneath State field on the QBE grid.Add Ascending in the sort row underneath the second city Field on the QBE grid.Take off the show checkmark on the last City field on the QBE grid.Click the Run icon to run the query.If your query is correct, you will see the following dynaset. Name the query, State City Sort Query.5480052915920The dynaset is sorted first by State, then by City.00The dynaset is sorted first by State, then by City.Now, you do this one!Write a query that lists the ClientID, Last Name, and First Name. Sort the query alphabetically by Last Name, and then by First name. Name the query, Last First Name Sort Query. If your query is correct you will see the following dynaset.2432052590165The dynaset should look exactly like the one shown.The dynaset is sorted alphabetically by Last Name, and then by First Name.Notice the sort order of the 2 records with the last name of Smith.00The dynaset should look exactly like the one shown.The dynaset is sorted alphabetically by Last Name, and then by First Name.Notice the sort order of the 2 records with the last name of Smith.You can add a calculated field to your database. Suppose you have decided to give everyone a 10% break on their charges. Let’s write a query to show the Charge amount without discount, the discount amount and the Charge Amount after the discount.FieldFirstNameLastNameAmountDiscount:[Amount]*.10New Charge:[Amount]-[Discount]TableClientClientChargesSortShowxxxxxCriteriaOrOrNow, let’s try it.Click on the Create Tab, and then click on the Query Design Icon to display a new Query By Example (QBE) Grid.Double click the Client table selection and the Charges selection and then click the Close ButtonDouble click on the FirstName, LastName and Amount field to add them to the grid.Type in the following on the first open top cell on the QBE grid.Discount:[Amount] * .10Type in the following on the first open top cell on the QBE grid.New Charge:[Amount]-[Discount]Notice there will be no table under these two selections on the QBE grid.Click the Run icon to run the query.14947902135505You QBE grid should look as shown below.00You QBE grid should look as shown below.If your query is correct, you will see the following dynaset. Name the query, New Charge Query.4044953774440Don’t worry about the order of the records in this dynaset.Just make sure 23 records are displayed.00Don’t worry about the order of the records in this dynaset.Just make sure 23 records are displayed.Now, you do this one!Write a query that creates a new calculated field to your database called Revised Charge that adds $10 to each Client Charge. The query should show the client Last Name, First Name, Old Charge Amount and the Revised Charge Amount. Name the query, Revised Charges Query. If your query is correct you will see the following dynaset.3333753703320Don’t worry about the order of the records in this dynaset.Just make sure 23 records are displayed.00Don’t worry about the order of the records in this dynaset.Just make sure 23 records are displayed.If the criterion is a checkbox field, you use the words yes or no, (without “ “), on the QBE gridEXAMPLE NOT SHOWNI WILL SHOW YOU AN EXAMPLE OF THIS DURING THE LECTURE.-212090490855Pre Lab 9 SubmissionResearch Papers.accdb00Pre Lab 9 SubmissionResearch Papers.accdb ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download