Microsoft Access - Group By and Ignore null values - HotUKDeals
We use cookie files to improve site functionality and personalisation. By continuing to use HotUKDeals, you accept our cookie and privacy policy.
Get the HotUKDeals app free at Google Play

Search Error

An error occurred when searching, please try again!

Login / Sign UpSubmit

Microsoft Access - Group By and Ignore null values

£0.00 @
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 c… Read More
allsa001 Avatar
4m, 3w agoPosted 4 months, 3 weeks ago
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?
allsa001 Avatar
4m, 3w agoPosted 4 months, 3 weeks ago
Options

All Responses

(1) Jump to unreadPost an answer
Responses/page:
#1
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 an Answer

You don't need an account to leave a response. Just enter your email address. We'll keep it private.

...OR log in with your social account

...OR comment using your social account

Thanks for your comment! Keep it up!
We just need to have a quick look and it will be live soon.
The community is happy to hear your opinion! Keep contributing!