Next up in our series of the Top 10 topics that you should know inside and out for the Texas High School UIL Computer Applications contest regional and state meets is….APPEND & DELETE QUERIES!  This list of 10 topics is focused more on advanced formulas and applications of the programs than it does on basics (e.g. basic math formulas in Excel, letter formats and other Word templates, etc).  To date, the top 5 that we’ve covered have been:

1. IF Statements, Nest IF Statements, COUNTIF, SUMIF in Excel

2. Dates & Times in Excel

3. Select Queries in Access

4. Pivot Tables in Excel

5. VLOOKUP & HLOOKUP formulas in Excel

6. Update Queries in Access

The append and delete queries are frequently tested on the Access portion of regional and state Computer Applications tests. It’s helpful to think of these two types of queries as “opposites.” An Append query allows you to append (tack on) additional records to an existing table. A delete query allows you to delete records (that meet your stipulated criteria) from an existing table.

Keep reading →

This year, we’re putting together a set of topics that coaches & competitors in the Texas High School UIL Computer Applications contest should know in order to be competitive at state and regional levels.  These 10 topics will focus more on advanced formulas and applications of the programs than it will on basics (e.g. basic math formulas in Excel, letter formats and other Word templates, etc).  We covered the first item a while back in our blog post on IF statements…and conditional IF statements, SUMIF, COUNTIF, etc.  The second blog post introduced the second item: Using Times and Dates in Excel.   The third item was covered in our blog on using pivot tables in Microsoft Excel. Number four looked at Select Queries & Query Basics in Access. In this fifth installment, we’ll be looking at VLOOKUP and HLOOKUP formulas in Excel

VLOOKUP and HLOOKUP are “lookup” formulas in Excel that enable you to easily pull in data across different flat data sets. While VLOOKUP is more commonly used, both are frequently used to pull in data from one worksheet or workbook to another based upon a primary key that is common across the two data sets. For example, if you have a set of order information (date, quantity, product, revenue, customer ID) that includes a customer ID in it on one worksheet tab and a set of customer information in another tab that includes the same customer ID (along with customer name, email, address and phone number), you can use a lookup formula to pull in the customer name to the order information table (by finding the customer ID in the customer table and pulling in the customer name associated with that ID). The lookup formula in Excel thus allows you to compare flat files and pull data into a worksheet from a separate data file provided there is some unique field shared by the two data sets. In essence, it allows you to structure a select query in Excel.

Keep reading →