Why use Access instead of Excel? Shared workbooks in Excel are prone to corruption, so if multiple users need to view and update data, Access provides the better platform. The data you are working with is in one database file. Typically when you use Excel, you are working across multiple workbooks which may need linking together.
Espresso topics are 'bite' sized topics that are no longer than two hours in duration. If you feel that you need longer on an individual topic or want a 'pic 'n mix' of multiple topics then request a Latte workshop. If you can't see a topic of your choice, please get in contact so that we can discuss it.
If you feel that you need longer on an individual topic or want a 'pic 'n mix' of multiple topics then request a Latte workshop. If you can't see a topic of your choice, please get in contact so that we can discuss it.
SET UP THE ACCESS APPLICATION
Customise the Ribbon (the area where all the buttons live) and the small toolbar known as the Quick Access toolbar (QAT) which sits above and to the left of the Ribbon. Use Options to set up Access.
DESIGN AND STRUCTURE
Microsoft Access is a relational database management system (RDBMS) which consists of four diferent types of objects. Using a sample database you will explore how these objects interact together. The four objects are as follows:
1. Tables and the Relationships that are created between tables.
2. Queries to retrieve data from the tables.
3. Forms that act as a user interface to enter data into the tables.
4. Reports to view, print and share your data.
CREATE TABLES AND RELATIONSHIPS
A relational database holds its data in more than one table. Relationships between the tables are defined so that information can be used from more than one table at a time. The main types of relationship are one to one, one to many and many to many. Learn how to set validation rules to reduce data input errors, for example: prevent a person under the age of 16 from being hired.
Creating queries is one of the most important and frequently performed database tasks. Queries are used in Access to view, find and analyse data in different ways i.e. bring together data from multiple tables and sort it in a particular order; perform calculations on groups of records, create crosstab queries which group the results of two types of information that you specify. You can also use them as a source of records for forms and reports.
Create a User Interface using forms. Learn how to use the Form Wizard to create simple but useful data entry forms.
A report is used to convey the information contained in a table or query in a clear and easily understandable format. Reports enable users to group and sort data, display graphics, create totals and calculate averages or other statistics. Since the user has control over the size and appearance of the data within the report, it in an extremely flexible way to present information. You can design custom reports or use the Report Wizards to create a report in a selected style and format.
The modules above will provide an introduction to each of the Access objects. Now take the more advanced modules to really control your database:
FURTHER ACCESS - FORMS
Learn further form design and an introduction to creating macros. Macros are code scripts that help automate the database. For example when you click a button on a Customer form, a second form opens showing the orders that the customer in the first form has placed. Add a ‘switchboard’ to the User Interface. A switchboard is typically the first form that you see when the database opens and provides buttons to navigate through the database.
FURTHER ACCESS - REPORTS
Create main and sub-reports.
FURTHER ACCESS - QUERIES
Create more advanced calculations in queries and use SQL in queries. SQL stands for Structured Query Language and a little knowledge of this language will help ypu create more powerful queries.
FURTHER ACCESS - ACTION QUERIES
If you need to make changes to multiple records at the same time, such as archive all records older than a certain date, you will need to create an Action Query. They are used to add, delete or update records in the tables of the database.