FMS developer Molly Pell is featured as a guest blogger on the Microsoft Access developer blog.
This post demonstrates how to use the Pivot statement to control column names returned by crosstab queries, allowing crosstabs to be used on reports.
Check out the post here: Using crosstab queries in reports
For another paper with an example of Creating an Annual 12 Month Summary Report without VBA Code by Creatively Using a Microsoft Access Crosstab Query
When designing an application and its tables, it’s very important to capture the time dimension and determine how data should be stored with the expectation that it will change over time. While there’s a natural tendency to keep data normalized so that the same information is stored in only one place, the time dimension also needs to be considered.
- What Needs to be Preserved Over Time?
- Making Sure Data Normalization Doesn’t Lose Historical Data
- Shadow Tables
For more details, read our paper: Microsoft Access Database Architecture: Taking Time into Account and Shadow Tables
Additional papers and resources in our Microsoft Access Developer and VBA Programming Help Center
Read our new paper on:
Microsoft Access Database Architecture: Storing Temporary Data and User Settings
There are many things a user does with an application that need to be preserved either during processing, between screens, between sessions, or between application updates/versions. When designing a system, it’s important to consider what needs to be kept and where/how to do this. If designed properly, the data should also support multi-user environments.
Users are commonly annoyed to be forced to re-enter their last specifications when the application should start with that as its default. After all, a computer is supposed to be good at remembering things, right?
There are several ways to preserve user information during a session, on a PC, and/or between PCs:
- Keeping Selections in Memory for the Current Session
- Using the Registry to Store User Information Between Sessions
- Using Private Tables to Store Information Between Sessions
- Making Sure Previous Values Remain Valid
Read the paper for more details and tips.
Spreadsheets and Databases
We are often asked by Microsoft Office power users whether, why, and when they should use Microsoft Access versus Microsoft Excel. Especially when they are very comfortable using MS Excel and don’t understand the reasons why anyone would use MS Access or databases. We’ve written a new paper that describes the issues in detail:
- How Microsoft Access and Excel Empower Information Workers
- Advantages of Microsoft Excel Spreadsheets
- Disadvantages of Microsoft Excel Spreadsheets
- Advantages of Microsoft Access and Databases
- Disadvantages of Microsoft Access
- How they Should Work Together
Paper: Microsoft Access versus Microsoft Excel for Data Analysis and Reporting (Spreadsheets vs. Databases)
Data 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.