Query to find records not between two dates (1 Viewer)

[Tangawizi]

Hi,

I'd be very grateful if someone could point me in the right direction regarding this query. I have a table consisting of people's details and another which tracks payments they've made. I'm trying to design a report which shows the people who *haven't* made a payment between 2 specified dates. Creating a simple query using the 2 tables linked with the person's ID field, and with the criteria Not Between [date 1] and [date 2] as the criteria doesn't return the expected results.

I was thinking that it may be better to use a query to make a table with the people who *have* paid between those dates and then subtract them from the remainder to yield those who haven't paid.

I'm not sure if this makes sense or is a good idea and if it is, how to do it. Or maybe there's a better way?

Thanks for any ideas.

 

[jdraw]

This sounds reasonable

I was thinking that it may be better to use a query to make a table with the people who *have* paid between those dates and then subtract them from the remainder to yield those who haven't paid.

However, make absolutely sure you are looking at Active Customers. The caution is to ignore Customers in your Customers table who are not active --you don't want to be sending invoice notification or payment due notices to those Customers who are not current, but are still in your Customers table.

 

[Tangawizi]

Noted, thanks.

Two things I'm not sure about: 1. how to check for and delete the temporary table on subsequent runs of the report; and 2. how to 'subtract' one table from another.

 

[Gasman]

Not Between works for me?
SELECT tblDaily.DailyDate, tblDaily.MealTypeFK
FROM tblDaily
WHERE (((tblDaily.DailyDate) Not Between Date() And Date()-3))
ORDER BY tblDaily.DailyDate;

 

[The_Doc_Man]

My only other suggestion is this: The BETWEEN operator is equivalent to PMTDATE >= lower date AND PMTDATE <= upper date. You could surely reverse the inequalities to PMTDATE < lower date OR PMTDATE > upper date. The trick is to know DeMorgan's theorem having to do with changing the AND to OR when you reverse the sense of combined comparisons.

And just for understanding, if it had been comparison OR comparison and you reversed the sense of both comparisons, you change OR to AND.

 

[Tangawizi]

Not Between works for me?
SELECT tblDaily.DailyDate, tblDaily.MealTypeFK
FROM tblDaily
WHERE (((tblDaily.DailyDate) Not Between Date() And Date()-3))
ORDER BY tblDaily.DailyDate;

View attachment 109403

The trouble is that is also returns records of people who paid on dates outside the selected range.

 

[jdraw]

Perhaps you should tell us in plain English what you want to accomplish and why Date1 and Date2 are so significant.

If you have a Table of Current Active Customers, and a Table of Payments by Customer, then that should be the basis of a potential report.

 

[ebs17]

SQL is calculation of quantities. Here:
Active people minus people who paid in the period

PARAMETERS
   parStart Date,
   parEnd Date
;
SELECT
   P.PersonID,
   P.LastName
FROM
   (
      SELECT
         PersonID,
         LastName
      FROM
         tblPersons
      WHERE
         active = true
   ) AS P
      LEFT JOIN
         (
            SELECT
               PersonIDf
            FROM
               tblPayments
            WHERE
               PaymentDay BETWEEN parStart AND parEnd
         ) AS X
         ON P.PersonID = X.PersonIDf
WHERE
   X.PersonIDf IS NULL

@Gasman: For longer periods, almost every payment will not fall within your narrow period, including those made two or three years ago. So you cannot effectively identify active non-payers with this.

 

[Gasman]

The trouble is that is also returns records of people who paid on dates outside the selected range.

Yes, but the key asked here was NOT between two dates?

 

[Gasman]

PARAMETERS
   parStart Date,
   parEnd Date
;
SELECT
   P.PersonID
FROM
   (
      SELECT
         PersonID
      FROM
         tblPersons
      WHERE
         active = true
   ) AS P
      LEFT JOIN
         (
            SELECT
               PersonIDf
            FROM
               tblPayments
            WHERE
               PaymentDay BETWEEN parStart AND parEnd
         ) AS X
         ON P.PersonID = X.PersonIDf
WHERE
   X.PersonIDf IS NULL

@Gasman: For longer periods, almost every payment will not fall within your narrow period, including those made two or three years ago. So you cannot effectively identify active non-payers with this.

Not saying that is not the case, just showing that NOT BETWEEN works perfectly well.
If you cannot define your logic correctly, then nothing is going to work correctly.

I tested that on my diabetes DB, and as you say it produced all records except from Date -3 which should be 08/08/23 and I displayed that it stopped at 07/08/23.

 

[Tangawizi]

Yes, but the key asked here was NOT between two dates?

Yes, but the query returns all the people who haven't paid between the date range (great, this is what I want), but also all the people who have paid outside the range, which I don't want.

 

[LarryE]

Yes, but the query returns all the people who haven't paid between the date range (great, this is what I want), but also all the people who have paid outside the range, which I don't want.

 

[The_Doc_Man]

Yes, but the query returns all the people who haven't paid between the date range (great, this is what I want), but also all the people who have paid outside the range, which I don't want.

This makes it sound like you have more complex selection criteria than originally requested. Like maybe a payment due date IS between two limiting dates, but the corresponding payment is NOT between the two dates. Or like the payment due date is one of the limiting dates and a month later is the critical date... or something similar.

Please tell us the full criteria for selection.

 

[cheekybuddha]

Did you try Eberhard's suggestion in Post #8 ?

 

[jdraw]

I think Tangawizi is a new member and new to database/Access. So I think this is an opportunity to introduce this OP some basics, concepts, analysis, clarity in posting....

 

[ebs17]

I find @Tangawizi task description is very clear and therefore immediately usable. In order to use his own designations, he would also have had to provide a query or the relationship picture.
His proposed alternative approach is absolutely correct and shows an already existing deeper understanding of set operations.
If @Tangawizi still dares to try out an existing concrete implementation, a further step would have been taken.

 

[Tangawizi]

This makes it sound like you have more complex selection criteria than originally requested. Like maybe a payment due date IS between two limiting dates, but the corresponding payment is NOT between the two dates. Or like the payment due date is one of the limiting dates and a month later is the critical date... or something similar.

Please tell us the full criteria for selection.

In a nutshell, I'm trying to create a report showing who hasn't made a payment between a selected date range, that's it.

 

[Gasman]

I think you need to identify who should have made a payment in that period, and then use a sub query to identify those who did make a payment.
The sub query would be for NOT IN of the main query.

 

[ebs17]

NOT IN ... is the worst link in practice because index use is not possible.

 

[theDBguy]

Hi @Tangawizi. Welcome to AWF!

Are you able to share a sample copy of your db with test data?