CODEWITHSUNDEEP
sqlms-accessjoinsubqueryms-access-2010
CodeMed
CodeMed

Reputation: 9201

filtering records based on two other tables

I need to generate a list of active customers in an MS Access database. All past and present customers are stored in a customers table. However, the criteria for determining active status needs to be derived from two other tables: intake and exit. A customer is considered active if they have an intake date which does not have an exit date after it. However, to confuse things, a former customer who has an exit date can become a customer again by getting a new intake date.

Here are the relevant parts of the structure of the three tables that store this information: 

customers table  
    customerID  
    name  

intake table  
    intakeID  
    customerID  
    intakeDate  

exit date  
    exitID  
    customerID  
    exitDate

A customer can have multiple intake records and multiple exit records. So the pseudocode for the SQL statement needs to look something like: 

SELECT customerID, name FROM customers  
WHERE ((most recent intakeDate)>(most recent exitDate(if any)))

What should this look like in real SQL? For an MS Access 2010 database. Obviously, joins are necessary. But what types of joins? And how does it need to look?

Upvotes: 0

Views: 138

Answers (3)

Linger
Linger

Reputation: 15058

SELECT ActiveCustomers.*, tblAddress.* FROM 
(
  SELECT customers.name, customers.customerID,
  (
    SELECT COUNT(intakeDate) 
    FROM intake 
    WHERE customers.customerID = intake.customerID AND Len(intakeDate & '') > 0
  ) AS IntakeCount,
  (
    SELECT COUNT(exitDate) 
    FROM exit 
    WHERE customers.customerID = exit.customerID AND Len(exitDate & '') > 0
  ) AS ExitCount
  FROM customers 
) AS ActiveCustomers
INNER JOIN tblAddress ON ActiveCustomers.customerID = tblAddress.customerID
WHERE IntakeCount > ExitCount
AND tblAddress.CurrentAddress = True

Upvotes: 1

Yawar
Yawar

Reputation: 11627

I like @sqlgrl's approach of just looking at the most recent intake and exit for each customer, but I have adapted it to use MS Access-specific syntax and also, I think, tightened up the join logic a little bit:

select c.*
from ([customers table] as c
inner join (
  select customerID, max(exitDate) as lastOut
  from [exit date]
  group by customerID
) as [out]
on c.customerID = [out].customerID)
inner join (
  select customerID, max(intakeDate) as lastIn
  from [intake table]
  group by customerID
) as [in]
on c.customerID = [in].customerID
where [in].lastIn > [out].lastOut

The above basically says:

  • Build a list of each customer's most recent exit date
  • Build a list of each customer's most recent intake date
  • Join the two lists with the customers table
  • If the customer's most recent intake date is after their most recent exit date, include this customer in the final output

Upvotes: 1

sqlgrl
sqlgrl

Reputation: 66

select * from 
    (SELECT  CustomerID, Name, MAX(intakeDate) AS intakeDate, MAX(exitDate) AS exitDate
    FROM  customerstable 
    INNER JOIN intaketable 
    ON customerID = customerID 
    INNER JOIN  exitdate 
    ON intaketable.customerID = exitdate.customerID
    GROUP BY dbo.customerstable.CustomerID ) c
where intakeDate > exitDate

Upvotes: 1

Related Questions