Need a query to list clients without jobs

I have what I thought is a simple requirement, but the solution is alluding me.

I have a table tblClients.  This links to a table tblJobs via the ClientID.
tblClients has a RenewalDate.
tblJobs has a DateJobReceived.

So I want a list of all clients who are registered (ie RenewalDate > 1/4/18) [say]..
.. who do not have any Jobs since their renewal date (ie count of jobs with DateJobReceived > 1/4/18 = zero)

Can anyone suggest a query that would give me this list please.

Regards

Richard
rltomalinAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mbizupCommented:
Try this:
SELECT * 
FROM  tblClients c INNER JOIN 
(SELECT COUNT(JobTableID) AS TotalJobs, ClientID FROM tblJobs  WHERE DateJobReceived > #1/4/18# GROUP BY ClientID) q  
ON c.ClientID = q.ClientID
WHERE q.TotalJobs = 0 

Open in new window


Or this:
SELECT * 
FROM  tblClients c INNER JOIN 
(SELECT COUNT(JobTableID) AS TotalJobs, ClientID FROM tblJobs  WHERE DateJobReceived > #1/4/18# GROUP BY ClientID HAVING q.TotalJobs = 0) q  
ON c.ClientID = q.ClientID

Open in new window

0
mbizupCommented:
Correction to that second suggestion:

SELECT * 
FROM  tblClients c INNER JOIN 
(SELECT COUNT(JobTableID) AS TotalJobs, ClientID FROM tblJobs  WHERE DateJobReceived > #1/4/18# GROUP BY ClientID HAVING COUNT(JobTableID) = 0) q  
ON c.ClientID = q.ClientID

Open in new window

0
John TsioumprisSoftware & Systems EngineerCommented:
A bit more generic
SELECT * 
FROM  Clients,(SELECT Max(DateJobReceived) as LastJobDate , ClientID FROM Jobs GROUP BY ClientID) as Jobs
WHERE Clients.ClientId = Jobs.ClientID AND Clients.RenewalDate > Jobs.LastJobDate 

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

xiao jinshouCommented:
My understanding of your tblClients has a unique row for each ClientID or there is a flag to indicate whether the ClientID is active.  So I write the query below:

create table #tblClients (ClientID INT, RenewalDate Date)
insert into #tblClients values
                         (1,'2017-12-01')
                  ,(2,'2018-02-03')
                  ,(3,'2018-03-01')

create table #tblJobs (ClientID INT, DateJobReceived Date)

insert into #tblJobs values
                         (1,'2018-01-01')
                  ,(1,'2018-01-02')
                  ,(2,'2018-02-01')
                  ,(2,'2018-02-02')
                  ,(2,'2018-02-03')
                  ,(3,'2018-03-02')


;With
  TMPClientsJobs As
      (SELECT
                 tblClients.ClientID
                  ,RenewalDate
              ,DateJobReceived
              ,ROW_NUMBER() OVER (PARTITION BY tblClients.ClientID ORDER BY tblClients.RenewalDate DESC) rn
       FROM #tblClients tblClients
       INNER JOIN #tblJobs tblJobs
         ON tblClients.ClientID = tblJobs.ClientID
      WHERE RenewalDate >= '2018-01-04' AND DateJobReceived >= RenewalDate
        )
,TMPWithJobs As
  (SELECT
    ClientID
   ,RenewalDate
   ,DateJobReceived
  FROM TMPClientsJobs
  WHERE rn = 1)

 SELECT DISTINCT
    tblClients.ClientID
   ,tblClients.RenewalDate
 FROM #tblClients tblClients
 WHERE tblClients.ClientID NOT IN (SELECT ClientID FROM TMPWithJobs)

drop table #tblClients
drop table #tblJobs
0
xiao jinshouCommented:
Or simply:

create table #tblClients (ClientID INT, RenewalDate Date)
insert into #tblClients values
             (1,'2017-12-01')
                  ,(2,'2018-02-03')
                  ,(3,'2018-03-01')

create table #tblJobs (ClientID INT, DateJobReceived Date)

insert into #tblJobs values
             (1,'2018-01-01')
                  ,(1,'2018-01-02')
                  ,(2,'2018-02-01')
                  ,(2,'2018-02-02')
                  ,(2,'2018-02-03')
                  ,(3,'2018-03-02')


;With
  TMPClientsJobs As
      (SELECT
             tblClients.ClientID
          ,RenewalDate
              ,DateJobReceived
              ,ROW_NUMBER() OVER (PARTITION BY tblClients.ClientID ORDER BY tblClients.RenewalDate DESC) rn
       FROM #tblClients tblClients
         INNER JOIN #tblJobs tblJobs
         ON tblClients.ClientID = tblJobs.ClientID
         WHERE RenewalDate >= '2018-01-04' AND DateJobReceived >= RenewalDate
        )

 SELECT DISTINCT
    tblClients.ClientID
   ,tblClients.RenewalDate
 FROM #tblClients tblClients
 WHERE tblClients.ClientID NOT IN (SELECT ClientID FROM TMPClientsJobs WHERE rn = 1)

drop table #tblClients
drop table #tblJobs
0
rltomalinAuthor Commented:
Thanks for getting back so promptly.  Unfortunately I cannot get any of these solutions to work (probably my own inexperience with SQL)

Just to clarify that my Client and Job IDs actually have a space in the name, so I have changed the code in the suggestions to match that.

The code I am now using therefore is....

FROM mbizip
SELECT *
FROM  tblClients c INNER JOIN
(SELECT COUNT([Job ID]) AS TotalJobs, [Client ID] FROM tblJobs  WHERE DateReceived > #1/4/18# GROUP BY [Client ID]) q  
ON c.[Client ID] = q.[Client ID]
WHERE q.TotalJobs = 0


SELECT *
FROM  tblClients c INNER JOIN
(SELECT COUNT([Job ID]) AS TotalJobs, [Client ID] FROM tblJobs  WHERE DateReceived > #1/4/18# GROUP BY [Client ID] HAVING COUNT([Job ID]) = 0) q  
ON c.[Client ID] = q.[Client ID]

Neither of these produce anything


FROM John
SELECT *
FROM  tblClients,(SELECT Max(DateReceived) as LastJobDate , [Client ID] FROM tblJobs GROUP BY [Client ID]) as Jobs
WHERE tblClients.[Client Id] = tblJobs.[Client ID] AND tblClients.RenewalDate > Jobs.LastJobDate

This does produce an output, but far too many (over 200).  I think there is a problem with this as there is no check that the client is registered (ie RenewalDate > Date())

But if I change the last bit of code to look for that rather than > Jobs.LastJobDate it returns what looks like the total of registered clients without the check for jobs created since renewal (which I think is as would be expected - about 120 jobs).
If I then add a further AND statement at the end - tblClients.RenewalDate > Jobs.LastJobDate it doesn't make any difference - still shows the number of current registered clients.

Sorry this is a bit rambling.  I am having a bit of trouble following this.  Any help would be appreciated.

Regards
Richard
0
John TsioumprisSoftware & Systems EngineerCommented:
Without sample data i am afraid we will be only guessing
0
rltomalinAuthor Commented:
I'm sorry I can't follow the suggestion from xiao jinshou.
I'm not sure that it targets my problem.
0
John TsioumprisSoftware & Systems EngineerCommented:
Nothing is stopping you from adding one more condition
SELECT * 
FROM  tblClients,(SELECT Max(DateReceived) as LastJobDate , [Client ID] FROM tblJobs GROUP BY [Client ID]) as Jobs
WHERE tblClients.[Client Id] = tblJobs.[Client ID] AND tblClients.RenewalDate > Jobs.LastJobDate AND tblClients.RenewalDate>Date()

Open in new window

0
xiao jinshouCommented:
My logic is to use Common Table Expression (CTE), which is to use one  (or more) temp tables to do a query.  

Please see my second reply of the query.  The first temp table TMPClientsJobs list all the ClientIDs who was registered and had a latest job after Jan 4, 2018.  Then the final query lists the ClientIDs who are not in the table who has a job after Jan 4, 2018.

Please try to run it.

- Xiao
0
rltomalinAuthor Commented:
Thanks for all the help.  I THINK I have figured out the problem (I think my fault).  I will look at it tomorrow and feedback.

Regards

Richard
0
mbizupCommented:
Xiao (and to clear up some confusion for Richard),

The topic area here is MS Access (and I believe the database in question is as well).  CTE's do not apply here, and Temp tables should *generally* be avoided as they tend to bloat Access databases.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
My logic is to use Common Table Expression (CTE),
This is in the Microsoft Access topic area. Access doesn't use CTEs.
0
rltomalinAuthor Commented:
Thank you very much, this is now fixed.  The code was fine.  I did modify it a bit but the problem that I was having yesterday was entirely my own fault.
I was confusing myself with my own terminology.  I have the field RenewalDate which gets modified when the client renews (by adding 1 year to it).  But then I was looking at this current issue and regarding the RenewalDate as the date they renewed.  What I did was to just subtract 1 year (using DateAdd) from RenewalDate in the SQL.

Thanks a lot.

Best regards
Richard
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.