Access 97 Query Question

Factual Questions
1

I’m running Office 97 on Windows XP Home. This is a database for a small business. I’m self-taught in Access and am still studying. I’m sure anyone experienced in Access would laugh like heck at my database, but it (usually) does what I need it to do.

I have one table of customers which contains their basic information (name, address, phone number, etc.). I have a table for services which contains the date of service, type of service, charge, etc. These tables are linked by a customer number field. Customers have multiple service dates associated with their customer number.

I am attempting to find out who among my current customers do not have a service date in 2008 via query.

Let’s say Customer Doe received service on 1/15/2006, 2/15/2007, and 3/18/2008. A query written with Service Date is Not Between #01/01/2008# and #12/31/2008# still shows Customer Doe because there are records in the service table for 2006 and 2007 that the query returns.

Am I asking for the impossible here?

2

You want a find unmatched query with an additional date criterion. I think this should work:

SELECT customers.CustNum, customers.Custname, customers.CustAddress, servicehistory.CustNum
FROM customers LEFT JOIN servicehistory ON customers.CustNum = servicehistory.CustNum
WHERE (((Year([ServiceDate]))=2008) AND ((servicehistory.CustNum) Is Null));

3

…or if you prefer that in visual form, it looks like this

4

Thank you!!! I think I understand what you’re saying well enough to give this a shot.

5

Don’t forget it’s a left join - you change the join properties by clicking on the link line in the query designer (you perhaps already know this)

6

Hmm. That returned 0 results, which I know isn’t correct. Here’s the SQL view:
SELECT Customers2006.[Cust #], Customers2006.LastName, Customers2006.FirstName, Customers2006.[Company Name]
FROM Customers2006 LEFT JOIN [Service Table] ON Customers2006.[Cust #] = [Service Table].CustNo
WHERE ((([Service Table].CustNo) Is Null) AND ((Year([Service Date]))=2008))

When I removed the service date part, it did return records from the customer database that had no records at all in the service table, so this seems to be at least on the right track.

7

Here’s what you need. This can’t be represented in the visual designer, so you’ll have to enter it in the SQL view:

SELECT customers.custNum, customers.custName, servicehistory.serviceDate
FROM customers LEFT JOIN servicehistory ON ((customers.custNum = servicehistory.custNum) AND (Year([serviceDate])=2008))
WHERE servicehistory.custNum Is Null;

The missing piece was that the date parameter needs to be part of the JOIN, not part of the WHERE.

8

hmmmm… You’re right - I should have tested it.

I’m sure there is a way to do it in a single query, but here’s how to do it in two:

create the first query that selects that subset of records in the services history table pertaining to the year in question:

SELECT servicehistory.*
FROM servicehistory
WHERE (((Year([serviceDate]))=2008));

  • save this as Query1 (or whatever)

Create another query to find customers who don’t appear in Query1:

SELECT customers.*
FROM customers LEFT JOIN Query1 ON customers.custNum = Query1.CustNum
WHERE (((Query1.CustNum) Is Null));

ETA: Nice one, Troutman - I never knew you could do that.

9

Oh, wow. That appears to have done the trick (ETA: Troutman’s suggestion.)

Thank you so much, both of you!!!

My next self-assigned project is going to be to figure out why that worked. :smiley:

But I also really like the query on a query suggestion and will definitely be filing that away for future reference.

10

You’re welcome. An attempt to explain why in simple terms: think of the order of operations from algebra. The JOIN is evaluated first, then the WHERE (this isn’t entirely accurate behind the scenes, but it’s good enough for us).

In the first query, we joined the tables on the foreign key only. Because it’s an outer join, we get all customer records whether or not they have a service record. If there is no service record for a customer, service.custNum is NULL in our query results, along with every other service field. This is the basis for an exclusion query - we look for records where a field in the joined table is NULL, and that tells us what records in customer don’t have a service record.

In our first query, the WHERE clause was looking for service records where the date was 2008 AND the custNum was null. That will always result in 0 records - if there was no service record, obviously there’s no date.

The second query works because we join on the FK and the date. So again we get all customer records, but this time they are joined to service records in 2008 only. Now our WHERE clause can easily find those customer records that are missing a service record in 2008.

If this doesn’t make sense, the easiest way to see it is to do a SELECT * and remove the WHERE clause in both queries. Run them both and compare the results, and you’ll see why it works.

As for the two query approach, some purists will say it is inefficient. In reality, that approach has its place. Depending on the query engine, the execution plan might end up being the same in both cases, so you can’t definitively say the two-query approach has poorer performance. And you might find that in terms of re-use, you have other needs for a query that returns all service requests in 2008. It might also be easier to understand, therefore easier to maintain and less prone to bugs. This is a pretty simple example so it might not be the case here, but you are right to store this knowledge for potential future use.

11

Thank you for the explanation. I’m not certain I understand it completely, but it did help me understand more, and I think playing around with it will help me get a better grasp of what’s actually going on.