Zero Activity Query

[lumpy2497]

I currently have 2 tables going:

Table 1: Customers
Fields: Account (join field)
First Name
Last Name


Table 2: Transactions
Fields: Account (join field)
Date
Payment (money that comes in)
Fee (invoice that is charged)


I need to find those customers that do not have any transactions (no payments, no fees) for a certain date period (ie entire month of January)

I have see "sql not exist", "minus", "outer join" for sql statements, but most of my work is in Access 2000.

Any help is greatly appreciated!!

Thanks ahead of time.

Leeann...

 

[KeithG]

Try this

Select Customers.[First Name],Customers.[Last Name] From Customers Left Join Transactions On Customers.Account=Transactions.Account Where (Transactions.Date between [Start Date] and [End Date]) And isnull(Transactions.Account)=True

 

[lumpy2497]

Didn't Work

Keith...

Here is what I tweaked to make the tables and fields correct for my database, and it doesn't work. When I change the ISNull to "False", it gives me all the transactions that I know have happened, which should happen. When the ISNull is "true" query is run, there should be about 50 or so records that should appear, especially for January 2007 or any other time period, and they don't.

SELECT [Client Info].FirstName, [Client Info].LastName
FROM [Client Info] LEFT JOIN Transactions ON [Client Info].Account = Transactions.Account
WHERE (((Transactions.Date) Between [Start Date] And [End Date]) AND ((IsNull([Transactions].[Account]))=True));


Leeann...

 

[lumpy2497]

Did a bunch of research and finally found a SQL Statement that solved my problem. For those interested, here it is:

SELECT Account, FirstName, LastName
FROM [Client Info]
WHERE NOT EXISTS
(SELECT Account
FROM Transactions
WHERE (Transactions.Account LIKE [Client info].Account) AND (Transactions.Date > [start]))
ORDER BY [Client Info].Account;


Leeann...