Thursday, 10 January 2019

Microsoft Access Queries – Calculated Fields

Calculated fields 


There are going to be times when calculations need to be done in Access.  They are done in a very similar way to Excel, the main difference being that rather than refering to cell references field names are used instead.


To work out the stock value, you need to multiply the units in stock by the unit price.  This information needs to be displayed in the next column and you need to provide a column heading.  To set this up a formula needs to be entered in the the next blank field.

The entry needs to be :

Stock Value:[unit price]*[units in stock] 

Stock Value is the alias – what that field will then be known as.

[unit price]*[units in stock] is the formula.  Notice that it does not need to start with an equals sign as it would in Excel.  The individual field names need to be enclosed in square brackets. If there were no spaces in the field names you would not need to type in the square brackets they would be inserted automatically.

The formula can be entered directly into the field name.  Alternatively you can zoom in on the field by clicking on Shift f2.

The formulae that are entered can be as complex as you wish them to be.  The structure of the functions that can be used do not have to be learnt, the expression builder can be used to to see the functions available and the structure of each layout.  Save the query before selecting the expression builder

The Expression Builder can be activated by  CTRL-F2.  Then the selection needs to be made from the first column depending on what is being worked upon.




When calculations are to be entered then functions need to be selected in the first column.  This displays the different type of functions in the middle column.  By selecting the category required then the functions are displayed in the third column.

By clicking on a function in the third column, the format of the function is displayed at the bottom of the window.  By double clicking on the function, the function is inserted into the formula at the cursor position.

Totals in Queries 


A new feature of Access 2007 is the facility of adding a totals row on dynasets, allowing more Excel like features.   This is done in the datasheet view by clicking on the totals button in the records group of the Home ribbon.

This displays a totals row at the bottom of the window.  When you click on the cell under particular fields the drop down options will be dependent on the type of data in the field.  On text fields the two options are none or count.  On numerical data there are more statistical functions to choose from.






















Wednesday, 9 January 2019

Excel Introduction Public Courses

Microsoft Access Queries – Creating a Query in Design View

Creating a query in design view



1. Click on the Design view button in the Others group of the Create Ribbon.

2. From the Show Table select the table or query that the query is based on. If necessary click on the tab required. More than one table can be added – depending on the complexity of the query to be set up.

3. Click on Close – this then takes you into seeing the query in design view.


Microsoft Access Queries – Creating Select Queries

Creating Select Queries 


The select query is the default query point.  If other types of queries are required they normally start off as a select query.

The different stages of setting up a select query are:

1. Select the fields required in the query.

2. Enter the criteria required on one or more fields.

3. Run the query.

The query can be generated manually in design view or by using the wizard.

Creating a query using the wizard


1. Select Table or Query (in the Navigation Pane) upon which the new query is to be based on.


2. Click on the Query Wizard button on the Create Button.



3. Select the Simple Query Wizard.




(Notice that the table selected in step 1 is listed under Table/Queries, if it is the incorrect one then it can be changed at this point).

4. Select the fields required by either double clicking on the necessary fields in the Available Fields column or by clicking on them and then using the single Arrow button to take them across to the Selected Fields column.

If all the fields are necessary then the double arrows will take across all of the fields.  If any unnecessary column is taken across, it can be removed by using the arrows point ing to the left.

5. On the next screen select detail (as apposed to summary – which we will look at later).

6. Then give the query a name and decide what you want to view.




Using the wizard in this way will give you a complete list of records from the table that you selected.  It will show you the fields that you selected and display them in the natural order of the table.
The wizard does not allow you to add a criteria or provide a sort order.

































Microsoft Access Queries – Join Types

Join Types 


If the query is based on more than one object there needs to be a relationship between the different objects other wise the result of the query will be meaningless.



This query shows that there is a relationship between the tables Products and Suppliers, so it will only show the supplier information for the products records that are there.  As the join is equal, if there are any products that do not have the supplier information filled in then it will not show those products.

As long as the data is complete in all the fields then the number to records returned by this type of query should equal the number of records in the larger table, but with incomplete data it could be less than  the number of records in the larger table.





If the join between the two tables is removed then the query will assume that every product in the products table is being supplied by every supplier in the suppliers table.  The resultant dynast than has a multiple number of records from two tables.



There are not many occasions where this combination is required and if this is done between two large tables, the query will run very slowly.  This dynaset was generated by tables of seventy seven and twenty nine records – imagine the size when the table sizes are in the tens of thousands!

Changing the Join Types 


When more than one table is added to a query and there are relations between the tables, if the join types have not been changed elsewhere then the default relationship is where the two sides are equal.
Depending on the data involved, the join types might need to be changed.



The join type can be changed by double clicking on the line that joins the two tables together.  This brings up the join properties.

There are three options

1. Only include rows where the joined fields from both tables are equal.

2. Include all records from Left Table and only those records from Right Table where the joined fields are equal.

3. Include All records from Right Table and only those records from Left Table where the joined fields are equal


The first option will show the products and their suppliers.  (Only for those products that have supplier information filled in.) 

The second option will show all the products with the corresponding supplier information where it has been filled in. 

The third option will show all of the suppliers with their products.  This could include Suppliers where they do not have an associated product. 




















Microsoft Access Queries – Queries Explained

Queries Explained 

Queries are used to analyse data in a database.  They can take many different forms depending on what they need to do.

The query does not work with a copy of the data – it deals with a dyna set, which comes from the expression dynamic subset.  The data can be selected against a criteria that is specified.  As the data in the tables change, then so can the answers to the query.

The query can also be used to display particular fields rather than all of the fields in a table.  The fields can come from one table, several tables, another query or a combination of tables and queries.

Microsoft Access Queries – Union Queries

Union Queries 


Select queries allow you to bring together information from multiple tables and queries and add information into the same records but they do not allow you to combine two individual queries together.

For instance if you wanted to produce a christmas card list for your customers and suppliers from the customers and suppliers table then the select query facilities will give you two different lists.  If those lists need to be combined then a Union query needs to be set up.

If you know SQL then the query can be written directly by changing the view of the query to SQL View.  Otherwise, the two queries can be created individually and then combined.

The customer query can be created.



The supplier query can be created



When the second query is being set up, it is very important that the fields are in an identical order and named the same as the fields in the first query.

 If the query is to be sorted in any way then the sorting has to be done in the second query only.
Once the two queries have been set up then their SQL code can be copied over into a third query.

Open the two queries in SQL view 

1. Create a new query, change the view in SQL

2. Copy the SQL statement from the first created query

3. Paste it into the new query

4. Remove the last semi colon

5. Type in the word union

6. Copy the SQL statement from the second created query

7. Paste it into the new query after the word union

8. (If a sort order was selected then remove any reference to the table in the sort order.)







The union query cannot be then viewed as a select query.

The first two queries are no longer needed and can be deleted, but should you need to fine tune the union query then it will need to be done in SQL.

(Otherwise, by keeping the original two queries, then the changes can be done to the originals and combined again!)






Excel Public Course 28th January + Hint 103

Tuesday, 8 January 2019

Microsoft Access Queries – Using Query Criteria

Using Query Criteria 


Criteria is needed when you don’t want to see the whole dataset.  Criteria will define which records are selected to be shown in the dynamic subset.

Criteria can be:

A simple match such as finding all the customers that are from a particular country . Whether certain data has been filled in or not.

A comparison such as finding all the products that cost more than a particular value

A formula can be used to select records such as finding all the orders where it took two weeks or more to deliver from the original order date.




To enter the criteria you need to be in the design view of the query. 

1. Select the fields that the criteria values are going to be applied to.

2. Enter the criteria values on.

3. Criteria rows.

When you enter the data on the criteria rows you need to consider whether they should be on the same row or different rows.  
If they are on the same row then both pieces of criteria apply.  In the above example it would find the category one products that cost more than twenty.   

If the “>20” had been on the next line down (the OR line),then the record must meet either one of criteria  

Then the criteria would have found all of the category one products whatever there price and all of the products that cost more than twenty whatever the category.  The reason for this is that the two pieces of criteria are not connected to each other. 

Examples of Criteria




Wild card characters can be used in the criteria when the data has to follow a particular pattern rather than a particular value.  Different characters match different values. 

The wildcard character “*” matches any combination of characters.
Like “B*” matches anything starting with the letter B. 

Like “*sauce” will find any product that finishes in Sauce. 

Like “*cheese*” will find any items that contains the word cheese. 

The wildcard “?” character matches a single character. 

“J??n” will match John and Joan but not Jon and Joanne. 

The wildcard character “#” matches a single digit.



























Microsoft Access Queries – Viewing a query in Design View

Viewing a query in Design View 


A query that is created by the wizard can then be changed as required by changing the view to design view and making the amendments required.

The different parts of the query are

At the top you have the table that the query is based on.

At the bottom you have the QBE grid – QBE stands for Query By Example.

This is where the query is defined.  The fields in the query are listed here on the field row. On the second row you have the table that the field is from.  (This is more important when the query is based on more than one table and or query).

Microsoft Access Reports – Address Labels

Address labels have a separate wizard that generates address labels


1. Select the table or query the data is coming from.


2. Click on the label button.


3. Select the label size my selecting the manufacturer and product number.

4. On the next step choose the font, size and colour.

5. Then design the prototype label by adding the fields required by using the single arrow in the middle.  The return key will move you to the next line and the spacebar is used for gaps in the fields if multiple fields are to be on one line.

6. On the next step choose the sort order.

7. On finishing you will see a preview of the address labels.


Microsoft Access Reports – Calculations in Reports

Calculations in reports 

Calculations can be added in reports in several ways.

One of the easiest ways of incorporating calculations in reports is to have the calculation set up in a query.  The advantage of using this method is that when the report is being set up it will be used in exactly the same way as the other fields and so no special set up is required. But calculations set up in queries do not always meet the needs of the calculation required.

When calculations need to be set up in a report then decisions need to be made as to where the calculation needs to be set up.  Is it in the:

• Detail section group

• The Group section (header or footer)

• The Page section (header or footer)

• The report section (header or footer)

If the section required is not visible then first it must be shown.



In the previous report where the customers were grouped by country the group footer is not shown, but by first showing more info then the footer section can be shown.

Adding a calculation control 


1. Add a text where the calculation needs to be displayed.

2. Type in the calculation in the text box starting with an equals sign.

3. If necessary change the text in the associated label, otherwise delete the label.

This produces the result. 

The depth of the gap between one section and the next is controlled by the depth of the section.  


 The formula does not just have to produce a numerical number.  Text and other fields can be concatenated (added together by using the ampersand) to make an expression. 


This produces: 



























MOS Microsoft Office Specialist MOS in Easy Steps









Exams Required to Become Office Specialist / Master (MOS).




Office Specialist / Master (MOS) Vouchers & Materials

Mouse Training we provide several purchasing options our two most popular are


1. Voucher include a 30 day Retake Voucher

2. Exam Study Packs - seven packs to collect each include - Exam Study Manual, Gmetrix Test Prep, Voucher + 30 day Retake Voucher ( vouchers valid one year ).

Purchase Exam Study Packs at a discounted rate £ 130.00

Exam Admin Fees : No admin fees when vouchers are purchased from  Mouse Training

 Office Specialist / Master (MOS) Training

Exam Testing Weekday :  Monday - Friday - ( 10:00 - 16:00)
Exam Testing Saturday :  ( 10:00 - 14:00) 


Mouse Training Provide a range of courses from one to three days tailored courses.

Any questions please call our team ...










Monday, 7 January 2019

Microsoft Access Forms – Editing a Form

Editing a Form 



Once a form has been generated,whether by using one of the standard buttons or the wizard, you might need to change the form in some way.  This can be done by editing the form in design view or layout view.

To have the form in design view or layout view, either right click on the form in the navigation pane and select design or layout view

or  

once a form is opened then select the design or layout view from the ribbon.

Selecting controls in Layout View 

To be able to change items on a form they must be selected.

1. To select an individual item click on an item, the orange border will then display around that item.

2. To select all the controls associated with the record click on the four headed arrow just above the first control.

3. To select the title of the form you need to click on the title button on the controls section of the format ribbon




Microsoft Access – Different Ways of Control Selection


Different Ways of Control Selection 

A single control can be selected by clicking on an individual item. 
Multiple items can be selected by clicking on the first one and then holding down the shift key while clicking on the other items required. 



Other ways of making multiple selections are:

1. Draw a rectangle through the items to be selected.

2. On the top ruler position the cursor so that vertically all the controls underneath are the items required.  If all of them cannot be selected from just one position, then again you can drag along the ruler.

3. On the left ruler position the cursor so that horizontally all the controls are the items required.  If all of them cannot be selected from just one position, then again you can drag along the ruler.


Microsoft Access – Create a form with Form Wizard

Create a form with form Wizard 



If none of the standard options of forms meet our requirements then a form can be created using a wizard where you can much more control of what fields are included and how they are laid out.
Select Form Wizard from the more forms button  This will get the Form wizard to start.  On the first screen the items are

1. Where you select the table or query the form is to be based on.  (more than table or query can be chosen by selecting the first one, choosing the fields and then selecting the next one).

2. Select the field required.

3. Add it to the form (can also be added by double clicking on the field name in the available fields column.

4. All the fields can be added by clicking on the double arrow.





Once the selections are made on this screen, click on next where you can select the presentation of the form.  The options are:



Columnar : The records are arranged in columns so that you see one record at a time.

Tabular : The records are arranged under column headings, can see multiple records per screen

Datasheet : Displays the data in the same way as it is in a table. (Will only display the fields selected).

Justified : Arranges the data across the screen under labels in multiple rows.  You will only see one    record at a time. 

Then you select the style required.  To see what the styles look like you can click on them one by one.   
It is advisable to use one style on the forms rather than using a different style for each form (though there is nothing stopping you form do so if you wish). 
Finally name the form and close the wizard, you have to decide whether to go to the form in design view or form view depending on what you need to do next. 



Microsoft Access – Conditional Formatting

Conditional Formating 


Conditional formatting  can be used to display data in different ways depending on the value involved.  In total you can have four different ways of displaying the data: the default method that will be used if no conditions apply and then three others where the format chosen depends on whether the data meets a set criteria

1. Select the field.

2. Click on Conditional button on the Font group of the design Ribbon.

3. Set the conditions and and formatting required.

4. Click on OK







Microsoft Access Forms – Changing-the appearance of the Controls

Changing the appearance of the controls 


Once an item is selected the appearance can be changed by clicking on the appropriate button on the ribbon.





With AutoFormat it doesn’t matter whether one field is selected or all of the fields, AutoFormat changes the whole form to the format selected.

Changing the size of the controls 


When changing the size of controls, select the control required and position the cursor on the edge of the selected control.  The mouse pointer becomes a double headed arrow.  Then drag in the direction required.
Changing the control in this way will change the size of all of the same type of controls, not just the selected one.


The arrangement of controls 


The positioning of the controls can be changed by using the Arrange ribbon.
Depending on the function of the button, it will impact on just the selected control or the whole series of controls.



Controls in Design View 


When working in design view you need to know what the items are:

1. Label – where static headings are displayed.

2. Text box where information from the tables and queries are displayed.

3. Selected item (is demonstrated by the orange outline).

4. Associated item connected to selected item (shown by grey dashed outline).

5. Control Layout - If the form has been generated where you have chosen a layout then the     individual fields and labels are controlled together that type not just the selected one.


If the controls are to be changed individually then the control layout needs to be taken off.  This can be done by selecting the grouped controls by clicking on the control Layout symbol and then clicking on the Remove button (in the Control Layout section of the Arrange Ribbon). 


Once the controls are individual then they are highlighted in a slightly different way. 
There are handles around the item.  The large handle in the top left hand corner will allow you to move just that item.  The other handles will allow you to resize the control(s) depending on how you drag on the handle.  If you position the mouse pointer on the edge of the selected item, it will display as a four headed arrow (as above) – this allows you to move the selected item(s) and the associated label(s). 
































































Sunday, 6 January 2019

Microsoft Access Field Properties – Format


Format

Field properties are where you fine tune the table. The data type provides general
information about the type of data that a field is going to contain and in the properties is

where you define it more exactly.


Format controls how the data appears in tables, forms and reports (it is the default appearance in forms and reports – it can be changed in them).
The formatting options are different depending on what the data type of the field is.  For text fields the options are:

To display the whole field in upper case.
<   To display  the whole field in lower case.
If no entry is made then the data is displayed as entered.


For number and date/time data types you can choose from a standard list of displays (which meet most requirements) or customise the formatting.



Microsoft Access - Field Properties - Indexed

Indexed



Field properties are where you fine tune the table. The data type provides general
information about the type of data that a field is going to contain and in the properties is

where you define it more exactly.


You use an index to speed up queries, sorting, and grouping operations run against large amounts of data. You can also use indexes to prevent users from entering duplicate values.

The options are:

No : Turns off indexing (default)

Yes (Duplicates OK) : Indexes the field and allows duplicate values. For example, you may 
                                    have duplicate first and last names

Yes (No Duplicates) :  Indexes the field and does not allow duplicate values

If the field name finishes with ID; key; code or num the indexed property is automatically set to Yes (Duplicates OK).  If you do not need to have these fields indexed then the setting has be changed manually. 

Microsoft Access Field Properties – Input Mask

Input Mask

Field properties are where you fine tune the table. The data type provides general
information about the type of data that a field is going to contain and in the properties is

where you define it more exactly


An input mask allows you to control how the data is entered into the field.  Certain text settings here take priority over the format.






Microsoft Access Field Properties – Lookups

Lookups


Field properties are where you fine tune the table. The data type provides general
information about the type of data that a field is going to contain and in the properties is

where you define it more exactly

In the linked tables the data that is stored that makes the connections to other tables is the key field in the other table, which is normally an ID field in some way - a Customer ID or Product ID.  You cannot always deduce who or what the customer is from just the ID and so have to refer to the other table.  To make it easier a lookup can be created and it will automatically do this for you. 

The lookup field can be done in several ways, each of them give the same results. 

1. Select the field that needs the lookup

2. Change the data type to lookup Wizard

3. Select the first option where the lookup column looks up the data from another table

4. Then Choose the table that you want to lookup the data from


5. Select field(s) you wish to see by clicking on the single arrow button in the middle or by double       clicking on the appropriate field name(s)

6. Set the sort order

7. Adjust the column widths by going to the dividing line in the column headings (if necessary)

8. Add column heading (if you want it to be different to the field name)

9. Click on Finish – this will then prompt you to save the table as this saves the relationship that you have created between the two tables

The Lookup wizard can also be started in design view by using the Lookup Column button on the ribbon.  This will insert a new field at the cursor position. 
In the datasheet view (where you can add data), the lookup column can be set up by right clicking on the column head and selecting the Lookup Column. 
NB:  if you have already created a relationship on the field that you wish to set up as a lookup the original relationship has to be broken otherwise the Lookup Wizard will not work.

Microsoft Access Field Properties – Required

Required 

Field properties are where you fine tune the table.  The data type provides general information about the type of data that a field is going to contain and in the properties is where you define it more exactly.

When this property is set to Yes, you must enter a value in the field to save the record.
When a form is set up to add new data, this field must be included on the form or it must have a default value set up otherwise the form will not allow you to save the data.

If the data might need to be entered later then do not set the required property to yes.
In a text field if allow zero length is set to yes then the field will accept  “” . It will appear as if there is nothing in the field.  If you wish to prevent this from happening then set the Allow Zero length to No.

Saturday, 5 January 2019

Microsoft Access Field Properties– Smart Tags

Smart Tags 


Field properties are where you fine tune the table.  The data type provides general information about the type of data that a field is going to contain and in the properties is where you define it more exactly.

You specify one or more smart tags for the field and any controls bound to the field.  Smart tags are components that recognize the types of data in a field and allow you to take action based on that type.  For example, in an E-mail Address field, a smart tag can create a new mail message or add the address to a list of contacts.


Microsoft Access Field Properties – Text Align

Text Align


Field properties are where you fine tune the table.  The data type provides general information about the type of data that a field is going to contain and in the properties is where you define it more exactly.

Text Align Specifies the alignment for data.

The options are:

General : Aligns text to the left, numbers and dates to the right (default setting)

Left : Aligns all text, dates, and numbers to the left

Right : Aligns all text, dates, and numbers to the right

Centre : Centres all text, dates, and numbers

Distribute : Justifies all text, dates, and numbers evenly against both sides of the field or text box

Microsoft Access Field Properties – Unicode Compression

Unicode Compression, IME Mode, IME Sentence Mode


Field properties are where you fine tune the table.  The data type provides general information about the type of data that a field is going to contain and in the properties is where you define it more exactly.


Properties only need to be adjusted if dealing with databases created with a Japanese or
Korean version of Access. (IME stands for Input Method Editor).

Microsoft Access Field Properties – Validation Rule

Validation Rule


Field properties are where you fine tune the table.  The data type provides general information about the type of data that a field is going to contain and in the properties is where you define it more exactly.


The validation rule allows you to set up any rules that the data has to follow.  It is a way of controlling the input of data.  Using the field property will only allow you to set up a rule that applies to that field.  It cannot be used as a way of comparing data in different fields.


You can enter rules such as the employee cannot have a start date of before the company creation date.

The required date cannot be before today’s date.

>=date()

The quantity ordered must be between a particular range.

>=1 and <=500

     or

between 1 and 500

Where key words such as between, and, date() or any other functions are being used, type them in lower case, Access will automatically capitalise the first letter if it has understood it as a key word.

Validation Text 


Validation text is where you type in the error message of a validation rule.  It will be displayed if data is entered that does not follow the rule.

Microsoft Access Forms – Using Forms

Adding, Editing and Deleting Records




The records in a form can be used and ammended in exactly the same way as they are in a

table. The difference is that they are displayed in a different way


On forms you tend to view records one at a time rather than in rows.

New records are added by clicking on the new record button either on the ribbon or the
navigation bar.

Existing records can be changed by changing the data in the existing records. When this is
done the pencil icon appears on the left to show that there is data that hasn’t been saved.

Records can be deleted by clicking on the delete button on the ribbon.

Friday, 4 January 2019

Microsoft Access Field Properties - Field Size

Field Size 


Field properties are where you fine tune the table.  The data type provides general information about the type of data that a field is going to contain and in the properties is where you define it more exactly.



The first property is field size, the only data types that have this property are text, number and autonumber.  The other data types have the field size fixed by the data type.  
The text data type can change the field size to define how much data can be typed into the cell by limiting the number of characters.  The maximum is 255, the default is 50 but it can be changed as necessary.  The field size does not control the amount of disk space the field is taking up – that is dependent on what you type in on the field.






With the number data type it depends on the range of values that are to be typed in as to what field size is required.  Also it is the field size that controls the size that the data takes and not the value.
The Autonumber data type has two number sizes that can be used, Long Integer or Replication ID.

Microsoft Access Field Properties – Default Value

Default Value 

Field properties are where you fine tune the table.  The data type provides general information about the type of data that a field is going to contain and in the properties is where you define it more exactly.

The default value is the value that is entered automatically when the record is created.  You do not have a default value in the primary field (as you do not want duplicates). The default value can be over typed.

You might want to set the current date as the default value on orders or the 1 on the quantities ordered.

Microsoft Access Field Properties – Decimal Places

Decimal Places


Field properties are where you fine tune the table.  The data type provides general information about the type of data that a field is going to contain and in the properties is where you define it more exactly. 

This property only applies to number fields. You can define the number of decimal places
that are displayed on the number. If the field is used in calculations, the complete number is
used, not how it is displayed. The default value is Auto (where it depends on the number)

the range of values is 0-15.

Microsoft Access Field Properties – Caption

Caption 


Field properties are where you fine tune the table.  The data type provides general information about the type of data that a field is going to contain and in the properties is where you define it more exactly.


The caption allows you to set the column heading if you want it to be different to the field
name. If no entry is made then the field name is displayed. It can be used to display the
name in lay mans terms if technical terms are used for field names, or if no spaces are used
in the field names then the caption could have the spaces in so that it appears better on the


Microsoft Access Field Properties – Allow Zero Length

Allow Zero Length


Field properties are where you fine tune the table.  The data type provides general information about the type of data that a field is going to contain and in the properties is where you define it more exactly.

This property only applies to text fields.  When this property is set to Yes, you can enter zero-length strings in a field.  A zero-length string contains no characters.

You use it to indicate that you know no value exists for a field, so that when you query the data you can separate between not knowing if the data exists and knowing that there is no data.

You enter a zero-length string by typing two double quotation marks with no space between them (“”).