440 likes | 631 Views
Access Queries. Office 2013/365. Queries. Most common type of Query is selection(projection) Specify sources for data retrieval table(s) and/or query( ies ) Specify attributes(fields) to be retrieved Selection criteria Calculations that need to be performed Action Queries
E N D
Access Queries Office 2013/365
Queries • Most common type of Query is selection(projection) • Specify sources for data retrieval • table(s) and/or query(ies) • Specify attributes(fields) to be retrieved • Selection criteria • Calculations that need to be performed • Action Queries • Other types such as crosstabs
Query Creation • Structured Query Language (SQL) • The user enters commands according to a pre-defined syntax to retrieve desired data. • Query By Example (QBE) • The user starts with a sample of the table(s) columns and marks the fields he or she wants to include in the answer. • Defaults are available for summarizing and manipulating the data.
Creating Queries • Query Wizard • QBE Query Design • The user starts with a sample of the table(s) columns and marks the fields he or she wants to include in the answer. • Defaults are available for summarizing and manipulating the data. • SQL design (beyond scope of our class) • The user enters commands according to a pre-defined syntax to retrieve desired data.
Creating Query • Wizards • Click on create tab • Select query wizard • Select simple query • Select your data source(s) • Select desired attributes from each data source • Select next • Name query • Click on finish • Limitation • May be able to do a summary calculation, but not a calculated field
Wizard Click on create tab and select Query Wizard
Access creates the SQL needed to do the query for the wizard • SQL for previous query example SELECT Customers.[ID], Customers.[Last Name], Customers.[First Name], Customers.[E-mail Address], Customers.[Job Title], Customers.[Business Phone] FROM Customers;
Design View • Uses QBE (what we will use) • Design View • Click on create tab • Select query design • Select your data sources and click on add • Select attributes from each data source and drag to grid below • Run query • Save query if it will be used on a regular basis • Can create calculated fields
Select attributes from each data source and double click or drag to grid below
Resulting Dynaset SQL SELECT Customers.[ID], Customers.[Last Name], Customers.[First Name], Customers.[E-mail Address], Customers.[Job Title], Customers.[Business Phone] FROM Customers;
Queries with selection criteria • Use design view when using selection criteria • Select your data sources and attributes of interest from your data sources enter selection criteria • Run query
Query to list Customers from CA Selection Criteria
Resulting Dynaset SQL SELECT Customers.ID, Customers.Company, Customers.City, Customers.[State/Province] FROM Customers WHERE (((Customers.[State/Province])="CA"));
Comparison operators <, >, =, <=, >=, and <> Not equal to Logical operators And, Or, and Not Queries(complex selection criteria)
Multiple Criteria • AND operator • Queries that must satisfy more than one condition, with all conditions being true, use the ANDoperator • OR operator • When any one of several criteria is all that is required for a row to be displayed in a dynaset, then you use the OR operator
And Operator Selection Criteria
Resulting Dynaset SQL SELECT Customers.ID, Customers.Company, Customers.City FROM Customers WHERE (((Customers.City)="Los Angelas") AND ((Customers.[State/Province])="CA"));
OR Operator Selection Criteria
Resulting Dynaset SQL SELECT Customers.ID, Customers.Company, Customers.City FROM Customers WHERE (((Customers.[State/Province])="CA")) OR (((Customers.[State/Province])="UT"));
Action queries • Action Queries • Make table • Append • update • Delete • Action queries alter information as opposed to selection queries, which passively display information without altering anything • Back up data before performing action queries • No undo
Action Queries • Make Table • Query selects records from data base and saves the records in a new table • Append • Make query add records to an existing table • Update • Make query update data in an existing table • Delete • Make the query delete the information from an existing table where the data matches the criteria
Action Queries • Use design view to perform action queries • Select create tab • Query design • Select data source(s) • Select data and/or selection criteria • Select design, select action query • Save query • Run query • You will get a pop-up message indicating what the query is about to do
Make table, Append, Update, Delete Depending on action query selected you need to specify fields and/or criteria
Append Query Considerations • Select the table containing the data you want to add • Source may be internal or external • Select the destination • Fields must be of like data type • Field mapping
Destination Source
Note: Access has attempting to map the fields from tblCA to table customer Access maps all fields between the two tables with the same field names
Note: Since the two tables used a different names for this field, manual mapping required