by John Campbell | Apr 14, 2022 | Database, MS Access
Continuing our series of Tips – Tricks & Guidelines with Microsoft Access we share some additional items. The ongoing articles will continue to focus on tables within an Access database.
Database Tables and Table Relationships
What exactly are database table relationships and why would you want to use them? Designing a database with multiple tables can be particularly challenging. Not only do you have to determine all the database tables but understanding the concept of a database as far as multiple tables is a difficult undertaking.
Some people just give up on trying to do this, and quickly gravitate to MS Excel. Before long, they find themselves in a spreadsheet nightmare of having multiple spreadsheets linked together across the great chasm of a shared network. Individuals navigate to Excel, because they do not have the time or knowledge to build an Access database.
So, assuming you want to build a database in Access, then the table relationship window in Access can be extremely helpful. My professional opinion is don’t create anything in your database until you can map out all relationships using this feature. In the below figure, we see a standard relationship between a customer and an order.
This article will focus on the “one to many” relationship. What does this relationship mean and how is it used? In the above example, the customer information is stored as the “one” relationship and the orders are stored as the “many” relationship. Why would you want to store the customer information more than once? The orders or many side will only store the CustomerID more than once because a customer can order more than once.
For example, if a driveway sealcoat company blacktops your driveway. In this case, the customer is stored in the customer table and all the seal coat/order details will be stored in the orders table.
Two years from the first sealcoat, the driveway will need to be resealed again. The customer is already in the database, so a new order is created for the same customer. In the below form view of the orders shows the final development once the tables are created.
In the above example, the customer job information is the primary component of the one-to-many relationship. If a new job is needed for the same customer, all the user does is select the new job button in the below diagram.
Once the new job is added, then the customer summary screen changes to reflect the 2nd job for the same customer. See the below diagram.
So this takes us back to the table relationships, but also bridges the gap to why you setup relationships in the first place.
The above figure is the outcome of creating the tables and forms. The below figure is where it started when you are setting up the table relationships to begin with. The CustomerID in the customers table matches to an order in the Orders table.
Tip – Never begin to create any database entry forms in an Access database until you have mapped out all the table structure first.
The secondary items in the below diagram include the following:
- Enforce Referential Integrity – An order cannot be entered into the orders table until that said customer is created first. This prevents “lost” orders being created without a customer.
- Cascade Updated Related Fields – If the customer ID field value changes in the customer table, all associated customer ID values will also change in the orders table. This is more prevalent in a database that has product numbers or employee id values changing.
- Cascade Delete Related Records – If you delete a customer, all associated orders will also be deleted. Again, this prevents “lost” or “Orphaned” records from being left alone in the downstream tables.
In summary, designing a database with multiple tables is not an easy task. However, it is possible with a great deal of research and hard work, it can be done. It’s really a puzzle that you solve, and all the pieces are right in front of you when it comes to tracking the data within your day-to-day processes.
If you are having trouble knowing how to get started with Microsoft Access, reach out to Arkware today for any database needs.
by John Campbell | Mar 25, 2022 | MS Access
Continuing our series of Tips – Tricks & Guidelines with Microsoft Access we share some additional items. The ongoing articles will continue to focus on tables within an Access database.
Viewing data within an Access database can come in many methods. Database forms are generally used to view and edit data. However, Access has several options within an Access table for those who are used to viewing data in Excel with a similar feel and look.
Many of these options are found under one menu when you have a particular Access table open. We’ll be highlighting a few of these in this month’s article. See below figure.
One option under the “More” dropdown list is the option to freeze columns. The user is allowed to freeze or lock in place one or more columns. This is useful when the table is open so that you don’t forget or lose track of what record you are looking at. In the above figure, you may want to always view the Company Name, First & Last Name for example. To do this, just click and drag starting on the column headings > click the “More” option as noted > select Freeze fields. Once completed, the user is then able to navigate from left to right and see all the company information while locking the contact information.
A second option that eases viewing is the ability to see the entire field name by widening the column headings. The user is allowed to highlight a column and then select the Field Width option. A dialog box then appears. See below figure.
The best option at this point is to select “Best Fit”. This will adjust the column width to the widest item in the column you are selecting.
Tip – Instead of doing the above manually, the user is also allowed to > Click and drag to highlight all columns > then double click between one of the columns.
This will then adjust all the columns in the table to the “Best Fit” option.
Another option in viewing the table data is to also view the associated linked tables where a table relationship is setup between one or more tables. This relationship is performed by using the Relationship option under the Database Tools main menu. This must be done prior to viewing table relationships within the actual table. See below figure.
In the above example, the end user can see the actual order history for a particular customer. There may additional be additional tables as well. As long as the “+” sign appears in the datasheet mode, then the case of more related tables still stands to be true.
See below figure for multi-table example:
Once you have made any layout changes to a table and you then close the table, a dialog box will appear as noted in the below figure:
Please note that anytime a record is updated in a database and the table or form or lost focus of the record occurs, this saves that data in that record. The above message is asking to save the layout of your table. If you have made any column or appearance changes, these must be save when you close out of the table.
These are just a few time saving tips when using Access tables. If you are having trouble knowing how to get started with Microsoft Access, reach out to Arkware today for any database needs.
by noah | Jan 17, 2022 | Database, MS Access
Microsoft Access is nearly 30 years old but remains very much relevant today. Some businesses use Access for all of their database needs, while others keep it as a front end to more commercially successful database management systems like SQL Server. However you choose to use Access, you can expect a robust productivity tool that’s cost effective and easy to use.
Below are the ten best features we appreciate about Microsoft Access.
1. Modernized Templates
Microsoft Access offers modernized and simplified templates that allow you to create beautiful databases without having to know computer code. Choose from a wide range of templates for inventory tracking, project management, customer service and more. Within minutes, you’ll have a database ready and working for you!
2. Relationships
A relationship in Access lets you combine data from two separate tables. In general, tables can be related in three different ways: one-to-one, one-to-many or many-to-many. Relationships not only create connections between tables but also help prevent missing data and quickly determine the results of a query.
3. Primary Keys
Primary keys are fields with value that are unique throughout a table. A table can only have one primary key. MS Access will either create a primary key for you when you create the table, or you can create your own.
4. Backstage View
The Backstage view is what you see when you open Access but do not open a database. We like this feature because users are able to perform a variety of tasks such as opening an existing database or creating a new one without having to leave the interface.
5. Macros
A macro is a tool that automates tasks and adds functionality to different database parts, including forms, reports and controls. Macros save time and automate tasks that you perform often such as opening and closing forms and running reports.
6. Tables
Access tables look similar to an Excel spreadsheet, but instead of having rows and columns, they’re called records and fields. Also, Access stores data differently than in a spreadsheet. One table is created to track each kind of information to avoid redundancies.
7. Forms
Another neat feature in MS Access is forms. Forms let you view, enter and edit data one row at a time. A form typically includes command buttons and controls to complete other tasks such as sending data to another tool.
8. Reports
Reports offer a way to view, format and summarize information in your database. For example, you can create a simple report of addresses for your contacts, or an inventory report for the holiday season. You also have the option to use sorting, grouping and summarizing data in your reports.
9. Queries
A query is a request for data results. You can use a query to answer a question, perform calculations, combine data and more. In Access, queries are very versatile and let you pull information from various tables and assemble it for display in a report.
10. Modules
One last feature that we love about MS Access is a module. Modules are collections of declarations, statements and procedures that are stored together as a unit. Modules are similar to macros in the sense that they provide more functionality. You will have to use the VBA language to write modules.
Microsoft Access continues to be a reliable productivity tool for businesses. If your business requires MS Access, contact Arkware today. Our experts can help you make the most of the software so that your business can run most efficiently!
by John Campbell | Dec 28, 2021 | MS Access
Continuing our series of Tips – Tricks & Guidelines with Microsoft Access we share some additional items for the month. The ongoing articles will continue to focus on the tables within an Access database.
Filtering within a table
When querying for information in an Access database, the first thing that comes to mind are Queries and Reports. However, if you need some basic information or want to get a quick glance to some specific data, the user can gather the information with the table view through filters on the table.
For example, what if you simply want to know orders from 2 specific states. While the table is open, simply right click in the field you want to filter > right click > select Text Filters and then enter the states or items you want to filter for. See the below figure.
Once an item is selected, the results then appear in the table.
Tip – Sometimes, the filtered data is something that you may do on an ongoing basis. Another option on the tool bar is the Advanced Filter/Sort. See the below diagram.
When selecting this option, the user is sent into what looks like the design of an Access query. At this point, there are many options, but for the purpose of this article, you now have the option of what started as a quick filter to save the filter as a query. Simply right click on the query grid and select “Save as Query” See the below diagram.
Now you have saved a basic filter which can be used again as a query in the database for future reference.
Another option for filtering while in a table is the option to “filter by form”. Again, this is used mainly as a very basic method to search for data within a table quickly and easily. See the below diagram.
Once the filter by form is initiated, then all the records in the table disappear and then each of the fields will show drop down lists on all the fields. Once the dropdown is selected, then it will show the unique values for each column or field. See the below diagram.
Select the item you want to filter for and then those records will be displayed. If more advanced methods or querying are needed, then a select query would most likely be utilized. Additionally, if the table has more than 100K records, then for better performance a standard Access query would be a better choice.
These are just a few time saving tips when using Access tables. If you are having trouble knowing how to get started with Microsoft Access, reach out to Arkware today for any database needs.
by noah | May 17, 2021 | Database, MS Access
Microsoft Access makes it easy to build an inventory database to keep track of your inventory numbers. Thankfully, there are prebuilt inventory databases to take advantage of. Templates save time and money, but it’s possible that you might not find what you need. If this is the case, you can purchase a premium template or hire a database company like Arkware to build one for you.
If you’d like to try and build your own inventory database, here are the steps to follow.
Consider the needs of your business.
Before creating a database, think about what it will need to be useful for your business. Consider who will be using the database and plan accordingly. This means you’ll need to brainstorm what aspects of inventory are most important such as part or product names, quantities, vendors, locations, etc.
Also determine how to implement your inventory database, such as by using an SaaS or cloud product. You’ll also want to consider how your MS database will interact with other programs. Your IT staff should be able to provide this information to you.
Create your database.
Once Microsoft Access is installed on your computer, you can get to work creating your database. You can create your own database or choose a template like the Goods web database that lets you manage inventory, ship effectively and cut costs. If this template fits your needs, you can save a lot of time using it. All you have to do is enter the following:
- Company information
- Employee information
- Products, categories and suppliers
- Inventory levels
- Orders
If you plan on creating your own database, start with a blank database. You can add fields, tables or application parts like ‘units ordered’ or ‘units received.’ You can also create queries, forms, reports or macros. Once the database is set up, you’ll need to move data into your database by entering, pasting or importing the information.
Fine tune your database to fit your needs.
Whether you use a template or build a database from scratch, you’ll need to fine tune it according to your notes. Link or establish relationships between the fields and determine which values you’ll need to have returned when using the database. Also create a way to store these values. This will help you avoid duplicate data in multiple fields.
The final step is to populate your inventory database with information. You might also want to use additional technologies to structure queries for databases. If you decide to do this, we recommend delegating this work to a database expert. This way, you can ensure everything is set up appropriately.
Schedule a Consultation with Arkware
Arkware can help you build an inventory database that will help your business run efficiently. Contact us today for a consultation and let’s chat about your new database and what you hope to see from it.
by Jim Ebsen | Mar 25, 2021 | MS Access
Microsoft Access – The Basics
Easy Development for Non-Developers
As is typical of the many applications developed by Microsoft, MS Access has been designed to be intuitive for the user. With many in-program wizards, help screens, drag and drop features, and even online training tools provided by Microsoft, it is easy to get started in MS Access.
Access Video Training
What you need to start: MS Office Versions with MS Access
Microsoft Access is a low-cost and easy-to-use database development application that is included with specific versions of Microsoft Office. Modern MS Office packages where MS Access is included are 2019, 2016, & 2013 in both the Professional and Professional Plus editions. For MS Office 365, the minimum licenses that include MS Access are Business Standard and Microsoft 365 Apps.
Free, Runtime Use
To do development, or to make alterations to an existing database, a full version of MS Access is required. However, the free, MS Access runtime package allows an MS Access database to be distributed to users who do not have the full version of Access installed. See Run Time Download 1; Run TIme Download 2
Start with a Template
To give you a jumpstart, Microsoft has many free MS Access database templates available for download so you can quickly begin customizing your first database to meet your needs. There are many options to pick from, but one of them is certain to help you hit the ground running.
Perhaps a few simple modifications could get you the outcome you desire.
Not seeing what you need? Arkware is your answer.
Arkware has provided MS Access services to many different industries. If you need a full, low-cost package from start to finish, or if you are stuck in one of the finer details of MS Access, please let us know and we would be glad to help. We are your MS Access experts!!
