Data Analysis Tips and Techniques with Luke Chung

Seasoned Access developer and FMS founder Luke Chung shares powerful but often overlooked query techniques for data analysis.

In this comprehensive presentation, Luke demonstrates a variety of practical query approaches for analyzing data in Microsoft Access, including record sampling, frequency distributions, cross-tab reports, and data cleaning techniques. The session focuses particularly on achieving sophisticated analysis results using queries with minimal or no VBA code.

Whether you're working with large datasets or need to present information in more meaningful ways, this presentation offers valuable techniques to enhance your Access development toolkit.

Query Tips & Techniques

Record Sampling

• Use MOD function to select every Nth record
• Employ RND function for random sampling
• Pass field values to ensure proper random distribution
• Control sample size through criteria

Frequency Distribution

• Use PARTITION function to create automatic data bins
• Group data into custom-sized ranges (e.g., age groups)
• Create custom grouping tables for more flexibility
• Combine tables without explicit joins for custom ranges

Not In Queries

• Utilize LEFT JOIN for finding missing data
• Handle complex criteria properly with subqueries
• Identify records missing from related tables
• Combine multiple conditions effectively

Advanced Analysis Tools

Cross-tab Queries

• Transform row data into column formats
• Control column headers with PIVOT clause
• Create flexible reports that handle changing data
• Design reports that work with varying date ranges

Temporary Tables & Databases

• Use temp tables for intermediate processing
• Keep temporary data in frontend for multi-user environments
• Create snapshot databases for consistent reporting
• Pre-process complex calculations for better performance

Data Cleaning

• Handle carriage returns with REPLACE function
• Split compound fields using string functions
• Convert data types appropriately
• Clean up inconsistent data formatting

Modern Charts

New Capabilities

• Available in Access 2019 and 365
• Improved visual appearance over legacy charts
• Better VBA support for programmatic control
• Various chart types (bar, column, line, pie, combo)

Best Practices

• Sort data meaningfully rather than alphabetically
• Add informative data labels
• Group minor categories into "Other"
• Use appropriate chart types for different data

Conclusion

Luke's presentation demonstrates how Access developers can leverage built-in query capabilities to perform sophisticated data analysis without relying heavily on VBA code. The techniques shared can significantly improve how developers handle data manipulation, presentation, and reporting in their Access applications.

Recording

The full recording is available on YouTube:

Join Live!

Want to get even more out of these presentations? Join the live Access User Group events! The next upcoming events are listed on the AUG Event Calendar.

Attending live gives you the opportunity to:

• Interact directly with presenters during Q&A sessions
• Network with other Access developers
• Share your own experiences and challenges
• Get immediate answers to your specific questions
• Participate in group discussions

With multiple user groups across different time zones (and languages!), you're sure to find a meeting time that works for your schedule.

Acknowledgements

• Base cover image generated by FLUX-schnell
• Initial draft generated by Claude-3.5-Sonnet