Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old April 20th, 2005, 06:35 AM
Registered User
Join Date: Aug 2003
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default Find records that don't exist

I have been asked to create a report that will show all the properties in the Customers table that do not have any records on the Workorders table with (JobTypeID=2 AND JobStatus="0")

So far I have come up with this to show all the properties and the count of records meeting the criteria:

SELECT C.CustomerID, ([JobStatus]="0" And [JobTypeID]=2) AS Kount, Count([Kount]) AS KountComplete
FROM Workorders AS W INNER JOIN Customers AS C ON W.CustomerID = C.CustomerID
GROUP BY C.CustomerID, ([JobStatus]="0" And [JobTypeID]=2);

and it will show you the records.

I thought I would be able to use this information to run a second query and list all the records that have a count of 0 but it doesn't work. It shows you each combination with a count, eg

Customer ID Kount KountComplete
2 0
2 -1 2
2 0 6

So the above record has 2 records that have (JobTypeID=2 and JobStatus="0"), 6 that don't and 0 that come somewhere in between.

Another record is shown as

4428 0
4428 0 5

This is the sort of record I want to identify as it doesn't have any records where Kount = -1. BUT thanks to the grouping effect if I say where Kount <> -1 then it would still include CustomerID 2.

So, should I approach this another way or tweak this one?



Old April 20th, 2005, 08:16 AM
Registered User
Join Date: Aug 2003
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts

Ok, I've managed to get it. Using the query below, I then did a Right Join as shown below to list all records and then filter out those with the JobTypeID=2 and JobStatus="0":

SELECT C.CustomerID, QC.KountComplete, C.NameNumb, C.BillingAddress, C.City, C.PostalCode
FROM qryCountCompleteServices AS QC RIGHT JOIN Customers AS C ON QC.CustomerID = C.CustomerID
WHERE (((C.Owner)=6218) AND ((C.BlockCustomer)=0) AND ((QC.KountNA) Is Null));

The Owner and BlockCustomer were added after the event to further filter.

Similar Threads
Thread Thread Starter Forum Replies Last Post
Exist Finder classes to find elements into List? jeusdi .NET Framework 1.x 4 February 19th, 2007 01:31 PM
Returnig records on EXIST ninel SQL Server 2005 1 January 2nd, 2007 08:17 PM
Find records that don't exist harg7769 MySQL 0 April 28th, 2005 10:44 AM
sql command to find duplicate records? qwjunk Classic ASP Databases 1 January 31st, 2004 03:32 PM
how to find total no. of records reshmisworld Classic ASP Basics 1 July 24th, 2003 08:04 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.