Transcript Lecture 5
Summary Queries • Query Wizard – This is a choice once you select fields – Much easier to develop some summary queries with the wizard, then learn how to do it in design view – Wizard is pretty “smart”, it knows about dates, etc. • Examples: mean length of fish, count of fish by species Summary Queries • Queries can operate using the results of other queries • This is critical because many operations you may want to perform can not be done in a single query (i.e., it takes more than one step to get what you want) – Example: proportions of species by sample date • First, use summary query to get counts by each species • Second, use summary query to get total catch for each date • Third, merge total catch back with counts, and calculate proportions Crosstab queries • Crosstab queries are extremely powerful tools to create tables • Basic idea is that values of some field can become new field names (e.g., individual fish species caught become columns) • As with summary queries, easiest to start with Wizard. To create new crosstab query, start in queries, then select new from menu bar within database window • Best seen through example: catch table Crosstab queries • As far as I know, crosstab queries can only operate on a single table or other query, so you may need to first use a select query to get the data together that you want in the query • I haven’t used pivot tables, but they can apparently create similar output as a crosstab query Crosstab queries • http://support.microsoft.com/default.aspx? scid=http://support.microsoft.com:80/supp ort/kb/articles/Q132/1/40.asp&NoWebCont ent=1 • Web site for exchanging nulls to zeros in crosstab query Action Queries • Action queries modify the data in a table, so you want to be VERY careful using these – Update query: replaces existing data – Append query: adds new records to a table – Delete query: removes records from a table – Make-table query: creates a new table • We won’t cover these in depth. They are useful, but I’ve never really had to use them Querying Databases Part Deux • Examples of using multiple queries to accomplish particular analysis tasks – First example – determining percent of total catch by species Percent of Total Catch • First need to recognize current database structure – Fish catch lists individual fish, so first need to compute total catch for each species – After get total catch for each species, want to get total catch – Percent of total catch is gotten by dividing species-specific catch by total catch, so need to merge or join this information somehow Percent of Total Catch • Fish catch lists individual fish, so first need to compute total catch for each species – Do summary query on fish data table Percent of Total Catch • So far – we computed totals and percentages for all records. A refinement would be to compute this for individual sampling dates. • We do this by grouping (by groups) in the summary tables