Finding records with no entry within past year

[bg17067 via AccessMonster.com]

I have two tables [Tbl_Customers] and [Tbl_Orders] these are linked by
[CustID]. When orders are entered into [Tbl_Orders] a [Date of Order] field
is populated with current date.

I am trying to write a query that will show what customers have not placed an
order within the past 365 days. I have an expression that will show orders
within a year {Between Date() And (Date()-365)} but that's as far as I can
take it.

Thanks,

 

[Allen Browne]

See:
Subquery basics: Identifying what is NOT there
at:
http://allenbrowne.com/subquery-01.html

The first example is close to what you need.

 

[bg17067 via AccessMonster.com]

Allen,

Worked like a charm! Thank you for the quick reply.

See:
Subquery basics: Identifying what is NOT there
at:
http://allenbrowne.com/subquery-01.html

The first example is close to what you need.

I have two tables [Tbl_Customers] and [Tbl_Orders] these are linked by
[CustID]. When orders are entered into [Tbl_Orders] a [Date of Order]

[quoted text clipped - 7 lines]

within a year {Between Date() And (Date()-365)} but that's as far as I can
take it

 

[bg17067 via AccessMonster.com]

Is there a way to show the last [Date of Order] with this subquery?

SELECT Tbl_Customers.AccountNumber, Tbl_Customers.Name
FROM Tbl_Customers
WHERE NOT EXISTS
(SELECT Tbl_Orders.[Order #]
FROM Tbl_Orders
WHERE Tbl_Orders.AccountNumber = Tbl_Customers.AccountNumber
AND Tbl_Orders.[Date of Order] > Date() - 365);


I've looked at the examples on the website but haven't figured out how to tie
two subqueries together.

Thanks,
Brian

See:
Subquery basics: Identifying what is NOT there

[quoted text clipped - 8 lines]

 

[Allen Browne]

See:
http://www.mvps.org/access/queries/qry0020.htm

Michel Walsh discusses 4 approaches.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Is there a way to show the last [Date of Order] with this subquery?

SELECT Tbl_Customers.AccountNumber, Tbl_Customers.Name
FROM Tbl_Customers
WHERE NOT EXISTS
(SELECT Tbl_Orders.[Order #]
FROM Tbl_Orders
WHERE Tbl_Orders.AccountNumber = Tbl_Customers.AccountNumber
AND Tbl_Orders.[Date of Order] > Date() - 365);


I've looked at the examples on the website but haven't figured out how
to tie two subqueries together.

Thanks,
Brian

See:
Subquery basics: Identifying what is NOT there

[quoted text clipped - 8 lines]

within a year {Between Date() And (Date()-365)} but that's as far
as I can take it