query to find customers older than 60 days

[jomara via AccessMonster.com]

I have a query i am working on to show all contacts who have been in our
database for 60 days or more with no sales. my tbl fields are Rep, LastName,
FirstName, Date Created, and from another table SaleDate. My problem is
untill the customer buys there is no record in sale date so i need to use
date created how would i go about making the query default to date created if
no sales data is available

 

[Guest]

Judging by the subject line, you have some young customers!

SELECT Contacts.*
FROM Contacts LEFT JOIN Sales
ON Contacts.Rep = Sales.Rep
WHERE Sales.SaleDate Is Null
AND Contacts.[Date Created] < Date() - 60;

I'm guessing on the table names and that Rep is the primary key in one table
and the foriegn key in the other. I'm also assuming that Date Created is an
actual Date/Time field.

 

[Guest]

It's a little tricky, but can be done. You didn't give the names of your
tables so
I'll call this table Contacts
I'll call this Sales.

I ASS·U·ME that your doing a LEFT JOIN on whatever field your are using
(maybe ContactID?) to match your Contacts table with the Sales table. This
is important because we need to know when the value of SalesDate is Null.
(In query SQL view, change the Inner Join to Left Join - OR - In the query
design view, double click the join line and change so that it "Includes ALL
records from Contacts and only those from Sales where the joined fields are
equal.")

The date value that you want in your query would look something like this:
Expr1: IIf(IsNull([Sales].[SaleDate]),[Contacts].[Date
Created],[Sales].[SaleDate])

Which reads as:
If SalesDate is null then value is DateCreated else value is SalesDate.


Bryan..

 

[jomara via AccessMonster.com]

that part works great but left me with a new problem my salesdate field is a
continous form and if they buy can usually have dozens of transactions. I
have salesdate grouped to last and i tried max but it displays every
transaction not just the last

It's a little tricky, but can be done. You didn't give the names of your
tables so
I'll call this table Contacts
I'll call this Sales.

I ASS·U·ME that your doing a LEFT JOIN on whatever field your are using
(maybe ContactID?) to match your Contacts table with the Sales table. This
is important because we need to know when the value of SalesDate is Null.
(In query SQL view, change the Inner Join to Left Join - OR - In the query
design view, double click the join line and change so that it "Includes ALL
records from Contacts and only those from Sales where the joined fields are
equal.")

The date value that you want in your query would look something like this:
Expr1: IIf(IsNull([Sales].[SaleDate]),[Contacts].[Date
Created],[Sales].[SaleDate])

Which reads as:
If SalesDate is null then value is DateCreated else value is SalesDate.

Bryan..

I have a query i am working on to show all contacts who have been in our
database for 60 days or more with no sales. my tbl fields are Rep, LastName,
FirstName, Date Created, and from another table SaleDate. My problem is
untill the customer buys there is no record in sale date so i need to use
date created how would i go about making the query default to date created if
no sales data is available

 

[jomara via AccessMonster.com]

I answered my own question after i posted i set the total field in expr1 to
max and now it only returns the last transaction. thanks for the help

that part works great but left me with a new problem my salesdate field is a
continous form and if they buy can usually have dozens of transactions. I
have salesdate grouped to last and i tried max but it displays every
transaction not just the last

It's a little tricky, but can be done. You didn't give the names of your
tables so

[quoted text clipped - 28 lines]