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.