1 / 14

Query Lab 6/17/2014

Query Lab 6/17/2014. Agenda Practice writing queries against Belmont sample database Answer questions about Access Project Part 2. To Begin:. Copy (DON’T OPEN) the QueryBelmont database from K:IS201IS201-HilferLabClass Create a customer query:

makara
Download Presentation

Query Lab 6/17/2014

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Query Lab 6/17/2014 • Agenda • Practice writing queries against Belmont sample database • Answer questions about Access Project Part 2

  2. To Begin: • Copy (DON’T OPEN) the QueryBelmont database from K:\IS201\IS201-Hilfer\LabClass • Create a customer query: • Create a query to show customer information: company, name (concatenated as one field), phone number, city and state • Save this query as qryCustomers

  3. Add Criteria • Make a copy of qryCustomers, name it qry49341Customers • Modify the query to only display customers with a Zip code of 49341 • Don’t display the zip code in the results • You should have 2 result records

  4. Working with dates • Create a query that shows all contracts signed in August 2013. • Name this query qryContracts • How would you modify this to show all August contracts regardless of year?

  5. Using And • Create a query that lists only contracts that are greater than or equal to $50,000 And that were signed in 2013 • Name this query qryAndExample

  6. Using OR • Modify qryAndExample to show all contracts that are greater than or equal to $50,000 Or that were signed in 2013. • Name this query qryORExample

  7. IIF • The IIF function has three arguments: • Expression • True Part • False Part

  8. Using IIF • Create a query that shows ContractNum, SigningDate, Contract Amount and a Contract Size column that displays: • “Large Contract” if the contract is greater than or equal to $50,000. • “Small Contract” if the contract is less than $50,000.

  9. Using IsNull • Copy qryCustomer and name it qryIsNull • Modify the query to not show the company and customer name in separate columns. Instead show the company name and if the company name is null show the customer’s concatenated name.

  10. Queries with multiple tables (joins) • When querying data from multiple tables it is called “joining tables”. • Only include the tables in the query that are needed in the query, all relationships must show up. • Create a query showing customer name, Invlice date, invoice item and invoice amount. • Why do you end up with a strange result?

  11. Joins Continued • Add the Contract table to your query so that the relationship exists. • Rerun the query.

  12. Aggregate Queries • Create a query that returns one row: • The number of invoices • The total of all invoice amounts • The largest invoice • The smallest invoice • The average invoice amount • Name this query qryAggregate

  13. Aggregate Queries Cont • Modify qryAggregate to show the same information grouped by Contract number • Name this query qryGroupBy

  14. Tips • Only include tables you are using in the query design. • If you are joining tables (querying data from multiple tables), the tables must be related in the query design. • Wait to hide fields until the query is working. • Do not use the names of hidden fields in a query – you will end up with a parameter query.

More Related