Amber Hutchison 145 614 572 8252 This review sheet covers most of the information you ll need to know for the access part of your final exam I strongly recommend doing the practice problems for access off of Carmen Please feel free to text me if anything on here doesn t make sense or if you re studying and working on practice problems and are confused I m happy to answer questions and will respond as quickly as I m able General ACCESS REVIEW Primary Keys Unique values on a table There will not be repeats of any of the values listed as a primary key on its own table Foreign Keys The other end of a relationship with a primary key These values can repeat but if RDI is enforced will not contain any values that are not listed as one of the main primary keys on its table Relationships Connections between tables You will always be working with one to many relationships and you will generally want to mark which side is which when drawing your relationship diagram Inner Joins These are the most common type of joins and the default assumption When a relationship is an inner join a query will return only the records that can be found on both tables in the relationship This is useful for any query where you re only concerned about some of the records Outer Joins These joins will be used when you want every record from one table to show up regardless of whether or not there are corresponding records on the other table in the relationship This will most commonly be used in situations where you need three queries to solve a problem but can also be used in situations where you just want to see everything Many to One to Many In some situations you will have two tables that share a primary key table These three tables cannot be used in the same query without causing errors Because of this you will need to split questions like these up into three separate queries The first will find values from of the foreign key tables and will be an outer join The second will find values from the other foreign key table and will also be an outer join The third query will draw from the first two in order to combine them and will be an inner join Referential Data Integrity RDI and Orphans RDI says that there cannot be a record in a foreign key that is not also in the primary key It s important to note that there can be records in the primary key that do not show up on any foreign keys Orphans are records that break this rule Queries Query Name You will usually be asked to name the queries based on the question number For questions that require three queries you ll want to name them something like Query 1A Query 1B Query 1C Tables Required Here you will list all of the tables you used fields from as well as any tables needed to create connections between them You don t necessarily need to have a field from each table in order to include it here Join On This is also referred to as the foreign key and is asking for you to specify which fields connect the tables you re using in your query If you only use one table in your query this will be blank Join Type This will be inner or outer If you re doing a three query question the first two will always be outer joins and the third will always be inner If you only use one table in your query this will be blank Fields Make sure you type the actual field name questions may ask for Last Name when the field is actually called LName If you re using a field from a previous query and you used the total row before it will change the field s name For example if you had previously summed the field called Amount when you go to use the new field it will be called SumOfAmount Tables If a field in your query is on multiple tables in your query it doesn t matter which table you pull it from for inner joins for outer joins you will want to pull the field from the primary key table Total The total row will always be filled for every field or left completely blank You can tell if you need a total row if you have any of the following sum count average avg min or max Each of these works the same way in access as they would in excel and have the same key words Additionally if the word summarize is used in the question there is a strong change you will have a totals row Once you know that you have one of these you will need to fill out the other total row fields as well Check if you have any Where fields which indicates an un shown criteria or any Expressions Once you ve checked all of these anything remaining will be a Group By Sort Sorting moves from left to right if you want a query sorted by A then by B the A field must be listed further to the left in your query design QBE grid than field B Ascending is alphabetical and moving from 1 upwards Descending is reverse alphabetical and moving from infinity downwards like a high score screen If the question specifies that you must show fields in a certain order but sorted in a different order then you will need to include an extra field that is not shown to ensure sorting occurs in the proper order Show Make sure you read carefully the problems will say list A B C D these are the only things you want to show If the problem says something like list A B C D where such and such the such and such is not necessarily shown This isn t as important if you don t have a total row but if you do the row will say Where when it s on the criteria field Fields with Where are never shown Criteria Two criteria on the same row act like an AND both of them will need to be true Two criteria on different rows will act like an OR either one or the other will need to be true If you have criteria such as A and B or A and C you will need to put A on the same row as B as well as A on the same row as C Text Text values will always go inside quotation marks If you want an exact match you will use quotation marks around just the word you want If you only need part of the word to match there are two wildcards and The symbol is any number of wild characters while is a single wild character Any time you are using a wild card even with numbers you will need to use the criteria Like and quotation marks For …