query for mailing lables - customers with no orders for 2 years and or more then $50

[Robert Blackwell]

I want to make a query for a mailing of all our customers who haven't
ordered from us in 2 years and I'm not sure exactly how to do that.

I can make queries that gets a list of all the customers from date created
or by their "status" but the order history is a little more complex because
there are going to be many orders for some customers and for our regular
customers who always order, they will have orders from 2 years ago but they
will still have recent orders too , at least within 6 months of today if not
many orders in between.

So, I need to make a query of all customers who haven't placed an order
after 12/31/01 or, if they orders since 12/31/02 over $50.

What kind of info can I give (cell names etc) so you can help me figure this
out.

 

[Douglas J. Steele]

Open the Query Builder, and drag the appropriate table(s) into it. Create a
query that has the appropriate indicative information fields, including the
Order Date field, but no details about what they ordered, or what the size
of the order was.

Now, convert that query to a Total query (select View | Totals from the
menu). Leave the Total row as Group By under all fields except the Order
Date field: change that row to Max. If you run the query now, you'll get one
row per customer, with the Date field showing their most recent order.

Now, in the Criteria cell under Order Date, put

<DateAdd("yyyy",-2,Date())

Run the query: it'll return the details of all customers whose last order
was 2 years or more ago. (If you really want no orders since 12/31/01,
change that to < #12/31/2001#

For your second query, create a query exactly like the one above, except
change the criteria to <DateAdd("yyyy",-1,Date()) (or to < #12/31/2002#).
Save that query. You should now be able to join this query to your order
information. Joined, it's only going to return the details of the orders
that were placed prior to the cut-off date. Put >$50 as the criteria under
the order amount, and you should have everything for which you're looking.

 

[Robert Blackwell]

Okay, I converted the query into totals (that's cool I didn't know about
this) but the format we use for date created is ie. 11/13/2000

so when I run the query it's still showing duplicates for customers...I
tried changing the criterea to <DateAdd("dd/mm/yyyy",-2,Date()) but that
resulted in an "invalid procedure call".

 

[Douglas J. Steele]

Don't change the DateAdd from what I put. DateAdd("yyyy", -2, Date())
subtracts 2 years from today's date. You don't need to worry about what
format you use: it's irrelevant when you use a date function to return the
value.

If it's showing duplicates, you're probably including too many fields in the
query. All you want is the necessary information to identify the customers.
Don't include any invoice information other than the order date.

By the way, even if your short date format has been set to dd/mm/yyyy, you
still must put the dates in mm/dd/yyyy format in queries when you're using
the # delimiters.

 

[Robert Blackwell]

Okay, so, from the first query, it returned dupes because I had 2 fields
from the orderheader table date and totalamount. so when I just added the
date it worked fine (no dupes).

But now (if I read right) you said (in your first reply), in my second
query, copy the first, but add the totalamount field and put >50?
When I do this, I get dupes.

I'm not following when you say join either so maybe that's my problem?

Also, if I wanted to get the max order date < #12/31/2002# but not older
then 1/8/2002
This would be on the second query where I want to get orders from 2002 over
$50
How could I write that in the criterea

And lastly, after running both queries, I want to change their status to
inactive (so insert a 2 into the status field instead of a 1 which is
already there)

 

[Douglas J. Steele]

That's not quite what I said.

What I said was make a query identical to the first one, but change the date
criteria. That should be the only change you make to the query. Save the
query. Let's call it QueryA. Now, I assume you already have a query that
returns invoices and invoice amounts, that we'll call QueryB. Create a brand
new query, and add QueryA and QueryB to it. Join the two queries by dragging
the InvoiceDate and the CustomerID (or whatever your fields are) between the
two tables, so that a solid line appears. Now, drag the relevant fields from
QueryB to the grid. When you run that query, it should give you the invoice
details for each invoice returned by QueryA. Put a dollar criteria under the
total field, and you should be done.

 

[Robert Blackwell]

http://www.wowcentral.com/random/query.gif

_qryinactivecustomers is the query I made to get all the customers with no
orders in 2 years. Grouped by orderdate "max" as you suggested. Works like a
glove. If anything this is more important then the money query, but I still
wanna get it to work.
(I didn't know what you meant by joining because I was modifying an existing
query and I'm still a little new with this. The query had these tables
automatically joined and I was just taking that for granted.)
Also, I didn't know you could join queries. (I did a search in the help for
join and found it)

So, after joining the first query to table order header and dragging the
fields I want to display, running the query creates dupes cause of all the
orders each customer has.. So, now I guess I need to know which item to set
the group by to what so I don't get the dupes. I tried setting the
totalamountdue's total to max, but that returned no results... Also, is this
going to return customers who've never spent more then 50 or just if one of
their orders is less then 50?

Sorry for being so dense.

 

[Douglas J. Steele]

Thanks for the image (and for putting it on the web, rather than posting it
to the newsgroup!)

_qryinactive<$50 shouldn't be a Totals query. As well, you need to join the
Customer information from _qryinactivecustomer to tblOrderHdr in addition to
the Date information. Since I can't figure out how you identify a customer
from what's shown in the query, I can't suggest the exact field(s) that need
to be joined.

_qryinactivecustomer is supposed to give you the date of the last order each
customer placed. _qryinactive<$50 gets the details for each of those last
orders. In other words, it should tell you if the last order the customer
placed was less than $50. Is that what you want?

BTW, I would recommend removing the special characters <$ from the query
name. They may cause problems.

 

[Robert Blackwell]

"_qryinactivecustomer is supposed to give you the date of the last order
each
customer placed."
Yes, and itworks perfectly

"_qryinactive<$50 gets the details for each of those last
orders. In other words, it should tell you if the last order the customer
placed was less than $50. Is that what you want?"
Yes

Then I made the new query and choose the query "_qryinactivecustomer" and
table "tblorderhdr" as the source for this query.

Then I drug MaxOfOrerDate from the orig query and joined it with OrderDate
from tblOrderhdr since those are the same two fields. Isn't this what I'm
supposed to do(and shouldn't this be the only join neccessary)? In order to
filter out who's orders from the query "_qryinactivecustomer" were <50?

because I don't want to just see a list of totalamount dues...I want to see
a list of customers and their addresses so I can send them a mailing

Would it be too much trouble to ask for remote assistance?

 

[Douglas J. Steele]

Sorry: missed this over the weekend.

Then I drug MaxOfOrerDate from the orig query and joined it with OrderDate
from tblOrderhdr since those are the same two fields. Isn't this what I'm
supposed to do(and shouldn't this be the only join neccessary)? In order to
filter out who's orders from the query "_qryinactivecustomer" were <50?

No, it's not sufficient. You need to include the Customer number (or however
else you identify the customer). I'm assuming that _qryinactive<$50 has
something like a customer number in addition to MaxOfOrderDate. Drag that to
tblOrderhdr as well, so that there are (at least) two lines between the two
boxes.

What you're doing right now is retrieving all invoices from tblOrderhdr for
any date retrieved from _qryinactive<$50. What you want is, for each
customer, the exact invoice for their value of MaxOfOrderDate.