troubleshooting Question

statistical query over multiple tables

Avatar of Pau Lo
Pau Lo asked on
DatabasesMicrosoft Access
7 Comments1 Solution17 ViewsLast Modified:
I need to perform some analysis in an Access database based on payroll data. I have the payroll data for 12 months worth of payments, each imported into the database in a table, so a table for payroll data would show, amongst other fields, amount paid in January 2021, February 2021, March 2021, April 2021, May 2021, June 2021 etc etc.... per employee.

I need to look for unique differences across a number of key fields across the various tables (monthly data). There are a few key columns, the unique identifier for each individual, and therefore each record in a table, is a field called Employee ID. Firstly I needed a query to summarize across all months data (represented in a table per month), the amount of unique payment amounts for each Employee ID. The payment column is netPay.

Query results would ideally be returned in a 'grouped and unique count' format across the tables, e.g. Employee ID, netPay unique value, unique count. So if employee ID had 11 months netPay at 2000, and 1 month pay at 2100, the results would group on employee ID, and show for employee ID XYZ, unique value 2000, a count of 11 (11 instances where the value in the netPay column was the same in 11 tables for the employee ID), and unique value 2100 a count of 1 (where the netPay value was unique/different in 1 of the tables for the employee ID).

Any ideas on the best way to approach this in Access, via a query or any reporting tools within the software? Can it be done in a single query, or would it require a few stages? Once I have an idea on how best to do it for one of the fields I need stats/unique value counts for, in this case the netPay column, I can run it over additional fields tied to employee ID to look for differences across the tables.
Log in to continue reading
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform for $9.99/mo
View membership options
Unlock 1 Answer and 7 Comments.
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
The Value of Experts Exchange in My Daily IT Life

Experts Exchange (EE) has become my company's go-to resource to get answers. I've used EE to make decisions, solve problems and even save customers. OutagesIO has been a challenging project and... Keep reading >>


Owner of Outages.IO
Phoenix, Arizona, United States
Member Since 2016
Join a full scale community that combines the best parts of other tools into one platform.
Unlock 1 Answer and 7 Comments.
View membership options
“All of life is about relationships, and EE has made a virtual community a real community. It lifts everyone's boat.”
William Peck

Member since 2004