Microsoft Access - Group By and Ignore null values

I as wondering if anyone can help solve this problem I am having in Access.

I have a table of data which contains some conflicting data, and I want to know which specific rows of data contain the conflicting data.

For example I have a 5 employees, and want to know how many sales they made per month in 2016.
So my table columns are:
Name - Jan - Feb - Mar - Apr - May - Jun - Jul - Aug - Sep - Oct - Nov - Dec

Then each month, the salesperson submits there figures, so my table would look like this:
Name - Jan - Feb - Mar - Apr - May - Jun - Jul - Aug - Sep - Oct - Nov - Dec
Jack - 30
Jack - 30 - 20
Jack - 30 - 20 - 10
Jack - 40 - 20 - 10

As you will see there is conflicting data in Jack's Jan data, as 3 rows says there were 30 sales, but one row says there was 40 sales.

So I basically need a way of using 'Group By' on all the columns, and where there is null values, to ignore them.
Therefore, if there are two or more rows of data, I will know that there is conflicting data.

Any ideas please anyone?

1 Comment

Easiest way for you - plonk excel on the front of the table (external data) and use the filtering in excel - best way is to re-visit your schema design as that is (not meaning to be nasty) a very bad design.

Something like

Employee table (list of employee specific columns and an ID column)
MonthlySales (employeeID, Year, Month, Amount) where employeeID is the ID from the Employee table (Foreign Key relationship).

This way you have one row for each months sales for each employee in a very narrow table, rather than a table that is filled with Nulls for 50% of it. The Year column means you can just carry on using it.

You can then use a nice Pivot in Excel to flatten the data out and present it in a report type format

Please excuse the broad brush, but I deal with RDBMS's and don't touch Access

Hope it helps
Post a comment
Avatar
@
    Text
    Top Discussions
    1. USC voucher code pls?23
    2. No Vega deals around - pre-order for Vega 56?23
    3. I think I'm being scammed on Paypal?811
    4. Nintendo Switch Game Cases34

    See more discussions