If your business is located in Los Angeles, CA, this information can help acquaint you with functionalities and features of Microsoft Access. The material here can be used with earlier versions of Access, including Access 2016, 2013, 2010, and 2007. Familiarity with the components of Microsoft Access - tables, tables, forms, queries, and other objects will help you understand more about how Access works to create a workable database solution for your business.
We find that business owners and other database stakeholders who have some background in the Microsoft Access database terminology will make smarter decisions about their database requirements and how to work with a professional database developer, like MS Access Solutions. It will also assist in making you more conversant about MS Access and databases in general.
Are you seeking a professional Microsoft Access programmer "near me" in Los Angeles? If you are, contact MS Access Solutions, home of the Best Database Solutions company. We've been programming with Microsoft Access since Version 1 - in fact we've authored over 15 books and videos and how to for Access. We speak at conferences and consult with other top Microsoft database programmers. Call us at (323) 285-0939 to schedule a complimentary consultation so we can help you right away.
Here's a short high level view of an Microsoft Access database. Access has excellent tools for you to use to view and learn about database structure. The information below will explain why, how, and when you can use these tools.
The information for this article is designed around the desktop (traditional) Microsoft Access database system. Tools, like data entry forms are used with the current and previous versions os MS Access. However, some of this material will not apply to the Microsoft Access web databases or the now retired Access web apps.
The material in this article contains:
Overview
How to view details about the objects in a database
Design view for an Access table
View Relationships between tables
What is a database? A database is a collection of information that relates to a specific topic, purpose, or subject. Examples include information like a music collection, customer tracking of orders, or contact names, address, and phone numbers. Information like this was formerly stored on paper, or often, not captured at all. To get the most out of your computer database program, these other forms of data must be recreated in the database software.
What would happen if you need to store basic information for customers, but the data is located in multiple locations? For example, suppose the data for your suppliers are stored in various locations: in a card file containing supplier phone numbers, addresses stored on a paper list, and a duplicate file stored in an old spreadsheet. When changes occur, you may be required to change data in these multiple locations.
In a professionally developed Microsoft Access database, a supplier's phone numbers is stored only 1 time. When you need to update a phone number, you so you only have to update that information in one place. As a result, when you update a supplier's phone number, it is automatically updated wherever you use it in the database.
You can use Access to manage all of your information in one file. Within an Access database file, you can use:
MS Access Tables for data storage
Queries for locating and retrieving specific data
Forms are used to add, update and view table data
Reports are used to print or analyze data in a specified layout
Table Data can be used in a query, form, and a report
The items listed above: The tables, queries, forms, and reports are all "database objects."
A Microsoft Access database can link to tables that are data storage in other databases. You could create one Access database that contains only database tables, then create another MS Access database with links to the tables in the first database. You can also creates queries, forms, and reports that use data in those linked tables. Database applications utilizing linked tables are very efficient and are considered an excellent method for designing and developing database systems.
For proper data storage, each table should contain a specific type of information. Information types include products, customers, or order details. Relationships are used to bring the data together from any number of queries, forms, or reports.
However, web databases do not use Relationships object tab for creating relationships. Lookup fields are used to create relationships in a web database. Here are examples of data stored in tables that are joined by using related fields.
A Customers table can contain the information that was once in a mailing list.
An Orders table can contain information that was once located within a spreadsheet.
To differentiate on record from another, a proper database uses a unique ID, like a customer id. You can then one table's unique ID field to another table for defining a relationship between the two fields. Then Microsoft Access can use related records from the two tables (in this example) so that data can be used in reports, forms, or queries.
Microsoft Queries are a form of "question" that retrieves data meeting the specified conditions. This can be used with data from multiple tables. A query can be used to update and/or delete multiple records during its execution and can be used for custom calculations with the data. A query cannot be used to update or delete records with a web database.
Here are some examples of what queries can do:
The Customers table has information about customers, while the Orders table has information about customer orders.
A query can be developed that retrieves the Order ID and a Required Date data from the Orders table, and the Company Name, City, State, and Zip Code data from the Customers table. In this example: A query is created that will return only orders that were required in April, and only for customers who are based in New York City.
You can use a form to easily view, enter, and change data one row at a time. You can also use a form to perform other actions, such as sending data to another application. Forms typically contain controls that are linked to underlying fields in tables. When opening an Access form, the application will return data from one or more of those tables. The software will then display the data in a layout that you specify when creating the form. You can create a form by using the Form Wizard, or create a form yourself, or use one of the Form commands on the Ribbon in Design view.
Web databases require that you use Layout view, not Design view, to create reports and forms.
About Access Forms
Forms can be used to enter and view data. Here's an example:
A table displays many records at the same time, but this requires scrolling to view all of the data in one single record. Additionally, when viewing a table in you will not be able to update data in multiple tables in one procedure.
A form can retrieve and display one record at a time, plus a form can display multiple fields from multiple tables. Plus, a form can also be used to display pictures and other objects.
You can use a button on a form for clicking to open other objects and forms, click to print a report, or automate other tasks.
You can use a Microsoft Access report to quickly analyze your data or to present it a certain way in print or in other formats. For example, you may send a colleague a report that groups data and calculates totals. Or, you may create a report with address data formatted for printing mailing labels.
Uses For Access Reports
A report can be used to create different types of mailing labels
Chart totals can be displayed in a report
A report can be used to display calculated totals
Database Documenter
One of the best ways to learn about a particular database is by using the Database Documenter. You use the Database Documenter to build a report containing detailed information about the objects in a database. You first choose which objects will be detailed in the report. When you run the Database Documenter, its report contains all of the data about the database objects that you selected.
Open the database that you want to document. On the Database Tools tab, in the Analyze group, click Database Documenter. In the Documenter dialog box, click the tab that represents the type of database object that you want to document. To create a report on all of the objects in a database, click the All Object Types tab. Select one or more of the objects listed on the tab. To select all of the objects on a tab, click Select All. Click OK.
The Database Documenter creates a report that contains detailed data for each selected object, and then opens the report in Print Preview. For example, if you run the Database Documenter against a data entry form, the report created by the Documenter lists the properties for the form as a whole, the properties for each of the sections in the form, and the properties for any buttons, labels, text boxes, and other controls on the form, plus any code modules and user permissions that are associated with the form.
To print the report, on the Print Preview tab, in the Print group, click Print.
Use Design View To Examine A Table
Please note; you cannot use design view for web databases.
You can open a table in the Design view to get a detailed view for table structure.You can locate the data type for each field, find if the table uses lookup fields for queries to extract table data, or see if there are any input masks. Data types and input mask settings can impact your ability to locate data and running update queries. For example: Queries require that field types in both source and destination tables must match.
To perform the analysis, open the database you are inspecting
In the Navigation Pane, right-click the table that you want to explore, and then click Design View on the shortcut menu.
As needed, note the name of each table field and the data type assigned to each field.
The data type assigned to a field can limit the size and type of data that users can enter in a field.
For example, the database users might be limited to entering only 25 characters in a text field, plus they might be limited to entering only numerical data into another field.
Is this a Lookup Field - you can find out if a field is a lookup field by clicking the Lookup tab in the lower section of the table design grid within the Field Properties.
About Lookup Fields
A lookup field displays one set of values (one or more fields, such as a first and last name), but usually stores a different set of values (one field, such as a numeric ID). For example, a lookup field may store an employee's ID number (the stored value), but it displays the employee's name (the displayed value). When you use a lookup field in expressions or in find and replace operations, you use the stored value, not the displayed value. Becoming familiar with the stored and displayed values of a lookup field is the best way to make sure that an expression or a find and replace operation that uses the lookup field works the way that you expect.
Lookup Field Demo
In a typical Lookup field the settings used in the Row Source property of the field will vary.
Use a query or a table as the lookup field data source. You can also use a query to retrieve data from another table
The lookup field can use a query to retrieve data from another table. There is another type of lookup field; the Value List, that will use a programmed list of choices.
Using A Value List
The value list can be used a lookup field data source. As a default value, the value list uses the text data type.
The quickest method to locate lookup and value lists is to display the Lookup tab. Next click on entries in for each field in the table by clicking the Data Type column.
To view a graphical representation of database tables, plus the fields in each table as well as the table relationships, you can use the Relationships Object tab. The Relationships Object tab gives an overall view of the database table and relationship structure, which is important crucial information when creating or editing relationships between tables. The Relationships Object tab can be used to add, edit, or delete relationships.
How To Use Relationships
Open the database that you want to analyze, then find the Database Tools tab; next locate Relationships group and click on Relationships. The Relationships object tab will appear and displays the relationships between all the tables in the current database. As with many Access tools and features, the Relationships object tab is not used for web databases.
Our Microsoft Access developer team has over 25 years experience developing Microsoft Access databases for business clients. We can design, develop (program), and deliver a highly efficient and robust database with all the features you need. We can develop an MS Access database that connects to Microsoft's flagship business database, SQL Server, with the built in capacity for you to manipulate your data so you get the information you need for every day activities and for making critical business decisions.
Call MS Access Solutions at (323) 285-0939 to schedule a complimentary consultation so we can help you right away.
MS Access Solutions
811 Marina del Rey, CA 90292
(323)285-0939