Advanced Access queries using Totals!

It's time to try designing some advanced queries in Access! So far, the queries we've been writing (both in class and in Assignment 2), have ranged from basic (just listing certain data from a table) to intermediate queries where we restrict the data using conditions or parameters. For example, try creating the following queries in your new Police Fines database:

• Basic: List the driver, fine, location, reporting officer and date for all fines in the database.
• Intermediate: Limit the above query by only returning incidents that have occured since 1/4/05
• Something new: Modify your query so that it only returns incidents from the last 30 days (you will need to use the Now() function!)

So far so good? Our next step will be finding out how we can use the Totals function in our query design. You can access Totals by clicking on the little summation sign in the toolbar when designing your query. We'll have a bit of a demonstration of this in class, before having a go (together) at designing the following queries:

1. A count of the number of fines recorded in each location (qryFinesByLocation).
2. A count of the number of fines recorded by each officer (qryFinesByOfficer).
3. The maximum single fine (dollar value) reported by each officer (qryMaxFinesByOfficer)
   
4. A total of the fines reported by each officer (we always knew this was a money making scheme right ;-) (qrySumFinesByOfficer)
5. A total of the fines reported by each officer since 1/4/05 (qrySumFinesApril)
   
6. A total of the fines reported by each officer in the last 30 days (qrySumFines30days).
7. (V. Hard) A list of officers who haven't reported any fines since 1/4/05 (qryNoFinesApril).
8. A list of officers who haven't reported any fines in the last 30 days!

Phew!! If you can do those in Access, you're now an Access power user... you can pull useful information out of any database!

Database Normalisation

You're currently employed as an IT support person at the local police station, and you nearly fall over when you are asked to do some data entry, only to find the following "database" being used to record traffic fines!?

DriversLicenseID	FineType	IncidentLocation	DriverName	FineAmount	Reporting officer	Date
26545LD	Speeding (-15)	Springwood	Harry Potter	$350	Jenny Rainer	12/5/05
65475TN	RedLight	Katoomba	Bob Smith	$200	Jeff Skillset	15/5/05
75163LD	Speeding (15+)	Lawson	Rachel Rogers	$350	Nadia Smith	20/5/05

What's wrong with this database? Can you see how it might be possible to enter inconsistent information?

Over the past few weeks, we've been practising the task of improving database tables by breaking them down into smaller tables with relationships. The technical term for the process that we've been practising is Normalisation. It's probably a good time now to find out a bit more about Normalisation before we tackle our Police Fines database. After walking around a bit, take 10mins to read this useful Microsoft Knowledgebase article: Database Normalization Basics. We'll go through the example there together, discovering 1st, 2nd and 3rd normal form.

We'll see if we can Normalize the database to come up with a much more efficient (and consistent!) design!

Excel practise... If and While!

If you're keen to go over how to use IF ELSEIF END IF statements in VBA, there's a great tutorial with practical examples on Conditional Statements at FunctionX. We're going to focus our attention for the moment on looping in VBA with the While loop.

Try creating a new workbook, add a new user form with a few buttons (btnWhile1, btnWhile2). Put the following code behind one of your buttons (with the button name "Count to 10"):

'We're going to make up a variable to count through our while-loop:
Dim count as Integer
count = 0

While count < 10
MsgBox("Count is :" & count)
count = count + 1
Wend

Run your code by clicking on your button and make sure it does what you expect it to do! Now, have a go at the following:

1. Call your second button "Even Numbers" and see if you can add a similar function that only counts even numbers (Hint: start with the exact code above).
2. Call a third button "Odd Numbers" and see if you can create a similar function that counts the odd numbers less than 10.
3. Modify both your buttons so that they only count numbers up to 8. Check that they work correctly.
4. Add a new button called Colourise. When you've got lots of information in a worksheet, it's sometimes really helpful to use an off-white (or pastel) colour for every second row (it helps to differentiate the rows of information). Using the Cells function (eg: Cells(count,1) ), see if you can create code that colours every second cell up to row 100 with an off-white colour of your choice!
5. Modify your Colourise button so that it colours every second Row instead of just the one cell.

Remember, the power of loops is that you can repeat any simple action as many times as you want!

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!? We'll be learning a bit about commandment #1 tomorrow: Thou shalt design normalized tables!

Petrol prices competition...

OK, time to practise and build our Excel skills... we'll start with a raw text file with data. Your task is to import the data into Excel and create a Graph plotting the price of petrol for each day (yes, all 200 of them!)

Improving our Cinema movies database

Yahoo! Australia has implemented our design, and it works efficiently. However, users have requested that the website also displays the price for which they can see the movie at each cinema.

We've been asked to update the design of our database so that it can also store the price (Adult, Child, Conc.) to see the movie at a certain cinema as well as the movies rating. One of the things we'll be doing tomorrow is having a go at updating our design for our Movies database to allow the price and rating to be stored... and it's not as simple as it sounds!

Some Mindless fun with Excel!

Mark Thorpe has an excellent (and fun!) tutorial that will help us understand a bit more about Excel VBA (especially Events and Objects!).

It's great! (well, I had fun going over it to see if it was worthwhile!) If you enjoy this tutorial, why not let him know! I love finding fun ways to learn!

The power of pivot charts'n'tables...

... but what is a pivot chart or a pivot table? Why would we even want to use one?

See if you can find out, then add a comment back here with a link to the page you found (don't worry Lynne, we'll not spend more than 5-8 mins searching :-)). After finding out a bit about them, we're going to download an example file from MS and create a few Pivot Charts to see why they might be useful.

A database for Yahoo Movies

Do you ever find that, when you want to see a specific movie, you have to check all the cinema ads to find where it is playing? This isn't such a problem for the block-busters, but for smaller arty films it can be quite difficult to find out where you can see a film!

Imagine that we're being paid big bucks by Yahoo! Australia to design a database system that will record, for each current movie release, all the cinemas where the movie is showing!

The information that the database needs to contain is pretty simple:

• The movie name.
• For each movie, a number of cinemas where the movie is showing.

but you might find that there are other useful fields that you want to include.

We might try to divide into two groups and see what kind of design we come up with, and hopefully in the process we'll discover what database Normalization is all about.

Once we've got a design, we'll have a go at implementing our database in Access - learning how we can create simple forms with dropdown menus whereever necessary!

BTW: Remember to bring your 2nd Assignments along on Friday as we'll have an hour or so to work on them (or your third assignment if you've finished the second).

Bill Gates giving away money!

Have you ever received this email?

Subject: FW: PLEEEEEASE READ!!!! It was on the news!

Dear friends,
Something to share with all of u. Would u believe if this is true? Read on..... For those who need money badly and this is one opportunity to try it! I'm an attorney, and I know the law. This thing is for real. Rest assured AOL and Intel will follow through with their promises for fear of facing a multimillion-dollar class action suit similar to the one filed by PepsiCo against General Electric not too long ago.

Dear Friends,
Please do not take this for a junk letter. Bill Gates is sharing his fortune. If you ignore this you will repent later. Microsoft and AOL are now the largest Internet companies and in an effort to make sure that Internet Explorer remains the most widely used program, Microsoft and AOL are running an e-mail beta test. When you forward this e-mail to friends, Microsoft can and will track it (if you are a Microsoft Windows user) for a two week time period. For every person that you forward this e-mail to, Microsoft will pay you $245.00, for every person that you sent it to that forwards it on, Microsoft will pay you $243.00 and for every third person that receives it, you will be paid $241.00. Within two weeks, Microsoft will contact you for your address and then send you a cheque.
Regards.
Charles S. Bailey
General Manager Field Operations
[CONTACT DETAILS REMOVED]

I thought this was a scam myself, but two weeks after receiving this e-mail and forwarding it on, Microsoft contacted me for my address and within days, I received a cheque for US$24,800.00. You need to respond before the beta testing is over. If anyone can afford this Bill Gates is the man. It's all marketing expense to him. Please forward this to as many people as possible. You are bound to get at least US$10,000.00.

We're not going to help them out with their e-mail beta test without getting a little something for our time. My brother's girlfriend got in on this a few months ago. When I went to visit him for the Baylor/UT game. She showed me her check. It was for the sum of $4,324.44 and was stamped "Paid In Full".

Like I said before, I know the law, and this is for real Intel and AOL are now discussing a merger which would make them the largest Internet company and in an effort make sure that AOL remains the most widely used program, Intel and AOL are running an e-mail beta test.

What do you make of it? We'll talk about this is class... and see what we can find out about it! (But please, don't forward it on!)

Creating VBA Macros in Word

As well as handing in our first assignments tomorrow, we're going to find out how easy it is to create useful VBA Macros in MS Word! In fact, there's very little difference from what we've been doing with Excel!

We'll see how to use all our old and familiar friends, such as good 'ole MsgBox and, who could forget IF (and fellow mates THEN and END IF), in MS Word

Bring along your work for Assignment 2 as well, as we might have some time to work on it/ask questions in class.

See you then!