no recent records query

[titlepusher]

I need to design a query that will show me customers that have not given me
orders after a date. I don't simply want a list of orders before a date...
but I want a list of records where the date is prior to a specific date and
no records exist for that client after that date. My table includes a
[date] field and a [customer] field (among others) and multiple records for
the same customer. So assuming a customer stopped giving me business in say
May of 2007 I want his name to come up or at least his records to and then I
can work with that data. THANK YOU, tp

 

[pietlinden]

I need to design a query that will show me customers that have not given me
orders after a date.   I don't simply want a list of orders before a date...  
but I want a list of records where the date is prior to a specific date and
no records exist for that client after that date.   My table includes a
[date] field and a [customer] field (among others) and multiple records for
the same customer.   So assuming a customer stopped giving me business in say
May of 2007 I want his name to come up or at least his records to and thenI
can work with that data.   THANK YOU, tp

use an outer join instead of an inner join (the default).
right-click on the join between the two tables in the QBE grid.
Select "Show all records from ..." and select your Customer table, and
matching records from the Invoices table. then you should be able to
filter etc and it'll work.

 

[titlepusher]

Right now it is only one table.. of invoices.. (this data came from a old old
system) but I can strip out customer numbers and build a new table and filter
to get only one occurance and build a customer table with matching customer
numbers to those in the invoice file.. and I can set up the outer
join... but then how do I handle the date question...

thx, tp

I need to design a query that will show me customers that have not given me
orders after a date. I don't simply want a list of orders before a date...
but I want a list of records where the date is prior to a specific date and
no records exist for that client after that date. My table includes a
[date] field and a [customer] field (among others) and multiple records for
the same customer. So assuming a customer stopped giving me business in say
May of 2007 I want his name to come up or at least his records to and then I
can work with that data. THANK YOU, tp

use an outer join instead of an inner join (the default).
right-click on the join between the two tables in the QBE grid.
Select "Show all records from ..." and select your Customer table, and
matching records from the Invoices table. then you should be able to
filter etc and it'll work.

 

[John W. Vinson]

Right now it is only one table.. of invoices.. (this data came from a old old
system) but I can strip out customer numbers and build a new table and filter
to get only one occurance and build a customer table with matching customer
numbers to those in the invoice file.. and I can set up the outer
join... but then how do I handle the date question...

A NOT EXISTS clause will do that:

WHERE orderdate <= [Enter date:] AND NOT EXISTS
(SELECT CustomerID FROM Orders AS X WHERE X.CustomerID = Customers.CustomerID
AND X.Orderdate > [Enter date:])

John W. Vinson [MVP]

 

[John Spencer]

Field and table names would help us to build a query that will work.

Get a list of all customers
QueryOne:
SELECT DISTINCT Customer
FROM YourTable

Get a list of all customers who have an order after the cutoff date
QueryTwo:
SELECT DISTINCT Customer
FROM YourTable
WHERE [Date] >= #2007/05/01#

QueryThree: Uses the above two queries to get customers that have no
order after May 1, 2007

SELECT QueryOne.Customer
FROM QueryOne LEFT JOIN QueryTwo
On QueryOne.Customer = QueryTwo.Customer
WHERE QueryTwo.Customer is Null

IF your table and field names consist of only letters and numbers and
underscores (and are not reserved words) you can do that all in one
query by using 2 sub-queries in the FROM clause.



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================