Feb 13

Transposing Data in Microsoft Access Tables and Data Normalization

Microsoft AccessMicrosoft SQL ServerData normalization is fundamental to database design. Properly normalized data makes it easy to support an application over time and simplifies the querying, displaying, and reporting features of an application. 

Unfortunately, we don’t always receive or have normalized data. Tables that require adding fields as the data changes over time are particularly problematic and violate the basic premise of database design where adding records is free, but adding fields is expensive:

Here are some updated resources detailing the value of data normalization, including a sample database and VBA code to transpose and normalize your existing data.

These and other related papers are part of our developer centers:

Hope these help you create more scalable, maintainable, and analyzable databases.

For advanced data analysis, check out our Total Access Statistics add-in product.

Sep 22

Email Collaboration using Microsoft Outlook and Exchange from a Database Perspective

One of the challenges most organizations face is how to coordinate communications and tasks among team members and external contacts. With multiple people and clients/projects, emails fly in many directions. People with vital information may be unreachable while customers may be providing information to someone in your organization while others who need that information are oblivious. When someone leaves a team or organization, much of their information is lost.

Microsoft OutlookOver the years, we've helped several clients better manage their emails and treat them like a database. We've built solutions that work with Exchange and Outlook to automatically classify contacts, tag emails, and store the information in a Microsoft SQL Server database. The data is presented through a Microsoft Outlook add-in showing all communications with a contact's firm when you create or respond to an email. The data can also be displayed in the Facebook like interface to make it easy for everyone on your team to know what's going on.

There's no longer a need to look in someone else's Inbox since information is immediately shared between everyone who needs to know (even before the recipient opens their message). Searching for messages is quick and easy, and corporate document retention policies can be enforced.

To learn more about this and other innovative activities of our Professional Solutions Group, please contact our consulting team.

May 31

Microsoft Access Database Scalability: How many users can it support?

Microsoft Access 2013What’s the Maximum Number of Microsoft Access Users?

There is a persistent myth that Microsoft Access Jet databases can only support 20 or so users. Here’s my response to a recent inquiry:

I flatly refute any suggestions that Microsoft Access users are limited to around 30. We’ve run many tests and have never seen that kind of degradation in performance. It is a myth from Access 2.0 days that was eliminated with Access 97 almost two decades ago.

A poorly designed Access database won’t support two users, but a well designed Access solution can support hundreds of users. Of course, what matters is the number of simultaneous users, and what they’re doing.

At Least 200 Simultaneous Microsoft Access Users on an Access/Jet Database

If everyone is just viewing data or entering data into a table, that takes very little work and a large number of people (well over 200) can be supported. People cannot type faster than what Access can handle. If users are all running massive reports and queries with data updates, that can still be done but performance would be an issue which applies to any technology, so testing and optimization would be necessary.

Migrate Back-End Access Database to Microsoft SQL Server for More Users

Microsoft SQL ServerIf the back-end database is in SQL Server rather than an Access/Jet database, the number of users can be practically unlimited if each user has their own front-end copy of the Access application. Performance issues still apply based on what they are doing. In some cases SQL Server is slower than Access, so it is important to understand the situation before thinking SQL Server is the answer.

All that said, any Access application that is distributed to others with shared data should be a split database design. Here are a few resources we’ve written:

Total Access StartupSimplify Support for All the Users of Your Access Databases

We offer a commercial product for enterprises, Total Access Startup, that helps with the distribution of databases to each user’s desktop, and launching it with the right version of Microsoft Access.

This allows you to centrally support a large number of Access users across your network and ensure everyone is using the latest version of your application. It also simplifies the migration from one version of Access to another.

Additional Discussions

MicrosoftThis topic was also discussed on the Microsoft Developer Network here:
Microsoft Access Database Scalability: How many users can it support?

Sep 29

Total Access Statistics for Microsoft Access 2010 is Shipping

Microsoft Access 2010Total Access Statistics 2010

We are very pleased to announce Total Access Statistics for Microsoft Access 2010 is now shipping, along with updates for earlier versions of Access. Total Access Statistics is the most popular data analysis program for Microsoft Access. It extends the data analysis capabilities of Access queries to let you perform advanced numerical analysis on your data. Use any Access table, linked table, or query to perform calculations such as percentiles, regressions, frequency distributions, t-Tests, correlations, non-parametrics, rankings, moving averages, etc. It can also perform data normalization and let you select random records. As you would expect in a query, you can specify Group By fields so analysis is performed on each set of records with identical group fields.

Total Access Statistics runs within Access with all output in Access tables. It supports MDB, ACCDB, and ADP databases.

In addition to supporting Access 2010, we've added Financial Calculations for Cash Flows. It now calculates net present value (NPV), present value (PV), future value (FV), internal rate of return (IRR), and modified internal rates of return (MIRR). There's support for both the 32 and 64 bit versions of Access 2010. It includes both the interactive wizard that runs as an add-in, plus the programmatic VBA library so you can embed statistical analysis in your applications.

Here is additional information for:

Free demo versions are also available for you to download. 

May 11

Adding (and Subtracting) Weekdays in Microsoft Access, VBA, and VB6

Microsoft Access, VBA, and VB6 include a wide range of built-in Date functions, including DateAdd, which calculates the difference between two dates. A common need, however, is to add a number of weekdays to a date, without counting weekend dates.

Learn about this and all our royalty-free module code in Total Visual SourceBook where you can also perform business day math with a list of holidays to avoid.