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 full post, 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
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:
Read our paper, Microsoft Access Database Architecture: Storing Temporary Data and User Settings for more details and tips.
Microsoft Access combo boxes let you display data for users to select values from predefined lists. Using them effectively simplifies data entry and accuracy.
Brush up on the ComboBox Basics from Microsoft, and check out our Top Five Tips for using Combo Boxes on Microsoft Access forms. Each of them has articles describing them in more detail.
1. Set Important ComboBox Properties
To use combo boxes effectively, learn about the following properties:
* LimitToList: Set this property to Yes to prevent values that are not in your list.
* AutoExpand: Set this property to Yes to automatically select a matching value in the list as you type.
* ListRows: Set this value to a high value so that the drop down shows as many list items as space allows.
Use the ListIndexproperty instead IsNull to properly validate that a valid ComboBox list item is selected.
Use the following syntax to automatically select the first item in a ComboBox when the form loads, or when the value of another control on the form changes:
Me.ControlName = Me.ControlName.ItemData(0)
On a form with multiple ComboBoxes, you may want to make the selection in one ComboBox limit the choices in another ComboBox. To do this, add code to the “AfterUpdate” event of the first control that updates the RowSource property of the second control.
Use Total ZipCode Database from FMS to automatically fill a ComboBox with a list of valid City / State combinations when the user types a zip code.
Microsoft’s SkyDrive service has offered everyone a free 25GB hard disk in the cloud. This lets you store your files, backups, and even share files with others. It’s an amazing free offer that we’ve mentioned in the past.
Unfortunately, Microsoft has just reduced the free amount to 7 GB. That’s still generous, is more than Apple’s iCloud, and is what’s offered to new customers. For a limited time, any registered SkyDrive user *who has uploaded files to SkyDrive* as of April 22nd can opt in to keep 25GB of free storage while still getting all of the benefits of the new service.
So, if you already have a SkyDrive account, they are letting you keep your 25GB disk but you need to claim it.
Simply log into your SkyDrive account at skydrive.com with your Microsoft’s Windows Live credentials. On the bottom left of your account page, and click on the “Manage Storage” link. You’ll see a listing of storage plans, and under “SkyDrive Free” a button that says “Free upgrade!”
Just click it and you should see this:
For a more detailed description and video of how it’s used, see Microsoft’s Steven Sinofsky explain the exciting new features and vision:Note: After losing a court case in the UK, Microsoft has renamed SkyDrive to OneDrive.
In Microsoft Access, a common need is to have multiple combo boxes or list boxes on a form, and to have the selection in one combo box limit the choices in a second combo box or listbox. For example, consider an Address form containing State and City lookups. When you select a state, you want the list of cities list to be limited the selected state.
This is known as cascading combo boxes or synchronized combo boxes.
We recently posted a tip and demo database containing a sample of species, both plants and animals, categorized by their taxonomic rank (kingdom, phylum, class, order, family, and genus). When you select the value “Animal” from the Kingdom combo box, the Phylum combo box is updated to only show Animal phylum. The Species list box is also filtered by your selection.
To learn more, read our page on Creating Cascading Combo Boxes and List Boxes on Microsoft Access Forms and download our sample database.
Answered the question “How do I import email addresses from a query in Microsoft Access (2010) into an Outlook (2010) Address book?” on the Microsoft Answers site.
Longtime client Kathy Korman Frey, George Washington University School of Business professor and GWU Entrepreneur in Residence, has engaged the FMS Professional Solutions Group to enhance her HotMommas Project website.
The site is the world's largest collection of public case studies for entrepreneurial women. It is a community where women can learn from the experiences of other women addressing their businesses, family and personal challenges.
The revamped website is built on Visual Studio .NET, SQL Server with a modern, dynamic interface incorporating our technical and graphic artist resources.
Contact us if we can help you with a similar solution.
Yesterday, FMS President Luke Chung was invited by the governor’s office to participate in his proclamation of 2012 as the Year of the Entrepreneur. Luke stood behind Governor Bob McDonnell and Lieutenant Governor Bill Bolling during the press conference and participated in a day-long event supporting entrepreneurship, small businesses, and job creation.
Commerce Secretary James Cheng led the events. Participants were able to hear from successful Virginia business founders and learn from each other through a luncheon and Entrepreneurial Town Hall. Examples of agricultural, technology, manufacturing, and craft businesses showed the diversity of Virginia firms offering products and services for in-state, national and international customers. It was also interesting to trace the roots of the founding of Virginia in 1607 as a high-risk entrepreneurial enterprise that eventually led to success after many failures.
All parties recognized the value and responsibility of seasoned entrepreneurs helping newer entrepreneurs, and how fundamental this was to the success of our state and nation. Activities will occur around the commonwealth over the year where government representatives and entrepreneurs share their ideas, experiences, and resources. Already recognized as one of the most business friendly states in the country with one of the lowest unemployment rates, Virginia continues to foster business success in a bipartisan manner.
Noteworthy was the inclusion of Education Secretary Laura Fornash in the activities stressing the importance of public education as part of a healthy business climate. This includes having great K-12 education and the many higher education institutions across Virginia. Those institutions attract bright students from outside Virginia, create entrepreneurial opportunities around them, and give us the ability to keep them in Virginia for life. FMS and Luke Chung are honored to be a part of this initiative.
For more information visit:
LinkedIn offers many opportunities for professionals to interact with each other. There are many groups available for the Microsoft Access, Azure, SQL Server, and Visual Studio .NET communities. Here are some of the vibrant groups we’ve discovered:
Professional Microsoft Access Developers’ Network (PMADN)
Excel and VBA Users
Microsoft Azure and SQL Server
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.
Microsoft SQL Server