User:Sarahfitzsimons123/sandbox

Validation If you need to validate your database don’t forget the 2 parts •	The rule •	The error message (lots of students lose marks by forgetting this easy part!) The rule is something that must equate to TRUE to be accepted. Examples: >0	Data must be more than 0 <=12	Data must be less than or equal to 12 <>100	Data must be any number that is not 100 >12 and <46	Data must be a number between, but not including, 12 and 46 >=1/8/2010 and <=31/8/2010	data must be a date during August 2010 Like “P*”	data must start with a P Like “T??”	data must be 3 letters and start with a T Like “*fan*”	data must contain the phrase “fan” in it somewhere “Apples” or “Oranges”	data must be Apples or Oranges
 * Note: the only wildcards allowed are * and ? (Don’t get confused with input masks)**
 * Note2: you only use speech marks when the data is text. This is wrong “>3” unless you want people to enter >3 into that field!!**

Reports In order to create a report we must first make a query Rule #1 – only add the tables you need to a query. DO NOT add all tables by default

For simple queries here are a few rules to follow: •	Only add the fields that you need, you can add more and delete them later if need be •	Every time you make a change run the query to see the result of your change •	Use criteria to filter your data •	If you need to use criteria and NOT include that column uncheck the ‘show’ box •	If you have MULTIPLE criteria on one limit that is an AND search – all criteria must be true to be included in the results •	If you place criteria on separate lines you can perform an OR search All the criteria that we could use in validation (above) are also valid in queries. If the data matches the rule it is included in the results. If it doesn’t match the rule it is excluded from the results. LEARN THE CRITERIA OPTIONS Often you will need to create a CALCULATED FIELD (that calculates at ‘run time’). These take the format: FIELD NAME: calculation (don’t forget the colon!)

The computer will put the [] brackets in for you around the field names EXCEPT if filed names have spaces in them. Then YOU will have to put the brackets in E.g. Days: [Return Flight]-[Home Flight] (you MUST put the brackets in because of the spaces in names) If you see the term SUMMARY in the question, then you are likely to have to make a summary report. This can be done through the totals button.

Tips for summary reports: -	Run your query without the totals/grouping turned off first. Which fields contain REPEATED data that needs grouping? -	Which fields need choosing for average/sum? -	If you need to COUNT then you can choose any field and select count (I usually pick the primary key) -	If you need to filter the results but not include that field then choose WHERE

Don’t worry about sorting in the query- you will do this in the report. You may have to sort if you are exporting this to excel to graph the results. Once you are happy that you have the right fields and the right filter criteria then you can CREATE REPORT.

Report Generation Once we have the query correct we can create the report. You must SAVE the query first (use the question number). RUN the query and choose CREATE REPORT from the menu ribbon. You will have a basic report. These are your first steps: •	Set your page to landscape or portrait (as per question) •	Change the widths of the fields so that they all fit on one page wide •	Change the format of the fields if necessary (e.g to currency) •	Change the labels if they do not read English or make sense •	Add the title supplied or your own if not specified •	Add your name to the header or footer as specified Additionally, you may be required to: •	Group the data. This groups records that are similar together. It allows you to add subtotals to each group •	Sort the data. This allows you to sort data into order. Ascending means the way we normally think (A-Z, 1-100, Monday to Friday) Descending is the opposite •	Add totals/subtotals. In LAYOUT VIEW select the column you would like the total on and choose the TOTALS button from the menu ribbon