All about Queries

After last weeks mamoth effort on creating usable Access Forms, this week we're going to tackle some of the more advanced Queries in Access. Specifically, we'll:

• Create queries based on multiple tables
• See how we can use calculated fields in a query
• Use special aggregate functions in a query (like summing or averaging the results of our query)
• Create action queries (to delete or modify our data!)
• Create parameter queries that ask us a question before running the query!

But before we get stuck into that, take a look at some screenshots of the new Office 2007 lineup! What's different? You'll see examples of the new Word, Excel, Outlook and Access.

If we have some time left over, we're going to see how we can record and modify VBA macros in MS Word just like we did in Excel, with a short activity!

Getting to know Pivot tables!

(Get stuck into this exercise after completing this weeks VBA warmup!)

Sometimes the Excel chart wizard just doesn't cut it...

When you want to do something more complicated with your spreadsheet data, such as displaying a graph of the number of people playing each intstrument, or a graph indicating the number of Fords and Holdens in your stockyard you've got two options:

1. Write some really complicated VBA macro that counts the items in your spreadsheet and then creates a graph of this new information, or
2. Get Pivot tables and Pivot charts to do all the work for you!

So we're going to go with option 2! Microsoft have a great Pivot table tutorial that includes a file to download and follow on with, that will help you to de-mystify this advanced feature of Excel.... and if you get stuck, Brigitte's become quite an expert on Pivot tables :)

Remember, creating your own Pivot report in your 2nd Assignment forms part of the Credit/Distinction criteria!

Note: In case you weren't here last week, I won't be sitting with you this week, but I will be in and out of the lab... so if you see me around and need help, please don't hesitate to ask!

The 10 Commandments of Access

And it came to pass that the cries and lamentations of the Access newbies were heard on high by the gods of the Database, and their hearts were moved to pity for their followers. And they opened their mouths and spake, saying: "Nevermore shall the young and innocent wander witless on their journeys!

Checkout the 10 Commandments of Access from the Access Web :-) Which laws are we breaking or following!? My favourite is this one:

8. Thou shalt not copy and paste other people's code without at least attempting to understand what it does.

Which one do you reckon will be the most useful commandment?