can i do this?

[Ted]

Hello,

Is there any way that I can build a query that will search for records that
have not been entered by a certain date? In other words, I'm looking for
records that don't exist yet! This is part of a rentals database, and I
need to know who's late in making payments; I have a default day of the
month that each tenant is supposed to pay by plus a 5 day grace period. I
want to be able to search the database to see what records have NOT been
entered by that day + 5 each month. Thanks in advance for any help!

 

[Brendan Reynolds \(MVP\)]

You need to look at the date of the most recent payment that *does* exist.
For example, supposing the rent is due on the first of the month ...

SELECT Customers.CustomerName, Max(Payments.PaymentDate) AS MaxOfPaymentDate
FROM Customers INNER JOIN Payments ON Customers.CustomerID =
Payments.CustomerID
GROUP BY Customers.CustomerName
HAVING
(((Max(Payments.PaymentDate))<DateSerial(Year(Date()),Month(Date()),1) Or
(Max(Payments.PaymentDate)) Is Null));

 

[Ted]

Thanks..I understand where you're coming from here, but in a case where a
newly entered tenant doesn't pay his rent right off the bat, this example
won't work, right? In other words, if there is no previous record, and the
new tenant is a deadbeat and moves into a unit due to some promotion and
then skips out on the rent the first 2 months, this method won't catch
that...? I'll keep poking around with it. Thanks again!

 

[Jeff Rush]

Actually I believe that is where the (null) part comes in to play.

Jeff

 

[Brendan Reynolds \(MVP\)]

Hi Ted,

As Jeff says, I included a test for Null in an attempt to account for
customers who haven't made any payments. However, I missed something -
customers who haven't made any payments would be excluded from the query by
the inner join, so we'll need to change that to a left join ...

SELECT Customers.CustomerName, Max(Payments.PaymentDate) AS MaxOfPaymentDate
FROM Customers LEFT JOIN Payments ON
Customers.CustomerID=Payments.CustomerID
GROUP BY Customers.CustomerName
HAVING
(((Max(Payments.PaymentDate))<DateSerial(Year(Date()),Month(Date()),1) Or
(Max(Payments.PaymentDate)) Is Null));

Come to think of it, you actually weren't too far out when you described it
as finding records that don't exist.

 

[Ted]

Looks like a winner to me. Thanks much for the help!