Show Invoices Greater Than One Year

  • Thread starter Thread starter Lirva S via AccessMonster.com
  • Start date Start date
L

Lirva S via AccessMonster.com

I have the code below in a query. The first part of the code is to get the
last invoice date for each customer - that part works fine. What I also want
to do in addition to that is to show only invoices that are older than one
year.

Can someone tell me what I'm doing incorrectly.

Thanks.

((SELECT MAX([InvoiceDate]) FROM [tblInvoice] WHERE [tblCustomer].[CustomerID]
= [tblInvoice].[I_CustomerID]) Or Is Null) And >=DateAdd("y",-1,Date())
 
Simplest correction first:
Use "yyyy" not "y" YYYY= year; y = Day of year

This seems as if it might be a sub-query in a where clause. If so, please
post the entire query so people can see what is looks like.

Also, explain what you are trying to test with Or Is Null. That should
normally have a field name between "or" and "is Null"

Perhaps what you want is:
(SELECT MAX([InvoiceDate])
FROM [tblInvoice]
WHERE [tblCustomer].[CustomerID]= [tblInvoice].[I_CustomerID]
AND InvoiceDate >=DateAdd("yyyy",-1,Date()))
 
The code you provided returned the correct answer to the question I asked.
However, I noticed that I needed something else from the query. Some
customers have more than one Invoice, what I would like is the query to
eliminate the customer altogether if ONE or more invoice is dated within a
year.

I hope that isn't confusing.

Here's the SQL.

SELECT tblCustomer.SchoolName, tblInvoice.InvoiceDate, tblCustomer.Address,
tblCustomer.City, tblCustomer.Phone, tblCustomer.Ext, tblCustomer.Email,
tblCustomer.Fax
FROM tblCustomer INNER JOIN tblInvoice ON tblCustomer.CustomerID = tblInvoice.
I_CustomerID
WHERE (((tblInvoice.InvoiceDate)=(SELECT MAX([InvoiceDate]) FROM [tblInvoice]
WHERE [tblCustomer].[CustomerID]= [tblInvoice].[I_CustomerID] AND InvoiceDate
<=DateAdd("yyyy",-1,Date()))))
ORDER BY tblInvoice.InvoiceDate;

Thanks in advance.

------------------------------------------------------------------------------
------------------------

John said:
Simplest correction first:
Use "yyyy" not "y" YYYY= year; y = Day of year

This seems as if it might be a sub-query in a where clause. If so, please
post the entire query so people can see what is looks like.

Also, explain what you are trying to test with Or Is Null. That should
normally have a field name between "or" and "is Null"

Perhaps what you want is:
(SELECT MAX([InvoiceDate])
FROM [tblInvoice]
WHERE [tblCustomer].[CustomerID]= [tblInvoice].[I_CustomerID]
AND InvoiceDate >=DateAdd("yyyy",-1,Date()))
I have the code below in a query. The first part of the code is to get the
last invoice date for each customer - that part works fine. What I also
Click to expand...
[quoted text clipped - 9 lines]
[tblCustomer].[CustomerID]
= [tblInvoice].[I_CustomerID]) Or Is Null) And >=DateAdd("y",-1,Date())
Click to expand...
Click to expand...
 
One way to do this would be to use a not exists clause. The problem is that Not
Exists is often slow.

SELECT tblCustomer.SchoolName, tblInvoice.InvoiceDate, tblCustomer.Address,
tblCustomer.City, tblCustomer.Phone, tblCustomer.Ext, tblCustomer.Email,
tblCustomer.Fax
FROM tblCustomer INNER JOIN tblInvoice ON tblCustomer.CustomerID = tblInvoice.
I_CustomerID
WHERE (((tblInvoice.InvoiceDate)=(SELECT MAX([InvoiceDate]) FROM [tblInvoice]
WHERE [tblCustomer].[CustomerID]= [tblInvoice].[I_CustomerID] AND InvoiceDate
<=DateAdd("yyyy",-1,Date()))))
AND Not Exists
(SELECT * FROM
tblINVOICE as Temp
WHERE Temp.I_CustomerID = tblCustomer.CustomerID
AND Temp.InvoiceDate > DateAdd("yyyy",-1,Date()))
ORDER BY tblInvoice.InvoiceDate


If that is too slow, then there are other methods - embedding a query in the
FROM clause with an outer join and then testing for Null results to be returned.


Lirva S via AccessMonster.com said:
The code you provided returned the correct answer to the question I asked.
However, I noticed that I needed something else from the query. Some
customers have more than one Invoice, what I would like is the query to
eliminate the customer altogether if ONE or more invoice is dated within a
year.

I hope that isn't confusing.

Here's the SQL.

SELECT tblCustomer.SchoolName, tblInvoice.InvoiceDate, tblCustomer.Address,
tblCustomer.City, tblCustomer.Phone, tblCustomer.Ext, tblCustomer.Email,
tblCustomer.Fax
FROM tblCustomer INNER JOIN tblInvoice ON tblCustomer.CustomerID = tblInvoice.
I_CustomerID
WHERE (((tblInvoice.InvoiceDate)=(SELECT MAX([InvoiceDate]) FROM [tblInvoice]
WHERE [tblCustomer].[CustomerID]= [tblInvoice].[I_CustomerID] AND InvoiceDate
<=DateAdd("yyyy",-1,Date()))))
ORDER BY tblInvoice.InvoiceDate;

Thanks in advance.

------------------------------------------------------------------------------
------------------------

John said:
Simplest correction first:
Use "yyyy" not "y" YYYY= year; y = Day of year

This seems as if it might be a sub-query in a where clause. If so, please
post the entire query so people can see what is looks like.

Also, explain what you are trying to test with Or Is Null. That should
normally have a field name between "or" and "is Null"

Perhaps what you want is:
(SELECT MAX([InvoiceDate])
FROM [tblInvoice]
WHERE [tblCustomer].[CustomerID]= [tblInvoice].[I_CustomerID]
AND InvoiceDate >=DateAdd("yyyy",-1,Date()))
I have the code below in a query. The first part of the code is to get the
last invoice date for each customer - that part works fine. What I also
Click to expand...
[quoted text clipped - 9 lines]
[tblCustomer].[CustomerID]
= [tblInvoice].[I_CustomerID]) Or Is Null) And >=DateAdd("y",-1,Date())
Click to expand...
Click to expand...
Click to expand...
 
This method doesn't seem to work. I was thinking that if I eliminate the
customers who have at least ONE invoice that is less than a year then from
the result I can create another query - I think that may work.

Can you or someone help me create that query.

This is the SQL:
--------------------------------------------------
SELECT tblCustomer.SchoolName, tblInvoice.InvoiceDate, tblCustomer.Address,
tblCustomer.City, tblCustomer.Phone, tblCustomer.Ext, tblCustomer.Email,
tblCustomer.Fax
FROM tblCustomer INNER JOIN tblInvoice ON tblCustomer.CustomerID = tblInvoice.
I_CustomerID
ORDER BY tblInvoice.InvoiceDate;


John said:
One way to do this would be to use a not exists clause. The problem is that Not
Exists is often slow.

SELECT tblCustomer.SchoolName, tblInvoice.InvoiceDate, tblCustomer.Address,
tblCustomer.City, tblCustomer.Phone, tblCustomer.Ext, tblCustomer.Email,
tblCustomer.Fax
FROM tblCustomer INNER JOIN tblInvoice ON tblCustomer.CustomerID = tblInvoice.
I_CustomerID
WHERE (((tblInvoice.InvoiceDate)=(SELECT MAX([InvoiceDate]) FROM [tblInvoice]
WHERE [tblCustomer].[CustomerID]= [tblInvoice].[I_CustomerID] AND InvoiceDate
<=DateAdd("yyyy",-1,Date()))))
AND Not Exists
(SELECT * FROM
tblINVOICE as Temp
WHERE Temp.I_CustomerID = tblCustomer.CustomerID
AND Temp.InvoiceDate > DateAdd("yyyy",-1,Date()))
ORDER BY tblInvoice.InvoiceDate

If that is too slow, then there are other methods - embedding a query in the
FROM clause with an outer join and then testing for Null results to be returned.
The code you provided returned the correct answer to the question I asked.
However, I noticed that I needed something else from the query. Some
Click to expand...
[quoted text clipped - 41 lines]
[tblCustomer].[CustomerID]
= [tblInvoice].[I_CustomerID]) Or Is Null) And >=DateAdd("y",-1,Date())
Click to expand...
Click to expand...
Click to expand...
 
I'm not sure what you are trying to do.

My understanding was that you wanted to get customers that have not had an
invoice after period 2/2/05
but have had at least one invoice prior to 2/1/05. Then you want to return
all records for that customer that have an invoice date before 2/2/05 (or
whatever the cutoff is)

SELECT tblCustomer.SchoolName, tblInvoice.InvoiceDate, tblCustomer.Address,
tblCustomer.City, tblCustomer.Phone, tblCustomer.Ext, tblCustomer.Email,
tblCustomer.Fax
FROM tblCustomer INNER JOIN tblInvoice
ON tblCustomer.CustomerID = tblInvoice. I_CustomerID
WHERE tblInvoice.InvoiceDate <= DateAdd("yyyy",-1,Date())
AND Not EXISTS
(SELECT * FROM
tblINVOICE as Temp
WHERE Temp.I_CustomerID = tblCustomer.CustomerID
AND Temp.InvoiceDate > DateAdd("yyyy",-1,Date()))
ORDER BY tblInvoice.InvoiceDate;

You could also do this with two queries:
Query One - saved as QLatestInvoiceInYear
SELECT tblCustomer.CustomerID
FROM tblCustomer INNER JOIN tblInvoice
ON tblCustomer.CustomerID = tblInvoice.I_CustomerID
WHERE InvoiceDate > DateAdd("yyyy",-1,Date())

Second query:
SELECT tblCustomer.SchoolName, tblInvoice.InvoiceDate, tblCustomer.Address,
tblCustomer.City, tblCustomer.Phone, tblCustomer.Ext
FROM (tblCustomer INNER JOIN tblInvoice
ON tblCustomer.CustomerID = tblInvoice.I_CustomerID)
LEFT JOIN QLatestInvoiceInYear As Q
ON Q.CustomerID = tblCustomer.CustomerID
WHERE Q.CustomerID is Null AND
InvoiceDate <=DateAdd("yyyy",-1,Date())

Lirva S via AccessMonster.com said:
This method doesn't seem to work. I was thinking that if I eliminate the
customers who have at least ONE invoice that is less than a year then from
the result I can create another query - I think that may work.

Can you or someone help me create that query.

This is the SQL:
--------------------------------------------------
SELECT tblCustomer.SchoolName, tblInvoice.InvoiceDate,
tblCustomer.Address,
tblCustomer.City, tblCustomer.Phone, tblCustomer.Ext, tblCustomer.Email,
tblCustomer.Fax
FROM tblCustomer INNER JOIN tblInvoice ON tblCustomer.CustomerID =
tblInvoice.
I_CustomerID
ORDER BY tblInvoice.InvoiceDate;


John said:
One way to do this would be to use a not exists clause. The problem is
that Not
Exists is often slow.

SELECT tblCustomer.SchoolName, tblInvoice.InvoiceDate,
tblCustomer.Address,
tblCustomer.City, tblCustomer.Phone, tblCustomer.Ext, tblCustomer.Email,
tblCustomer.Fax
FROM tblCustomer INNER JOIN tblInvoice ON tblCustomer.CustomerID =
tblInvoice.
I_CustomerID
WHERE (((tblInvoice.InvoiceDate)=(SELECT MAX([InvoiceDate]) FROM
[tblInvoice]
WHERE [tblCustomer].[CustomerID]= [tblInvoice].[I_CustomerID] AND
InvoiceDate
<=DateAdd("yyyy",-1,Date()))))
AND Not Exists
(SELECT * FROM
tblINVOICE as Temp
WHERE Temp.I_CustomerID = tblCustomer.CustomerID
AND Temp.InvoiceDate > DateAdd("yyyy",-1,Date()))
ORDER BY tblInvoice.InvoiceDate

If that is too slow, then there are other methods - embedding a query in
the
FROM clause with an outer join and then testing for Null results to be
returned.
The code you provided returned the correct answer to the question I
asked.
However, I noticed that I needed something else from the query. Some
Click to expand...
[quoted text clipped - 41 lines]
[tblCustomer].[CustomerID]
= [tblInvoice].[I_CustomerID]) Or Is Null) And
=DateAdd("y",-1,Date())
Click to expand...
Click to expand...
Click to expand...
 
Hi John, Please bear with me.

Yes, I want to display customers who have not had an invoice in the past year.
But what is happening is if customer “John Brown” had an invoice in Jan. 2004,
and Dec. 2005 he is still showing up in the result because he had an invoice
in Jan. 2004. I want to eliminate him from the result altogether.

Hope that helps.


John said:
I'm not sure what you are trying to do.

My understanding was that you wanted to get customers that have not had an
invoice after period 2/2/05
but have had at least one invoice prior to 2/1/05. Then you want to return
all records for that customer that have an invoice date before 2/2/05 (or
whatever the cutoff is)

SELECT tblCustomer.SchoolName, tblInvoice.InvoiceDate, tblCustomer.Address,
tblCustomer.City, tblCustomer.Phone, tblCustomer.Ext, tblCustomer.Email,
tblCustomer.Fax
FROM tblCustomer INNER JOIN tblInvoice
ON tblCustomer.CustomerID = tblInvoice. I_CustomerID
WHERE tblInvoice.InvoiceDate <= DateAdd("yyyy",-1,Date())
AND Not EXISTS
(SELECT * FROM
tblINVOICE as Temp
WHERE Temp.I_CustomerID = tblCustomer.CustomerID
AND Temp.InvoiceDate > DateAdd("yyyy",-1,Date()))
ORDER BY tblInvoice.InvoiceDate;

You could also do this with two queries:
Query One - saved as QLatestInvoiceInYear
SELECT tblCustomer.CustomerID
FROM tblCustomer INNER JOIN tblInvoice
ON tblCustomer.CustomerID = tblInvoice.I_CustomerID
WHERE InvoiceDate > DateAdd("yyyy",-1,Date())

Second query:
SELECT tblCustomer.SchoolName, tblInvoice.InvoiceDate, tblCustomer.Address,
tblCustomer.City, tblCustomer.Phone, tblCustomer.Ext
FROM (tblCustomer INNER JOIN tblInvoice
ON tblCustomer.CustomerID = tblInvoice.I_CustomerID)
LEFT JOIN QLatestInvoiceInYear As Q
ON Q.CustomerID = tblCustomer.CustomerID
WHERE Q.CustomerID is Null AND
InvoiceDate <=DateAdd("yyyy",-1,Date())
This method doesn't seem to work. I was thinking that if I eliminate the
customers who have at least ONE invoice that is less than a year then from
Click to expand...
[quoted text clipped - 48 lines]
= [tblInvoice].[I_CustomerID]) Or Is Null) And
=DateAdd("y",-1,Date())
Click to expand...
Click to expand...
Click to expand...
 
I think you are trying to identify old customers that have not ordered since
some cut-off date (for example since January 1, 2005)

Ok. Small steps. Does the following query list all the customers that you
want to eliminate?
Query One - saved as QLatestInvoiceInYear
SELECT tblCustomer.CustomerID, TblInvoice.InvoiceDate
FROM tblCustomer INNER JOIN tblInvoice
ON tblCustomer.CustomerID = tblInvoice.I_CustomerID
WHERE InvoiceDate > DateAdd("yyyy",-1,Date())

The date range may need to be adjusted if by past year you actually mean the
entire year of 2005 only or since January 1 of the prior year

Where InvoiceDate Between DateSerial(Year(Date())-1,1,1) and
DateSerial(Year(Date())-1,12,31)
or
Where InvoiceDate >= DateSerial(Year(Date())-1,1,1)

If so, then you should be able to save that query. And then use it to
eliminate those customers from the results of your query.
Second query:
SELECT tblCustomer.SchoolName, tblInvoice.InvoiceDate, tblCustomer.Address,
tblCustomer.City, tblCustomer.Phone, tblCustomer.Ext
FROM (tblCustomer INNER JOIN tblInvoice
ON tblCustomer.CustomerID = tblInvoice.I_CustomerID)
LEFT JOIN QLatestInvoiceInYear As Q
ON Q.CustomerID = tblCustomer.CustomerID
WHERE Q.CustomerID is Null AND
InvoiceDate <=DateAdd("yyyy",-1,Date())

Lirva S via AccessMonster.com said:
Hi John, Please bear with me.

Yes, I want to display customers who have not had an invoice in the past
year.
But what is happening is if customer "John Brown" had an invoice in Jan.
2004,
and Dec. 2005 he is still showing up in the result because he had an
invoice
in Jan. 2004. I want to eliminate him from the result altogether.

Hope that helps.


John said:
I'm not sure what you are trying to do.

My understanding was that you wanted to get customers that have not had an
invoice after period 2/2/05
but have had at least one invoice prior to 2/1/05. Then you want to
return
all records for that customer that have an invoice date before 2/2/05 (or
whatever the cutoff is)

SELECT tblCustomer.SchoolName, tblInvoice.InvoiceDate,
tblCustomer.Address,
tblCustomer.City, tblCustomer.Phone, tblCustomer.Ext, tblCustomer.Email,
tblCustomer.Fax
FROM tblCustomer INNER JOIN tblInvoice
ON tblCustomer.CustomerID = tblInvoice. I_CustomerID
WHERE tblInvoice.InvoiceDate <= DateAdd("yyyy",-1,Date())
AND Not EXISTS
(SELECT * FROM
tblINVOICE as Temp
WHERE Temp.I_CustomerID = tblCustomer.CustomerID
AND Temp.InvoiceDate > DateAdd("yyyy",-1,Date()))
ORDER BY tblInvoice.InvoiceDate;

You could also do this with two queries:
Query One - saved as QLatestInvoiceInYear
SELECT tblCustomer.CustomerID
FROM tblCustomer INNER JOIN tblInvoice
ON tblCustomer.CustomerID = tblInvoice.I_CustomerID
WHERE InvoiceDate > DateAdd("yyyy",-1,Date())

Second query:
SELECT tblCustomer.SchoolName, tblInvoice.InvoiceDate,
tblCustomer.Address,
tblCustomer.City, tblCustomer.Phone, tblCustomer.Ext
FROM (tblCustomer INNER JOIN tblInvoice
ON tblCustomer.CustomerID = tblInvoice.I_CustomerID)
LEFT JOIN QLatestInvoiceInYear As Q
ON Q.CustomerID = tblCustomer.CustomerID
WHERE Q.CustomerID is Null AND
InvoiceDate <=DateAdd("yyyy",-1,Date())
This method doesn't seem to work. I was thinking that if I eliminate
the
customers who have at least ONE invoice that is less than a year then
from
Click to expand...
[quoted text clipped - 48 lines]
= [tblInvoice].[I_CustomerID]) Or Is Null) And
=DateAdd("y",-1,Date())
Click to expand...
Click to expand...
Click to expand...
 
John, I want to thank you for your patience and the time you took to explain
it bit by bit. I finally got what I wanted from the query.

Thanks again!

John said:
I think you are trying to identify old customers that have not ordered since
some cut-off date (for example since January 1, 2005)

Ok. Small steps. Does the following query list all the customers that you
want to eliminate?
Query One - saved as QLatestInvoiceInYear
SELECT tblCustomer.CustomerID, TblInvoice.InvoiceDate
FROM tblCustomer INNER JOIN tblInvoice
ON tblCustomer.CustomerID = tblInvoice.I_CustomerID
WHERE InvoiceDate > DateAdd("yyyy",-1,Date())

The date range may need to be adjusted if by past year you actually mean the
entire year of 2005 only or since January 1 of the prior year

Where InvoiceDate Between DateSerial(Year(Date())-1,1,1) and
DateSerial(Year(Date())-1,12,31)
or
Where InvoiceDate >= DateSerial(Year(Date())-1,1,1)

If so, then you should be able to save that query. And then use it to
eliminate those customers from the results of your query.
Second query:
SELECT tblCustomer.SchoolName, tblInvoice.InvoiceDate, tblCustomer.Address,
tblCustomer.City, tblCustomer.Phone, tblCustomer.Ext
FROM (tblCustomer INNER JOIN tblInvoice
ON tblCustomer.CustomerID = tblInvoice.I_CustomerID)
LEFT JOIN QLatestInvoiceInYear As Q
ON Q.CustomerID = tblCustomer.CustomerID
WHERE Q.CustomerID is Null AND
InvoiceDate <=DateAdd("yyyy",-1,Date())
Hi John, Please bear with me.
Click to expand...
[quoted text clipped - 56 lines]
= [tblInvoice].[I_CustomerID]) Or Is Null) And
=DateAdd("y",-1,Date())
Click to expand...
Click to expand...
Click to expand...
 
Back
Top