Show all names in first column and insert zero where no record exists (1 Viewer)

[guinness]

Hi There

I'm sure I've seen this on here before but can't think how to word a search for it.

I'm tracking the holiday entitlement of a team of people. I use a query to work out how much unbooked holiday they have to take.

My problem is where I'm scheduling next year my query returns the names of those who have booked a holiday and their remaining entitelement. That's as it should be. However if someone hasn't yet booked any holidays then it simply doesn't display their record. I would like it to treat that record as zero and show the remaining entitlement as a full years entitlement.

Here's the SQL
SELECT Employees.Trainer_Name, Sum([2015 Holiday].[2015 Days]) AS [SumOf2015 Days], Employees.Holiday_Days, [Employees]![Holiday_Days]-[SumOf2015 Days] AS 2015
FROM [2015 Holiday] INNER JOIN Employees ON [2015 Holiday].Trainer_Name = Employees.Trainer_Name
GROUP BY Employees.Trainer_Name, Employees.Holiday_Days;

The problem here is that the Sum of 2015 holiday is Null

Any quick work arounds or do I somehow need to create 0 hours records?

Any help appreciated

 

[Mile-O]

Looking at that SQL my first concern is that it looks like you have a table called [2015 Holiday]. If so, why? Surely a single Holidays table covering every possible year would be easier to work with?

 

[guinness]

Yep. It's a messy work around that probably highlights my lack of skill. It's actually a query for each holiday year 2014-2020 filtered between >31/04/xxxx and <01/04/xxxx. That gives me the amount of days taken for each year. The query I pasted sums the amount of days the employee has taken. So if the first query doesn't have any data to return for one of the team then the second query won't see that person.

The sql of the first query is:
SELECT Resourcing.Start_Date, Resourcing.Trainer_Name, Time.Hours AS [2015 Hours], [2015 Hours]/7.4 AS [2015 Days]
FROM (Resourcing INNER JOIN Employees ON Resourcing.Trainer_Name = Employees.Trainer_Name) INNER JOIN [Time] ON Resourcing.Duration = Time.Time
WHERE (((Resourcing.Start_Date)>#3/31/2015# And (Resourcing.Start_Date)<#4/1/2016#) AND ((Resourcing.Activity) Like "*Holiday*"))
ORDER BY Resourcing.Start_Date;

I actually knew that that wasn't the way to go about it but it was simple if tedious to do.

 

[Mile-O]

Quick example, which may be of some help in signalling a direction to take.

 

[guinness]

Really appreciate you doing that thanks Mile-0 but it still leaves me with the same issue.

Your sample db has a team of ten people but the query only shows two of them who have booked holidays. I would like to see all ten names with the Entitlement showing their entitelement and the Days Used showing zero for the eight members who have not booked a holiday.

I hope that makes sense?

 

[guinness]

Oops I was typing my reply when your second example came in. Let me take a look as it looks like you've solved it.

Two minutes

 

[Mile-O]

Yeah, I was a little hasty in my first example and subsequently deleted it.

 

[guinness]

Hi Mile-0

I just can't make that work with the structure of my database. I don't have different entitlements for each year so the entitlement is a fixed number taken from my employee table. Trainer_Name links to my employee table as well but I get the sum of the days taken (includes half days) from the query that I want to also display zeros.

Back to the drawing board

Thank you for trying

Guinness:banghead:

 

[Mile-O]

As i said, hasty example with little thought put into it.

However, would be helpful if you therefore gave some information on your actual table structure and, if any, your thinking behind it.

 

[namliam]

Wouldnt it be as easy as:

SELECT Employees.Trainer_Name
     , Sum([b]nz([/b][2015 Holiday].[2015 Days])[b])[/b] AS [SumOf2015 Days]
     , Employees.Holiday_Days, [Employees]![Holiday_Days]-[SumOf2015 Days] AS 2015
FROM Employees 
[b]Left join[/b] [2015 Holiday] ON Employees.Trainer_Name = [2015 Holiday].Trainer_Name
GROUP BY Employees.Trainer_Name, Employees.Holiday_Days;

I am going to ignore your naming convention which makes my skin crawl and leave it to Mile-O to help you fix your yearly query problem

 

[guinness]

Hi Mile-O

Thanks for taking anothr look. I'm going into a meeting for an hour but will get back to you straight after.

Cheers

Guinness

 

[guinness]

Hi Namliam

That worked. Thanks very much.

Believe me the naming convention is probably the least of this db's problems but, whilst it may be messy, It mostly works. I'm on a time scale which is why I've had to take the simplest most basic route rather than do a bit of research to find a better, quicker more robust way.

Your help is much appreciated

Guinness

 

[guinness]

With hindsight I probably shouldn't have used queries at all. I should have created some Dlookups in vba against my main table. We live and learn

 

[namliam]

HELL NO, you never use DLookups, a single DLookup here or there sure.... exceptions break the rules.

Multiple DLookups, HELL NO!

 

[guinness]

That's quite a strong reaction to Dlookup. Have you had a bad experience in the past?

What's wrong with them anyway?

 

[vbaInet]

That's quite a strong reaction to Dlookup. Have you had a bad experience in the past?

They had a fallout!

 

[namliam]

People overuse any Domain function, once they start using them.
Not realizing that each domain function is a query upon itself, 10 DLookups = 10 Queries.
One on those queries can (in larger DBs) take seconds or even longer if you Dlookup or DSum on a query....

Runaway processes/queries that run hours because of it, where a proper subselect or query can do it in a minute or less.

 

[guinness]

Duly noted namliam although as vbaInet will tell you my ability to identify the wrong solution to almost any situation is now becoming legendary

 

[guinness]

Oh and what is a subselect query

 

[namliam]

A query inside a query, either in a join or in the where.

Also, forgot to mention, if you do Domain functions inside a query your domain function will be executed for each line, even if it only takes one second.... for 10.000 records it racks up in time