Download
1 / 8
80 likes | 184 Vues
This guide explores key techniques for using Microsoft Access queries to manage and analyze data effectively. Learn how to calculate totals for each order using the SUM function, filter subsets of rows based on specific field values with WHERE clauses, and apply GROUP BY for efficient data organization. Discover advanced patterns, like disambiguating records with common values, and explore how to average values and use related queries for calculation. Ideal for users seeking to enhance their database skills through practical query examples.
E N D
Query 1: What is the total for each order? List OrderID and Order Total. • Summing for distinguished column of number column. • Distinguished column: OrderID • Number column: calculated field: [quantity]*[price] • Need group by • Sum for calculated field
Query 2: • Selecting subset of rows based on value of one field • Use Group By • Use where in field which is used for selection. • Enter condition under criteria.
Query 3: • Selecting subset of rows based on value of one field and existence of relationship • Use Group By • Use where in field which is used for selection. • Enter condition under criteria.
Query 4 • Select subset of rows based on a reference field and a property of count of a field C • choose reference field • use group by • select Count for C field • express property in Criteria
Query 5 • Query 1 composed twice • Additional pattern: LastName not unique. Use ID to disambiguate but don’t show it. Let’s call this the Disambiguation Pattern.
Query 6 • Averaging of distinguished number field • Choose number field to be averaged • Group By • Choose Avg • In this case distinguished number field is calculated by another query!
Query 7 • Select subset of rows based on reference field and property of calculated field. The property itself involves a query. • choose reference field and calculated field • Group by • use where in calculated field • express property under criteria
