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
<
[email protected]> a écrit dans le message de (e-mail address removed)...
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
WHERE (OrderFormsSent BETWEEN Date()-21 AND Date()-1000) OR
IsNull(OrderFormsSent)
<
[email protected]> a écrit dans le message de (e-mail address removed)...
Also sorry if I wanted it to grab not only the records that have a value
of
betweeen date()-21 and date()-1000 but also with nothing in
the field what would I put in?
Thanks, your help is greatly appreciated.
Regards,
Conrad
--------
Thanks Warrio it works a treat!
Just a question though, when I paste it in, save the query then reopen
it, access changes some of the symbols, then gives me a 'syntax error
in FROM clause'.
I've pasted what it turns it into... any idea why access would do this
and how I would prevent it happening?
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.
Conrad
Hi Conrad!
What you can do is to filter your table 'OrderFormsSent ' by selecting
only
the latest date for each [Customer Number]
and then join it to the rest of data you want to display:
SELECT Customers.[Customer Number], Customers.[Business Name],
Customers.AddressState, Customers.[Street Address 1], Customers.[Street
Address 2], Customers.City, Customers.Postcode, Customers.SendOrderForm
INTO CustomerstoprintNT
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
Hi,
I've already got some help for this, got a lot further but still not
working 100%.
I have a customer table that is linked to another table
'tblOrderFormsSent' (one-many) with a record of each time we send an
order form.
I want my query to pick the latest date from the tblOrderFormsSent
table and check to see if it is between Date()-21 and Date-1000.
I can't seem to get the Where clause in my nested query to use the
result of the max function and use it in the between section.
SELECT Customers.[Customer Number], Customers.[Business Name],
Customers.AddressState, Customers.[Street Address 1],
Customers.[Street
Address 2], Customers.City, Customers.Postcode,
Customers.SendOrderForm
INTO CustomerstoprintNT
FROM Customers INNER JOIN tblOrderFormsSent ON Customers.[Customer
Number]=tblOrderFormsSent.[Customer Number]
WHERE Customers.AddressState="NSW" AND Customers.SendOrderForm=Yes
AND
tblOrderFormsSent.OrderFormsSent In (SELECT
Max(tblOrderFormsSent.[OrderFormsSent]) AS MaxSentNo FROM
tblOrderFormsSent WHERE 'MaxSentNo' BETWEEN Date()-21 AND
Date()-1000);
Can anyone help with this?
Thanks!
