Setting up a database in microsoft access.

Project 1b Implementation Charlotte Body 11J

30/04/2007 Mr. Webster

Implementation

Setting up the Structure – To set up the database I went into Microsoft Access and clicked on ‘Blank Database’ then ‘OK’. A window then appeared called ‘File New Database.’ I typed in the name that I wanted to call my database, which was – ‘amandarestellacademy’. I then selected ‘Create’. A window then appeared with the name of the database, with many tabs on it. I selected the ‘Table’ index and then clicked ‘New’. I then selected ‘Design View’ and ‘OK’.

I then entered in my information (field titles) in the structure table that appeared. I changed the field properties according to my design. I had to change properties such as – field size, format, decimal places. As evidence of this I have done screen prints. I made sure that I saved my work regularly throughout.

Data Capture Form – I had to collect all of the information I needed to put in this database with a sensible method. For this I created a data capture form. This consisted of, going into Microsoft Word and creating a table by selecting ‘Table’, ‘Insert Table’ and choosing how many columns and rows I needed. I needed 12 columns and 31 rows. In the column headings I typed what my field headings would be. In the first column I numbered the rows. This would be the number of people.

I printed off 2 copies of this data capture form. One I kept blank so you could see how I designed and created it, and the other I had to fill in. I collected data from two sources. I firstly collected data from the filing cabinet at my dance academy, and I secondly collected data from a diary in which Amanda Restell’s mum had copied down in a hurry the information of a pupil that wanted to join, while she was on the phone. I have scanned in these sources as evidence.

My data capture form will contain the fields which will give the answers I need to my searches.

Testing Validation – I created a validation test plan. This made sure that all my properties of fields were correct and that all the data I was going to type into my database was valid and it reduced the chance of me making a mistake.

This test plan had column headings of:

I had to complete the last two columns with evidence of my results. This evidence was shown by using screen prints. I only typed in the first 10 records of my information. This was because it was only a test plan not a test on all of my information collected. I purposefully made mistakes when typing in my information to see if my validation rules worked. I made an empty validation test plan sheet so that you could see what my column titles were and what the table looked like before ...

This is a preview of the whole essay

This test plan had column headings of:

Testing Queries – I created test queries so that I could be certain that it worked, and my results for these queries would be correct.

I only used the first ten records when testing my queries as it was just a practice.

I created practice queries by returning to the window with the name of my database and clicking the ‘Queries’ tab, then ‘New’. I then selected ‘Design View’ and ‘OK’. This then produced a window that was headed ‘Show Table’. I highlighted the name of my database and selected ‘Add’. This then gave me a query window. I added the field names into the ‘field’ row.

I then used my practice queries to type in the ‘Criteria’ and ‘or’ rows.

For an ‘AND’ search I typed in the information all in the ‘Criteria’ column. For an ‘OR’ search I typed in one piece of information in the ‘Criteria’ row and the other piece of information in the ‘or’ row. I also created a ‘NOT’ query, in which case I just typed ‘Not’ in from on the piece of information I was searching for (so this piece of information would not appear in my results).

I predicted my results for each query and recorded my actual results. I also wrote a conclusion saying if the query was successful or not and why.

As evidence of these queries I did screen prints of my results.

Data Entry – After I had completed testing all of my queries I had to enter all of the information I had collected. I typed in all 30 records and my validation rules helped to make sure I did not type any errors.

After I had entered all of my information I had to verify it because although validation rules prevent people from making mistakes it cannot cur out the change completely that someone will not make a mistake when entering data e.g. someone’s name could be typed in wrong, and the computer cannot know that it is a spelling error.

I printed the database with all of my information typed out and highlighted all of my mistakes, after I verified it. I then changed these mistakes and printed a final copy with no mistakes.

Producing Tables - To produce a table I firstly had to select a query that I was going to use for the results to show up in a table. To do this I clicked on the ‘Query’ tab and then selected a query that I wanted. I then clicked ‘Open’ and my query appeared in design view. To display the results from this query I had to click the ‘data sheet view’ button in the top left of the screen. In the design view I could also chose which results in which fields I wanted. In the ‘Show’ row I could tick and untick which fields I wanted to show or not to show. The fields that I did tick were displayed in the table in the datasheet view, and the ones I did not tick, did not appear in the table.

Producing Reports - To create a report I had to go into the ‘Report’ tab and click ‘New’. I think had to select ‘Report Wizard’ and from the drop down box at the bottom right I had to select the query that I wanted to show as a report. I then clicked ‘Ok’. I had to move all of the fields that I wanted to be displayed in the report from the ‘available fields’ column to the ‘selected fields’ column, using the single arrow in the centre of the two columns. It then asked if I wanted any grouping levels and I said no, and it then asked what sort order I wanted but I just clicked ‘next’ as this did not apply to my record. I could then choose the layout that I wanted my report to be in. I could chose from ‘columnar’, ‘tabular’ or ‘justified’. I could also chose the page orientation that I wanted – ‘landscape’ or ‘portrait’. I could then chose the style I wanted for my report e.g. ‘bold’, ‘casual’, ‘compact’ etc. After I chose my style I had to give my report a title e.g. ‘Query 5’. I could then click ‘Finish’ and my report would be complete.

Producing Records - To create records I had to go into the ‘Form’ tab, and click ‘New’. I then selected ‘Form Wizard’ and from the small drop down box in the bottom right I selected the whole database – ‘Amanda Restell’. I then clicked ‘Ok’. I then moved all the fields that I wanted over from the ‘available fields’ to the ‘selected fields’ column by using the single arrow in between the two columns. Or to transfer all the fields all at once I clicked the double arrow button and it moved them all automatically. I then clicked ‘Next’. I could then chose the type of layout that I wanted in my record e.g. ‘columnar’, ‘tabular’ or ‘justified’. After clicking next I could chose the style I wanted e.g. clouds, colourful, colourful 2, dusk etc. I then clicked ‘Next’ where I could now title my record e.g. ‘Record 2’ and then I clicked finish and my report was complete. I could see the design form of my record in design view. I could see what the actual record looked like in form view.

To design the front welcome page of my database I went into the ‘Form’ tab, then selected ‘Design View’. This then set me up with a clear record that I could design how ever I wanted.

Editing Records - This was quite simple to do. I opened my database and searched through the records until I found the one I wanted to edit. I then highlighted the information that needed changing, I retyped the correct information and then saved my database with the new information inserted.

Producing Buttons - To create buttons I went into the ‘design view’ of the record I wanted to add the button to. I then used the ‘draw button’ icon to draw a button on the page. I then selected ‘Record Navigation’ out of the categories. I then chose what the button would do when I clicked it, I could make the button ‘got to next’, ‘previous’, ‘last’, ‘first’ record depending on which one I selected in the ‘Actions’ section. I then clicked ‘Next’ where I could then name the button if I wanted to, and then I clicked ‘Finish’. The button was ready to use in ‘Form View’.

If I did not want to just go to the next record etc, but go from my welcome page to the database where all the records were I created the button slightly differently. I went into the design view of my welcome page record and I drew a button with the ‘Draw button’ tool. I then selected the ‘Miscellaneous’ from the ‘Categories’ and then I chose to ‘Run Macro’. I could then chose which macro I wanted to run. I could chose to go to ‘Next’, ‘Previous’, ‘Last’ or ‘First’ record as I could do with the ‘Record Navigation’.

Producing Macros - To create a Macro I went into the ‘Macro’ tab and clicked ‘New’. Under the ‘Action’ section I chose what action I wanted to take place in this macro. I chose ‘Open Form’ and then saved this because I wanted to use this for the button to either open the whole database as records or return back to the welcome page. I then chose ‘Go to Record’ in action arguments underneath. I then selected whether I wanted to go to ‘Next’, ‘Previous’, ‘First’ or ‘Last’ record. I had to use a Macro for doing this is the end because the ‘Record Navigations’ did not work.. I could then use this macro when creating my buttons to go from one record to the next and to refer back to the welcome page.

Producing Graphs - To create a graph I went into the ‘Report’ tab, and clicked ‘New’, and then selected ‘Chart Wizard’. I then selected the table from where the data I wanted the graph to be created from. In ‘Chart Wizard’ I selected the field from the database that I wanted to draw the graph on by using the single arrow. I then clicked ‘Next’. I could then chose a suitable type of graph for example, column graph, bar graph, pie chart, scatter graph, line graph etc. After decided this I could then look at the layout of my graph to make sure that it looked ok. I then clicked ‘Next’. I gave the graph a title and chose whether it had a legend or a key. I had then finished my graph. I could not see my actual graph in design view, only a model template appears and the ‘Headers and Footers’ section.

Producing Queries - To create a query I went into the ‘Query’ tab and clicked on ‘New’, I then selected ‘Design View’ and clicked ‘OK’. I then had to ‘Add’ a table of the whole database. I then moved all of the field titles from the small added table into the field headings underneath. I could then type in my criteria in the different fields to get results to my queries.