Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Puzzler (Access97-SR2)

    <P ID="del"><FONT SIZE=-1>Post deleted by jazman2001</FONT>

  2. #2
    Lounger
    Join Date
    Jan 2001
    Location
    Slough, Berkshire, England
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Puzzler (Access97-SR2)

    I assume that you have a table of customers and a table of jobs, with the table of jobs having the customer number as one of the fields.
    If that is the case you can create two queries
    First query lists all jobs that are not complete (Status <> 5)
    Create the second query that links the table of customers to this query of active jobs.
    Set the link so that it includes all records from the Customer table and only those records from the Active Jobs Query where joined fields are equal.
    Then set a criteria on the Job field of IS NULL, this will then list all Customers who do not appear in the list of customers with active jobs.
    I hope that is clear

    David

  3. #3
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Puzzler (Access97-SR2)

    <font color=blue>"Then set a criteria on the Job field of IS NULL, this will then list all Customers who do not appear in the list of customers with active jobs."</font color=blue>

    Everything is clear except that part.
    I understand the 1 to many relationship based on the customer table and active jobs query.
    I did it, but it didn't work for some reason.

    I was looking into something like a subselect query where the customer would be distinct.
    (e.g.)
    SELECT DISTINCT project.CustID
    FROM project
    WHERE (((project.CustID) Not In (Select project.custID
    from project
    where statusID IN (1,2,3,4))) AND ((project.StatusID)=5));

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Puzzler (Access97-SR2)

    Here's an example of a group by query that gives you something like that. It is returning the customerIDs for customers whose orders have all been shipped. I based this on the Northwind Orders table so it would be be easy to test:

    SELECT Orders.CustomerID, (Count([OrderID])=(select count(O.orderID) from orders as O where O.customerID = Orders.customerID)) AS Completed
    FROM Orders
    GROUP BY Orders.CustomerID, IsDate([ShippedDate])
    HAVING ((((Count([OrderID])=(select count(O.orderID) from orders as O where O.customerID = Orders.customerID)))=True));

    Maybe this will give you some ideas.
    Charlotte

  5. #5
    Lounger
    Join Date
    Jan 2001
    Location
    Slough, Berkshire, England
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Puzzler (Access97-SR2)

    The two queries are as follows
    SELECT tblJobs.Job, tblJobs.Customer
    FROM tblJobs
    WHERE (((tblJobs.Status)<>"5"));

    and
    SELECT tblCustomers.[Customer No], qryActiveJobs.Job
    FROM tblCustomers LEFT JOIN qryActiveJobs ON tblCustomers.[Customer No] = qryActiveJobs.Customer
    WHERE (((qryActiveJobs.Job) Is Null));

    The Is Null will show only those records where the job number is missing, i.e. where there is a customer from the first table without any corresponding row in the table of active jobs.

  6. #6
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Puzzler (Access97-SR2)

    Actually, mine works out fine.
    I do however love to surround myself with alternatives
    so I'll try out your submissions.
    Thanks to you both.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •