A blank database is a standard Access database, and is good for local use. Creating a blank database will create one table as well. Web databases are designed to be compatible with Access’s web publishing tools. Creating a blank database will create one table as well. Templates are pre-built databases designed for a wide range of uses. Pick a template if you don’t want to spend a lot of time putting the database structure together.
Tables – This is the main way that data is stored in your database. Tables can be compared to spreadsheets in Excel: the data is organized in rows and columns. Because of this, importing data from Excel and other spreadsheet programs is a relatively straightforward process. Forms – Forms are the way that data is added to your database. While you can enter the data into the database directly into the tables, using forms allows for quicker and more visual data entry. Reports – These summarize and display the data in your database. Reports are for analyzing data and returning answers to specific questions, such as how much profit was made, or where customers are located. These are usually designed to be printed out. Queries – This is how you retrieve and filter your data. You can use queries to display specific entries from multiple tables. You can also use queries to create and update data.
Each piece of data should be give its own column (field), while each record should be a separate row. For example, each row would be a customer while each field would be a different piece of information about that customer (first name, last name, email address, phone number, etc. ). You can rename the column labels to make it easy to tell what field is what. Double click the column heading to change the name.
Click the External Data tab. Select the file type that you are importing. In the “Import and Link” section, you will see a few options for data types. You can click the More button to see more option. ODBC stands for Open Database Connectivity, and includes databases such as SQL. Navigate to the location of the data. If it is on a server, you will need to provide the server address. In the next window, choose “Specify how and where you want to store the data in the current database. " Choose “OK. " Follow the steps to import your data.
In the Create section of the Home tab, click the table button. A new table will appear in your database. You can enter information in the same way you did for the first table.
For example, in your Orders table, you may have a Customer ID field to track which customer ordered which product. You can create a relationship for that field with the ID field in your Customer table. Using relationships helps keep your data consistent, efficient, and readable.
You will need to have created the field for the foreign key before you create the relationship. For example, if you want to use the Customer ID on the Orders table, create a field in the Orders table called Customer and leave it blank. Make sure it is the same format as the field you are linking (numbers in this case).
Check the box to “Enforce Referential Integrity” when creating the relationship. This means that if data is changed in one field, the other field is automatically updated. This will help keep your data accurate.
Queries are broken down into two main types: Select and Action. Select queries pull data from tables and can make calculations. Action queries can add, edit, and delete data from tables.
You can use multiple criteria per query.
You can end parameters with “?” or “:”, but not with “!” or “. ”
If you don’t want a specific field to be displayed on the form, you can right-click on it and select Delete. If your tables have relationships, a datasheet will appear beneath each record, showing the connected data. You can edit your connected data this way much easier. For example, each sales rep in your database may have a customer database attached to their record.
Report – This will create an auto-report with all of the data from your source. Nothing will be grouped, but for small databases this is probably sufficient for showing what you need. Blank Report – This will create an empty report that you can fill with your data as you see fit. You will be able to choose from any available field to create a custom report. Report Wizard – The report wizard will guide you through the report creation process, allowing to to choose and group your data, and then format it accordingly.
Click the down arrow next to the Record Source field. A list of your available tables and queries will appear. Select one and it will be assigned to the report.
Click and drag the fields you want to add into the Design frame. The record will appear in the report. As you add additional fields, they will be lined up automatically with existing fields. You can resize fields by clicking on the edges and dragging the mouse. Delete fields from the report by clicking on the heading and pressing the Delete key.
Click the Design tab, click the Group & Sort button. Right-click on any part of the field you want to add to a group. Select Group On from the menu. A header will be created for the group. You can adjust the header to whatever you want to label the group.
