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 noah | Jul 22, 2021 | Database
If you’re currently using Microsoft Excel as your database, you probably want to rethink this. Excel is a great tool for creating spreadsheets and performing calculations with restricted data sets, but it’s not the best database tool. However, many people continue to use Excel as a database because it’s simple and familiar.
Luckily, you can upgrade to Microsoft Access, a database management system that stores information. Access is user-friendly, even for beginners, offering a similar look and feel to other Microsoft products. And, you can convert your Excel spreadsheets to your database.
Let’s discuss the biggest signs that your business or organization has outgrown Excel and why a database program like Access is a better fit.
Your Workflows are More Complex
Excel works fine for simple, linear workflows. But once your workflows become complex, it’s time to upgrade to a database program like Access. This way, you can easily enter or display information for various types of related information.
Your Data is Growing
Once you start to accumulate large amounts of data, it’s time to move away from Excel. Excel has a limitation on the number of records per sheet, and it can suffer performance issues as your data grows. You can separate the data into many sheets, but this eventually becomes a big headache, especially if you can’t make sense of your data.
There is Less Security
Another shortfall with Excel is its lack of security. Excel does give you the option to password protect your spreadsheets, but it’s a delicate system. However, there are tools online that make it easy for people to unlock protected files without having to know the password. To protect your data, it’s worth moving up to Access. And, you can choose what permissions to give each user.
Collaboration is Difficult
Collaboration is a lot more difficult on Excel than it is on Access. Even when you use multi-user editing or store your spreadsheet on a shared server, it’s easy for errors to happen. One of the most common errors: people overwriting each other’s work. If you require multiple users to access your data, a database program is the way to go.
Your Tasks are Tedious
Do you feel that you’re spending more time having to complete tedious tasks on Excel? This doesn’t have to be your norm. Database programs like Access have tools and features to make you most productive with your time. You can automate many of your tasks and reduce redundancies by storing related data.
These are just some of the reasons why Excel is not the best solution for your database. We understand that it’s familiar and easy to understand, but there are better options out there. If you’re looking for something simple and user-friendly, you’ll feel right at home with Access. Contact Arkware today to learn more about your database options.
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!!
by noah | Mar 17, 2021 | Database, MS Access
A program like Microsoft Access makes it easy to build a database that is fast and optimized. You can choose from the available templates or create your own. If you use a template, all you need to do is plug in your information and voila! If your needs are more complex, you can hire a database expert like Arwkare to build a personalized database.
However what happens when you’ve already paid someone to build your database and they didn’t do an adequate job? Unfortunately, this happens. If your database isn’t running as well as it should, it’s possible that it has a poor design with one or more of these practices.
Poor Normalization
Database normalization is the process of structuring a database to avoid redundant or duplicate information. Unfortunately, some databases are designed on the fly without following the rules of normalization.
At the very least, all databases should be normalized to third normal form. With this setup, each column of a table will be dependent on the primary identifier. If your database doesn’t comply with first, second or third normal form, consider redesigning these tables. We promise – it will pay off in the long run!
Improper Naming
We’re not going to get into the details on how to best name things as this is a topic in itself. What we want to stress is the need for consistency. The names you choose for your database are not just for identifying objects but also to allow future programmers, users, etc. to quickly and easily understand your database. In other words, no one should have to read an exhaustive manual to find out what a name means.
Lack of Documentation
When you carefully name your objects, columns and so forth, it makes it clear to everyone what your database is modeling. Follow a consistent naming standard as well as definitions on tables, columns, relationships and default and check constraints. Poor design tends to have a lack of documentation, and this makes it difficult for users to understand your database.
Not Using Stored Procedures
Stored procedures refer to SQL code that is saved to be used over and over again. While procedures might take a bit more effort at first, they’re worth it in the long run. Stored procedures offer the following advantages:
- Quick response times because the procedures are created and stored
- Option to group all the required SQL statements in a procedure and execute them at once
- Avoid repetition of code
- Use additional SQL functionalities
- Use the code in any number of applications
Lack of Testing
When it comes to testing, we recommend having a strict testing plan in place. This plan should go through every part of the development process to identify bugs and diagnose and fix problems that would otherwise lead to corruption. Good databases are frequently tested and end up running optimally because of it.
The best way to ensure a functional, efficient database design is by working with the right team of database development experts. Arkware has decades of experience building, repairing and optimizing databases and we always follow the best practices. Contact us today to discuss your database needs.
by John Campbell | Jan 19, 2021 | MS Access
All programs seem to have some sort of time saving tips or special shortcuts and Microsoft Access is no exception. The casual user may not be aware of many of these tips, so at Arkware, we have shared a few query tips and tricks.
Top Values Setting
When in the design of any query, there is a setting to filter out the top records. The top values setting provides several different options. One option is to return a certain percentage of data. If you want to know the top 25% of your highest price orders, you just select the 25% option from the list. The important part of this feature is to always have the data in the proper sort order. The drop-down list has some pre-defined choices, but it allows any values entered.
Zoom Box
It is often difficult to see everything in any part of a query when typing in the criteria or field name area. A keyboard shortcut of “Shift + F2” will bring up the zoom box. The zoom box allows you to see everything in one separate window.
Datasheet View button versus Run Button
When creating a standard query, there are two choices to see the query results. You can select either the datasheet button or the run button. However, when creating action queries (queries that perform action on records), using the run button will carry out the query. Before you run a delete or update query using the run button, you may want to see what or how many records are going to be updated or changed. Selecting the datasheet button will allow you to do this. Once you are confident of what records will be updated, then the run or action query button can be used.
ODBC Timeout Setting
Microsoft Access allows you to connect to other data sources such as a SQL Server database. Depending on the design of the SQL Server database and the number of records that are retrieved, it can take some time to retrieve those records.
The default ODBC (ODBC stands for “Object Database Connectivity) timeout setting is 60 seconds. This is the time it will take for the query to return data results. If it takes longer, the user will receive a time out error. A tip for this setting is to enter the value of zero in the setting. By entering a zero, the time out is set to unlimited. This means the user will not see a timeout error if it takes longer.
Parameter Query Setting
Microsoft Access offers many options for entering query criteria. One option is the parameter query option. This option prompts the user for specific data input. For example, you can have the option prompt for a specific data range by using the Between [Enter the start date] And [Enter the end date].
Sometimes you want to see all the records for a specific situation. For example, if you do not know the starting or ending dates for example, you may be excluding some records.
The tip for this feature is to add a statement at the end of the criteria. As noted below, the way to use this feature is once the query prompts the user to enter the date range, but not entering a date for the start and end date prompts, it will return all the records from the select query, and in this case all dates.
Hopefully, these tips will help you in your work with Microsoft Access. If you need help with your project or do not know where to begin in the design of any database, please reach out to Arkware for assistance.
