Problem writing query to get latest date and action from table

C

conradtruscott

Hi,

I have two tables, one is Customers and the other is tblOrderFormsSent
which are linked via a one to many relationship. What I need to do is
get the most recent record of the date field for order forms sent and
then see if it is between certain values.

I'm trying to use the maketable query below however I get a 'you cannot
use an aggregate function in a WHERE clause'. I know I somehow need to
nest another SELECT statement in there but I'm not to sure where.
Could someone please help or point me to a good site on contructing SQL
statements?

I'm a beginner at all this! Thanks in advance!

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)="NT") AND
((Customers.SendOrderForm)=Yes) AND
((tblOrderFormsSent.OrderFormsSent)=Max(([tblOrderFormsSent].[OrderFormsSent])
Between Date()-21 And Date()-10000)));
 
J

Jeff L

Is this what you are looking for?

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)="NT") AND
((Customers.SendOrderForm)=Yes) AND
((tblOrderFormsSent.OrderFormsSent)=
(Select Max([OrderFormsSent])
From tblOrderFormsSent
Click to expand...
Where OrderFormsSent Between Date()-10000 And Date()-21)));

Hope that helps!


Hi,

I have two tables, one is Customers and the other is tblOrderFormsSent
which are linked via a one to many relationship. What I need to do is
get the most recent record of the date field for order forms sent and
then see if it is between certain values.

I'm trying to use the maketable query below however I get a 'you cannot
use an aggregate function in a WHERE clause'. I know I somehow need to
nest another SELECT statement in there but I'm not to sure where.
Could someone please help or point me to a good site on contructing SQL
statements?

I'm a beginner at all this! Thanks in advance!

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)="NT") AND
((Customers.SendOrderForm)=Yes) AND
((tblOrderFormsSent.OrderFormsSent)=Max(([tblOrderFormsSent].[OrderFormsSent])
Between Date()-21 And Date()-10000)));
Click to expand...
 
G

Guest

If I understand the problem, the following might work.

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
WHERE Customers.AddressState = "NT"
and Customers.SendOrderForm = Yes
and Customers.[Customer Number] IN
(SELECT TOP 1 tblOrderFormsSent.[Customer Number]
FROM tblOrderFormsSent
WHERE tblOrderFormsSent.OrderFormsSent
Between Date()-21 and Date()-10000
ORDER BY tblOrderFormsSent.OrderFormsSent DESC);
 
C

conradtruscott

Hi Jeff,

Thanks for your reply. Firstly I got a missing ) or ] error when i
trierd to use your query. I appended a ) on the end, then I got a a
syntax error. I've attached what I used from your query. Any idea what
I missed?

Thanks again for your time.
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)="NT") AND
((Customers.SendOrderForm)=Yes) AND
((tblOrderFormsSent.OrderFormsSent)=
(Select Max([OrderFormsSent])

Regards,
Conrad

Jeff said:
Is this what you are looking for?

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)="NT") AND
((Customers.SendOrderForm)=Yes) AND
((tblOrderFormsSent.OrderFormsSent)=
(Select Max([OrderFormsSent])
From tblOrderFormsSent
Click to expand...
Where OrderFormsSent Between Date()-10000 And Date()-21)));

Hope that helps!


Hi,

I have two tables, one is Customers and the other is tblOrderFormsSent
which are linked via a one to many relationship. What I need to do is
get the most recent record of the date field for order forms sent and
then see if it is between certain values.

I'm trying to use the maketable query below however I get a 'you cannot
use an aggregate function in a WHERE clause'. I know I somehow need to
nest another SELECT statement in there but I'm not to sure where.
Could someone please help or point me to a good site on contructing SQL
statements?

I'm a beginner at all this! Thanks in advance!

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)="NT") AND
((Customers.SendOrderForm)=Yes) AND
((tblOrderFormsSent.OrderFormsSent)=Max(([tblOrderFormsSent].[OrderFormsSent])
Between Date()-21 And Date()-10000)));
Click to expand...
Click to expand...
 
J

Jeff L

You don't have the entire statement I gave you in the Select Max part.
Try it again.


Hi Jeff,

Thanks for your reply. Firstly I got a missing ) or ] error when i
trierd to use your query. I appended a ) on the end, then I got a a
syntax error. I've attached what I used from your query. Any idea what
I missed?

Thanks again for your time.
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)="NT") AND
((Customers.SendOrderForm)=Yes) AND
((tblOrderFormsSent.OrderFormsSent)=
(Select Max([OrderFormsSent])

Regards,
Conrad

Jeff said:
Is this what you are looking for?

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)="NT") AND
((Customers.SendOrderForm)=Yes) AND
((tblOrderFormsSent.OrderFormsSent)=
(Select Max([OrderFormsSent])
From tblOrderFormsSent
Click to expand...
Where OrderFormsSent Between Date()-10000 And Date()-21)));

Hope that helps!


Hi,

I have two tables, one is Customers and the other is tblOrderFormsSent
which are linked via a one to many relationship. What I need to do is
get the most recent record of the date field for order forms sent and
then see if it is between certain values.

I'm trying to use the maketable query below however I get a 'you cannot
use an aggregate function in a WHERE clause'. I know I somehow need to
nest another SELECT statement in there but I'm not to sure where.
Could someone please help or point me to a good site on contructing SQL
statements?

I'm a beginner at all this! Thanks in advance!

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)="NT") AND
((Customers.SendOrderForm)=Yes) AND
((tblOrderFormsSent.OrderFormsSent)=Max(([tblOrderFormsSent].[OrderFormsSent])
Between Date()-21 And Date()-10000)));
Click to expand...
Click to expand...
Click to expand...
 
C

conradtruscott

Thanks Jeff I tried it again. The query runs ok, but doesn't retrieve
any records.
i have an entry in the customer table with a tick in the Yes/No field
in the SendOrderForm field, the addressstate as NT, which also has the
following linked entries in tblSendOrderForm
09/06/06
20/07/06
Shouldn't this mean the query grabs the 20/07/06 value, determines it
is between the date()-21 and date()-1000 and pastes the record?

Thanks again for your help so far.
Regards,
Conrad

Jeff said:
You don't have the entire statement I gave you in the Select Max part.
Try it again.


Hi Jeff,

Thanks for your reply. Firstly I got a missing ) or ] error when i
trierd to use your query. I appended a ) on the end, then I got a a
syntax error. I've attached what I used from your query. Any idea what
I missed?

Thanks again for your time.
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)="NT") AND
((Customers.SendOrderForm)=Yes) AND
((tblOrderFormsSent.OrderFormsSent)=
(Select Max([OrderFormsSent])

Regards,
Conrad

Jeff said:
Is this what you are looking for?

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)="NT") AND
((Customers.SendOrderForm)=Yes) AND
((tblOrderFormsSent.OrderFormsSent)=
(Select Max([OrderFormsSent])
From tblOrderFormsSent
Where OrderFormsSent Between Date()-10000 And Date()-21)));

Hope that helps!


(e-mail address removed) wrote:
Hi,

I have two tables, one is Customers and the other is tblOrderFormsSent
which are linked via a one to many relationship. What I need to do is
get the most recent record of the date field for order forms sent and
then see if it is between certain values.

I'm trying to use the maketable query below however I get a 'you cannot
use an aggregate function in a WHERE clause'. I know I somehow need to
nest another SELECT statement in there but I'm not to sure where.
Could someone please help or point me to a good site on contructing SQL
statements?

I'm a beginner at all this! Thanks in advance!

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)="NT") AND
((Customers.SendOrderForm)=Yes) AND
((tblOrderFormsSent.OrderFormsSent)=Max(([tblOrderFormsSent].[OrderFormsSent])
Between Date()-21 And Date()-10000)));
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Pardon me for jumping in, but part of your problem is that your
specifications are a bit unclear.

What I think you want is the latest (by date) record that has an
addressState = "NT" and SendOrderForm = True. The Max date you are
calculating is the max date in the entire table.

Given your field names (spaces in the field names means you have to use []
around the field names and therefore are limited in using an embedded
subquery in the FROM clause of your SQL statement..

The simplest way to accomplish this would be to nest queries.

Query One (saved as qLatestMarked)

SELECT tblOrderFormsSent.[Customer ID], Max([OrderFormsSent]) as Latest Date
FROM Customers INNER JOIN tblOrderFormsSent
ON Customers.[Customer Number] = tblOrderFormsSent.[Customer Number]
WHERE OrderFormsSent Between Date()-10000 And Date()-21
AND Customers.SendOrderForm=True

Use the saved query in the next query

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 qLatestMarked
ON Customers.[Customer Number] = qLatestMarked.[Customer Number]
WHERE Customers.AddressState="NT"

I'm not sure why you need a make table query in this situation, since the
select query should return all the records you need.

Thanks Jeff I tried it again. The query runs ok, but doesn't retrieve
any records.
i have an entry in the customer table with a tick in the Yes/No field
in the SendOrderForm field, the addressstate as NT, which also has the
following linked entries in tblSendOrderForm
09/06/06
20/07/06
Shouldn't this mean the query grabs the 20/07/06 value, determines it
is between the date()-21 and date()-1000 and pastes the record?

Thanks again for your help so far.
Regards,
Conrad

Jeff said:
You don't have the entire statement I gave you in the Select Max part.
Try it again.


Hi Jeff,

Thanks for your reply. Firstly I got a missing ) or ] error when i
trierd to use your query. I appended a ) on the end, then I got a a
syntax error. I've attached what I used from your query. Any idea what
I missed?

Thanks again for your time.
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)="NT") AND
((Customers.SendOrderForm)=Yes) AND
((tblOrderFormsSent.OrderFormsSent)=
(Select Max([OrderFormsSent])

Regards,
Conrad

Jeff L wrote:
Is this what you are looking for?

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)="NT") AND
((Customers.SendOrderForm)=Yes) AND
((tblOrderFormsSent.OrderFormsSent)=
(Select Max([OrderFormsSent])
From tblOrderFormsSent
Where OrderFormsSent Between Date()-10000 And Date()-21)));

Hope that helps!


(e-mail address removed) wrote:
Hi,

I have two tables, one is Customers and the other is
tblOrderFormsSent
which are linked via a one to many relationship. What I need to do
is
get the most recent record of the date field for order forms sent
and
then see if it is between certain values.

I'm trying to use the maketable query below however I get a 'you
cannot
use an aggregate function in a WHERE clause'. I know I somehow
need to
nest another SELECT statement in there but I'm not to sure where.
Could someone please help or point me to a good site on contructing
SQL
statements?

I'm a beginner at all this! Thanks in advance!

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)="NT") AND
((Customers.SendOrderForm)=Yes) AND
((tblOrderFormsSent.OrderFormsSent)=Max(([tblOrderFormsSent].[OrderFormsSent])
Between Date()-21 And Date()-10000)));
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

nested SQL asking for value 7
Simple way to combine unmatched and matched records in one query? 6
ODBC Pass-through query with parameter from table 5
creating a custom date from two "between..and" dates 1
Update Multiple Fields from a Table (Linked or Otherwise) 4
1 table inserted multiple times in a query 3
Challenging Query 3
Table/query layout trouble 2

Top