Query with some sort of sum? (1 Viewer)

fat controller · Nov 18, 2017

I have a table (now stuffed full with data), which is serving its purpose quite nicely - however, someone has made a request for data from that table, but in a different way that has thrown me a bit of a curveball.

Each record contains a number of fields, but the key ones for this purpose will be:

SDYear (contains a four digit number to represent a calendar year)
EmployeeNumber (unique identifier for a member of staff)
NumAccidents (contains the number of accidents they have had, if any - null, if they have had none)

Isolating those staff who have not had an accident in a given year is dead easy (Where NumAccidents Is Null), however what I can't quite work out is how to isolate staff who have NEVER had an accident. Some of these records go back to the mid 1960's (I am not kidding!), with a record per year.

Just to complicate things that wee bit more, I would like to be able to pull a report showing all of these staff together, which I can then sort in order based on their start date.

Each person's history is dealt with on a continuous form which shows all of their records at the same time, so I did toy with the idea of totalling up the NumAccidents field, and then either putting the total into a field in a separate table (I could feasibly add a field into the Employees table) via VBA/SQL, but that would mean that each and every person's record would have to be revisited, and the code triggered somehow just to update this one field - - and there are literally thousands of staff...... you will understand my reluctance to do it this way.

Could this be done within a query?

arnelgp · Nov 18, 2017

create a Total query and Sum( NumofAccidents), create a Criteria Where Sum(NumofAccidents)=0.

fat controller · Nov 18, 2017

Hmmm.... The total query isn't totalling, but I suspect that might be due to the fact that 0 does not exist, but rather the field is null?

fat controller · Nov 18, 2017

Here is a screenshot

isladogs · Nov 18, 2017

Why group by year and id if all you want is an overall sum?
Just group by employeeID.

Also if you use Count rather than Sum you will get zero values rather than nulls

fat controller · Nov 18, 2017

I don't need to group by year - in fact, for the purposes of this, I don't even need to show the year. I have changed it to count as suggested, which is now giving zero values, and removed SDYear, but it is still not totalling them up.

In Design View all the other fields Totals line shows Group By - should this not be the case?

isladogs · Nov 18, 2017

Please post the query SQL.

fat controller · Nov 18, 2017

Code:

SELECT SafeDriving.ID, SafeDriving.EmployeeNumber, SafeDriving.EmployeeName, SafeDriving.StartDate, SafeDriving.Garage, SafeDriving.NumDaysEmp, Count(SafeDriving.NumAccidents) AS CountOfNumAccidents, SafeDriving.NumAbsence, SafeDriving.Qualifying, SafeDriving.RateThisYear, SafeDriving.DisqualReason, SafeDriving.SDYear
FROM SafeDriving
GROUP BY SafeDriving.ID, SafeDriving.EmployeeNumber, SafeDriving.EmployeeName, SafeDriving.StartDate, SafeDriving.Garage, SafeDriving.NumDaysEmp, SafeDriving.NumAbsence, SafeDriving.Qualifying, SafeDriving.RateThisYear, SafeDriving.DisqualReason, SafeDriving.SDYear;

I accept that I am probably being as thick as mud on this......

isladogs · Nov 18, 2017

You're adding too many fields so its listing each record
This should be all you need

Code:

SELECT EmployeeNumber, Count(NumAccidents) AS CountOfNumAccidents
FROM SafeDriving
GROUP BY EmployeeNumber;

NOTE: you don't need to preface each item with the table name as you're only using 1 table

fat controller · Nov 18, 2017

You sir, are a true gentleman! Thank you

I am now off to raise a glass to your health.

isladogs · Nov 18, 2017

Drink another for me.
I've gave up a few months ago & rely on surrogates now for my drinking

fat controller · Nov 18, 2017

With pleasure - I chucked the cigarettes nearly 20 years ago, so I don't feel too bad about allowing myself a glass of wine or three at a weekend (especially homemade)

Having said that, tomorrow I may raise a glass of malt - a lovely gift from a very happy person who's laptop I rebuilt after it had a mug of coffee spilt over it.....

Thanks again for your help

Gasman · Nov 18, 2017

I can help you out there

ridders said:
Drink another for me.
I've gave up a few months ago & rely on surrogates now for my drinking

isladogs · Nov 18, 2017

Stop it .... its malt whisky that I miss most ....
Beer & wine I hardly miss at all

Gasman · Nov 18, 2017

Urgh! I can't stand whiskey, well at least Scotch. I can and have drunk Bourbon, but very seldom these days. My mother loved Scotch, but then she was Scottish

Beer is my tipple, real ale with a few glasses of wine now and again.

So as it turns out, I can't help you much at all.

isladogs · Nov 18, 2017

Gasman said:
Urgh! I can't stand whiskey, well at least Scotch. I can and have drunk Bourbon, but very seldom these days. My mother loved Scotch, but then she was Scottish

Beer is my tipple, real ale with a few glasses of wine now and again.

So as it turns out, I can't help you much at all.

No matter. But please don't let that stop you.

Sent from my iPhone using Tapatalk

Pat Hartman · Nov 19, 2017

SELECT EmployeeNumber, Count(NumAccidents) AS CountOfNumAccidents
FROM SafeDriving
GROUP BY EmployeeNumber

This query is not giving you the count of accidents. It is giving you the count of rows where NumAccidents is not null. Therefore, if you actually need the number of accidents, this query will not produce the correct answer for anyone who had more than one accident in any year.

SELECT EmployeeNumber, Sum(NumAccidents) AS CountOfNumAccidents
FROM SafeDriving
GROUP BY EmployeeNumber;

This query will sum the not null values of NumAccidents.

isladogs · Nov 19, 2017

Hi Pat

Whilst you are of course correct in your observation, that's not an issue in this case.

If you look back to post 1, FC stated that he needed to know which employees had never had an accident.
Therefore, he is only interested in the employees with zero counts.

He also said he didn't want the solution to have null values
Hence use of Count in this case

If I had seen his db at the start I would have used Nz(Sum....,0)
Sum would normally be the correct solution

fat controller · Nov 21, 2017

As it happens, I have sort of returned to this one - the Count to only show those with no accidents seems to work just fine. However, I now have a second, very similar query where I have used Sum to add up each individuals accident totals - but, the Nz function is not working, which makes me certain that I have used it the wrong way; the SQL I have is:

Code:

SELECT SafeDriving.EmployeeNumber, SafeDriving.EmployeeName, SafeDriving.StartDate, SafeDriving.Garage, Nz(Sum(SafeDriving.NumAccidents)) AS CountOfNumAccidents
FROM LiveEmployees INNER JOIN SafeDriving ON LiveEmployees.EmployeeNumber = SafeDriving.EmployeeNumber
GROUP BY SafeDriving.EmployeeNumber, SafeDriving.EmployeeName, SafeDriving.StartDate, SafeDriving.Garage
ORDER BY SafeDriving.StartDate;

isladogs · Nov 21, 2017

You've forgotten that Nz requires an alternative value such as 0 for when the sum is null. I'd also change its alias to e.g totalAccidents

Code:

SELECT SafeDriving.EmployeeNumber, SafeDriving.EmployeeName, SafeDriving.StartDate, SafeDriving.Garage, 
Nz(Sum(SafeDriving.NumAccidents)[COLOR="Red"],0[/COLOR]) AS [COLOR="red"]TotalAccidents[/COLOR]
FROM LiveEmployees INNER JOIN SafeDriving ON LiveEmployees.EmployeeNumber = SafeDriving.EmployeeNumber
GROUP BY SafeDriving.EmployeeNumber, SafeDriving.EmployeeName, SafeDriving.StartDate, SafeDriving.Garage
ORDER BY SafeDriving.StartDate;

Also remember what I said about adding more fields than you need.
The ones I'd question here are Garage and possibly StartDate.
If an employee has been employed at more than one garage you will get multiple records per employee.
Similarly if they left & returned at a new garage.
To be a total pedant, what if they married or for some other reason changed their name.

So this might be better:

Code:

SELECT SafeDriving.EmployeeNumber, Nz(Sum(SafeDriving.NumAccidents)[COLOR="Red"],0[/COLOR]) AS [COLOR="red"]TotalAccidents[/COLOR]
FROM LiveEmployees INNER JOIN SafeDriving ON LiveEmployees.EmployeeNumber = SafeDriving.EmployeeNumber
GROUP BY SafeDriving.EmployeeNumber
ORDER BY SafeDriving.StartDate;

Now lets take it one stage further. Do you need the LiveEmployees table?
If this is just for current employees then fair enough.
Otherwise it can just be this

Code:

SELECT EmployeeNumber, Nz(Sum(NumAccidents)[COLOR="Red"],0[/COLOR]) AS [COLOR="red"]TotalAccidents[/COLOR]
FROM SafeDriving 
GROUP BY EmployeeNumber
ORDER BY StartDate;

Query with some sort of sum? (1 Viewer)

Slightly round the bend..

..forever waiting... waiting for jellybean!

Slightly round the bend..

Slightly round the bend..

Attachments

MVP / VIP

Slightly round the bend..

MVP / VIP

Slightly round the bend..

MVP / VIP

Slightly round the bend..

MVP / VIP

Slightly round the bend..

Enthusiastic Amateur

MVP / VIP

Enthusiastic Amateur

MVP / VIP

Super Moderator

MVP / VIP

Slightly round the bend..

MVP / VIP

Similar threads

Users who are viewing this thread