Youth Club Database Solution

  This is the cell I used to Freeze Panes.

This next screen dump shows the use of Pane Freezing where the names and Accounts area appear together:

Data Validation

You will see on the screen dump one before the previous an arrow has appeared next to the cell.  This is because this cell and the whole Field have been validated.  This is how it works.  First you click on Data, Validation and you will bring up this dialog box:

Alignment and Format

I have used lots of different alignments and font settings in my system as presentational devices to make it look and appear professional.  For all the Row headers I have used a central alignment, put them in Bold type, and also vertical text that reads up, so a large amount of text can be fitted in a small Field horizontally apart from the Date and meeting type which have been put horizontally.  This screen dump shows all the Row headers:

For the first Field, I have put a right alignment on so the Fields finish neatly.  From then on, all the text and character Fields in the Database section have a left alignment, and all the numeric and currency Fields in the Accounts section have a right alignment.  Some of the Fields are tight around the text, so whatever is in it fills the cell, to give it a neat look.  Where you see a £ sign, indicating currency, the cells have been formatted as ‘Currency’.  As a neat finishing touch I have put black outlining borders under the first Row and vertically throughout the accounts section where data is being entered and it is easy to lose track of where you are.  The outlines help with this a lot.

Accounts Section

We now get on to the Accounts section where there are lots of formulas that need to be explained.  We start with quite a complicated on in Column K.  This reads:

=COUNTIF($O$2:$IV$2,"*")

This means that the cell will count ‘1’ for every “*” that appears in cell range O2 to IV2.  The “*” is a wildcard meaning that it will count ‘1’ for every cell in the range that contains text.  It is counting all the dates that are in the second Row.  The “$” signs before each part of the cell reference means an absolute reference, so wherever you copy the cell to, the cell references will stay the same.  If you don’t put a “$” sign around the references, they will change depending on where you copy it to.  For example, if you copy a formula containing the cell reference O2 down one row, it will become O3, down two cells it will become O4 and so on; if you copy it across one Field, the same reference will become P2, across two Fields it will become Q2 and so on.

The next formula is in Column L, and is another COUNTIF formula:

=COUNTIF(O#:IV#,"1")

 This is very similar to the previous formula, and is used to count all the times a member attends the Youth Club.  A one is entered into the ‘Attd’ Field for each week, and this formula will count all the “1’s” appearing in the cell range O# to IV#.  So at the end of the term or year, the User can see how many times each member has attended the Youth Club.  I have used # symbols to signify that the formula is different for every member, as in this case, the formula will count everybody’s personal attendance records.

The next formula, in Column M, uses Columns K and L to work out the Percentage Attendance for each member.  The formula reads:

=L#/K#*100

This is a simple way of working out a percentage; using the total number of times the member could have attended the Youth Club and how many times they have attended the Youth Club, in the previous two Fields.  In this Field I have used a tool called conditional formatting and this is how it works:

The next column containing a formula in Column N, which adds up all the money which a member owes.  It reads:

=Q#+T#+W#+Z#+AC#+AF#+AI#+AL# etc…

Unfortunately, there is no quick way of doing this as both the ‘Paid’ and ‘Owe’ Fields contain values so it’s difficult to do it any other way except adding up every third cell.

The final formula appears firstly in Column Q, and all of the ‘Owe’ Fields, and is a formula for working out how much a member owes to the Youth Club.  It reads:

=IF(O# =1,0.4-P#,0)

This formula is saying that if O#, which is the ‘Attd.’ Field has a ‘1’ in it, then subtract the amount of money in P#, which is the ‘Paid’ Field from 40p (the total amount of money to be paid for each week), otherwise leave blank.  You can see that if a member pays 40p, this is entered into the ‘Paid’ Field, and the ‘Owe’ Field will show they owe nothing, and if a member does not attend a particular week, a ‘1’ will not be entered in the ‘Attd.’ Field, so nothing will appear in the ‘Owe’ Field.

The totals columns also contains some formulas, most of which are simple Sum formulas:

=SUM(#4:#79)

This formula simply adds up all the values in the range #4 to #79, the # symbol being a wildcard for the columns, and the same formula appears in the columns L and N to W.

There is a different formula that appears in Column M, and is for working out the average attendance of all the Youth Club members throughout the term or year.  It reads:

=(AVERAGE(M4:M79))/100

This formula works out the average in the range M4 to M79 and then is divided by 100 to turn it into a percentage.

Front Ends and Vlookup

The first sheet pf my system is called a Front End, and is a neat way of presenting a database, and also good for inexperienced Users of the program to be able to use the database, and get to grips with more complicated aspects of the database.  These next screen dumps show how I set the Front End up and how it works

The first stage is to bring the Forms toolbar and draw a drop down menu box:

Then you need to specially format the drop-down menu:

The next stage is to insert the formula into each of the cells you want to use, and these link back to the drop-down menu you have created and the database.

This is the final result, a very professional looking Front End:

 

 Macros

These are very simple to create and very easy to use.  I have put four simple macros in the Front End for the User to use, and the User Guide explains for the User how to create new ones.  The first step is to select a button from the forms toolbar:

A dialog box then automatically appears giving you the chance to assign a macro or create a new one.  To record a new one, click on the record button:

From there you can rename the macro, as long as the name contains no spaces, or assign a shortcut key, with Ctrl + (another key).  From there you click ‘OK’, and you are ready to start recording.  The computer will then record anything you do, so for example you could sort or search the database and then create a graph.  Once you have finished, you click the stop button, and anything you have done will be stored in the memory in the button.

You can rename the button by right-clicking on it and selecting the text and the macro is ready to use.