Hi Conrad,

The reason why you have only one record is because of the GROUP BY clause.
the subQuery returns only the maximum date that are between date-21
and -1000 and do not double the end result in the main query.

however if you want to double the customer name, you can do that:
===========================================
SELECT Customers.[Customer Number], Customers.[Business Name],
Customers.[AddressState], Customers.[Street Address 1],
Customers.[Street Address 2],
Customers.[City], Customers.[Postcode], Customers.[SendOrderForm]
INTO customerstoprintNSW
FROM Customers INNER JOIN

(SELECT [Customer Number], MAX(tblOrderFormsSent.OrderFormsSent) AS MaxDate
FROM tblOrderFormsSent
WHERE (OrderFormsSent BETWEEN Date()-21 AND Date()-1000) GROUP BY [Customer
Number], OrderFormsSent) AS tblOrderFormsSent

ON Customers.[Customer Number]=tblOrderFormsSent.[Customer Number]
WHERE Customers.AddressState = 'NSW' AND Customers.SendOrderForm
===========================================
in the query above, I've added the OrderFormsSent field to the group by
clause. because it is every time diffrent, you will have as many result as
many dates you have into this table. HOWEVER you might have a form that was
sent many times in the same day. in this case, they will all be grouped into
one field because they are diffrent. so it would be better to use an ID that
unique for each record instead.

but isn't it better to display the customer that have sent a form between
the two date, how many time have they sent a form between this date and the
maxDate? if you want to do that, then:
===========================================
SELECT Customers.[Customer Number], Customers.[Business Name],
Customers.[AddressState], Customers.[Street Address 1],
Customers.[Street Address 2],
Customers.[City], Customers.[Postcode],  tblOrderFormsSent .MaxDate,
tblOrderFormsSent .nbSent
Customers.[SendOrderForm]
INTO customerstoprintNSW
FROM Customers INNER JOIN

(SELECT [Customer Number], MAX(tblOrderFormsSent.OrderFormsSent) AS MaxDate,
Count(ID) AS nbSent FROM tblOrderFormsSent
WHERE (OrderFormsSent BETWEEN Date()-21 AND Date()-1000) GROUP BY [Customer
Number]) AS tblOrderFormsSent

ON Customers.[Customer Number]=tblOrderFormsSent.[Customer Number]
WHERE Customers.AddressState = 'NSW' AND Customers.SendOrderForm
===========================================
good luck

Hi Warrio,
Thanks for your help so far, i've learnt a lot.

I've copied the query into vb code as you recommended and am running it
from there.  The problem I have is that the query seems to not be
getting the maximum date value out of the tblOrderFormsSent table.  For
example, if the customer has any entry in the
tblOrderFormsSent.OrderFormsSent field that is between the values -21
and -1000, it will select that customers record...

so customer with only 20/08/06  - no record returned
with only 20/06/08 - 1 record returned
with both 20/08/06, 20/06/08 - 1 record returned  (not what i want)

is it because the first WHERE is looking up the field name rather than
the value returned by the max function?

I've listed the query i'm using below.
SELECT Customers.[Customer Number], Customers.[Business Name],
Customers.[AddressState], Customers.[Street Address 1],
Customers.[Street Address 2],
Customers.[City], Customers.[Postcode], Customers.[SendOrderForm]
INTO customerstoprintNSW
FROM Customers INNER JOIN (SELECT [Customer Number] ,
MAX(tblOrderFormsSent.OrderFormsSent)
AS OrderFormsSent FROM tblOrderFormsSent WHERE (OrderFormsSent BETWEEN
Date()-21 AND Date()-1000)
GROUP BY [Customer Number]) AS tblOrderFormsSent ON
Customers.[Customer Number]=tblOrderFormsSent.[Customer Number]
WHERE Customers.AddressState = 'NSW' AND Customers.SendOrderForm = Yes

Thanks again,
Conrad

Warrio wrote: