wildcare. By orla bradfield.
Today I started my database. From my plan I gathered that I was on target timing for starting my database. The database’s purpose is to detailed information of endangered species so that members can download and gather information to use for deciding on which animal to adopt.
I started my database by opening up a new database, and going to design view.
I then found all the field names from the DIDA wildcare website inserted them into my database fields.
Most of the data types for my fields were ‘text’ but a few, eg. Adoption cost, were currency this is because the data inserted for that is a price, therefore needs to be recognised as currency. I had to change the currency to 2 decimal places at the bottom, because that is the most they can enter.
Three of my fields had to have a validation rule these fields were status, threat and group.
For Status I inserted Red or Orange or Blue in the validation rule box this was also to categorised the status of the animal. I again gathered this information from the DIDA wildcare website on the ‘list’. Red stands for critically endangered , so a critically endangered animal would have a stats of red. Orange stands for endangered and blue stands for at risk.
In the validation rule box for threat I inserted the following data which I got from the DIDA wildcare website.
I did this so that the threat can be categorised and read easier for the member. C stands for climate change (showing that the threat for this animal being endangered is due to the climate of the animals habitat) L stands for loss of habitat (for example ; tree cutting, bush fires etc) H is for hunting (animal hunted) and P is for predator (this is where there has been a rise in the amount of that animals predator therefore the animal is being killed for example in the Masai Mara in Kenya Africa, if the number of lions really increased the number of wildebeests would decrease because wildebeests are one of a many types of a lions prey.
For Group icategorised the animals into there groups where I inserted Bird/Insect, Mammal, Fish or Reptile/Amphibian into the Validation rule box.
In todays lesson I carried on with my database I checked that I had so far completed my database correctly before carrying on. I also checked that I was up to date and in time with my plan, which I was.
The next step in my database was to import my data. Here is how I did it.
1 )I first went to external data on my main options .
2) I then clicked on text file because the data I needed to import from the DIDA wildcare website was a text file.
3) Once this came up, I browsed for my text file which I had copied and pasted into ‘Notes’ and saved in my area. I then selected append a copy of the records to the table before I clicked OK. So that my database could be imported correctly. More boxes came up so once I had made sure everything was correct I clicked on finish!
I then went to open my table found that all the data was correctly imported! I could tell that it was correctly imported and my validation rules were correct because I could see that all the different types of animals where correctly categorised into there groups, into status and into there threats.
For instance ‘Alligator, Chinese’’ the first animal which is highlighted black, is correctly categorised into ‘Reptile Amphibian’. Its status is ‘red’ which means its critically endangered and its threat is ‘H’ which means it’s a animal which is hunted, I would imagine for its skin.
The members can tell what the symbols for status and threat mean once they look at the data entry form key, which I am yet to have completed.
My next task was to create the data entry form for three new animals that I have to put into my database. I started by going to ‘Create’ ( on the main options box) and then clicking on ‘form’.
Once I clicked on formed the following appeared on my screen.
I had to turn this table into my own data entry form for the three species which I am going to insert myself.
I started by adding a new command button that says ‘add new record’. To do this I had to go to design view. I clicked on the button that brings you to command wizard. The command button wizard came up,It automatically has a default setting of saying ‘Record Navigation’ for the different actions available for each category. I changed the category to ‘Record Operations’ instead and I also changed the action to ‘Add new record’ because I needed a button that is able to instantly add a new record. After clicking next I named the button ‘add new record’ to make sure that the use of the button was clear.
I downloaded the data from the DIDA wildcare website. I found suitable membership fees for the customers.
Cell formatting involves changing the format of the cells to suit the content. For my spread sheet I had to change number to currency because the data in my spread sheet is based on the costs in pounds, therefore its currency. I did this because it makes the spread sheet easier to use for the customers, because they will be able to differentiate between the different types of data. As well as this I can now control the number of decimal points and places, this enables me to control my data accurately.
Once I changed it to currency the ‘30.00’ turned into ‘£30.00’
This allows me to insert/copy cells from one spread sheet to another. It’s a simple and fast technique. It saves time and it also automatically updates, so that if I change my data on one spread sheet both sets of data change instead of having to do it twice.
On my spread sheet I had two spread sheets ‘costs’ and ‘cash flow 2013’. On my costs spread sheet I had all the costs . As shown in image one. My cash flow 2013 spread sheet is shown in image 2.
I copied the information from the costs spread sheet into the cash flow 2013 spread sheet so that I could calculate totals and carry on making my cash flow 2013 spread sheet.
I used 3 different formulas one to multiply, one to add my results and one to subtract my results. For my multiply formula (image 1) I had to multiply the number results by the cost results. For instance my number ‘2450’ was in the field B5 and my cost was £30.00 and was in the field C5 so in the formulae bar I typed in =B5*C5 which multiplies the two results together, it came to the total £73,500.00 because I had previously changed the data type to currency.
This leads me on to my next formulae =SUM(__:__) I used this formulae for adding up my results to find more totals. For instance ,I wanted to find the total cost of all the membership fee’s ‘£349,750.00’ to find this out I had to add up each total for the different types of membership fee’s which was from field D5 to D9 (as shown in image 2) instead of counting from field to field I learnt I quick technique which was to write in the formulae bar =SUM( then highlight and drag from start field to my end field) As shown again in image 2. Although I didn’t really need to do this for this example I practiced it here so that when it came to much bigger amounts of data it would take 2 seconds instead of 2 minutes. I used this adding formulae on the rest of my spread sheet to find out all of my totals.
I did the same with all the numbers and costs (light blue fields) to get my total cost for each type of memberships.
My final method of formulae was subtracting. The only place I used this on my database was in ‘Net Cash flow’. The formulae was simple and easy to use. The net cash flow was the total income takeaway the total outcome. So basically the total money gained from the membership costs but taking away the total money spent on things like food and veterinary costs. For instance the net cash flow in January is =B18-B28, shown in image 3. I did this for all the net cash flows in all the months.
January is the only month which has ‘SURPLUS’ as a cash flow because there was more total income than total outgoings. All of the other months are in ‘DEFCIT’ because there was more total outgoings than total income, so there was less money gained from adoption fee’s than spent on things like utilities, food and salaries .
Absolute Cell referencing is fixing the cell to its position so that it does not change meaning that when it’s copied and pasted to other cells the cell references do not change.
I did this using the formulae ‘=Costs!$_$_’, for example.
I used the IF statement because its an conditional statement and shows my results as either ‘SURPLUS’ or ‘DEFICIT’ for the Cash flow.
The formulae I used was ‘=IF(_>_, “SURPLUS”, “DEFICIT”) For example I typed in B18 to(or >) B28 because I wanted to find out whether or not the total cash flow for the total outgoings was higher (DEFICIT) or lower (SURPLUS) than the total income. I did this for each month.
For conditional formatting I set a rule so that if the cell was in ‘SURPLUS’ it would turn green and if the cell was in ‘DEFICIT’ it would turn red. I did this so that my spread sheet would visually be much clearer to the user.
Goal seek is used to get a certain result when unsure of the original starting value. For instance if I were to use it to get my total outgoings to £0.00 I would have to do this by changing another cell, (shown in image 1) by doing this I would get
an odd membership fee value i.e. ‘£30.00, £25.00,£20.00 then £43.24’, because of this I would not use it in my spread sheet.
I need to test that spread sheet was working and accurate so I tested it using my calculator.
I wanted to try and get my starting cash flow and total ending cash flow to be in ‘SURPLUS’ I did this by changing my Membership fee’s to try and get as closest to ‘SURPLUS’ or £0.00 as possible.Membership fees
The original cost for the ‘Wildcare teens’ and the ‘Wildcare kids’ membership fee’s were £0.00. I changed the teens to £15.00 and the kids to £10.00 to try and decrease the ‘DEFICIT’. (Shown in image 1).
This decreased the ‘DEFICIT’ from -£64,854.00 to -£27,354.00. This was not a massive decrease so I wanted to test out more membership fee costs.
I increased some of the membership fee’s because it wouldn’t make sense if two fee’s were the same cost, for instance if I were to increase my teen membership fee anymore it would be the same price as a silver membership fee. So I increased all the membership fee’s by £5.00.(show in image 2).
This worked! The cash flow is now £61,896.00 which is a ‘SURPLUS’ But the final cash flow is too high due to the fact that it’s a charity and doesn’t necessarily need a lot of profit.
Renewal Date : / /
I need to make a suitable membership card to give to the members. I did some research to get some ideas of how to lay out my card.
Company Logo, Cleary displayed
Location, contact information
Terms and conditions, Cleary stated
Out of the two membership cards the ‘Stevie B’s’ gym and martial arts academy membership card (1) is the better as it includes all the suitable information and clear space to fill out the members name and renewal date.
Doesn’t have a renewal date although I will be including one on my membership card