Complex date query

  • Thread starter Thread starter bj
  • Start date Start date
B

bj

Hi there, I have a customer account No, and contact No, and an order entry
date in a table. Contact no is a number that represents an individual on
that account.Im trying to find our companies reactivation success.

What im trying to do sounds fairly straight forward but i am having trouble
executing it.

Im trying to find all those unique account nos, contact no that have placed
an order 25months + ago and placed an order in the last 12 months but not
13-24months ago.

So for july 04 the order entry date would look something like.

< #31/07/20002# and between #01/08/2003# and #31/07/2004# but not between
01/08/2002# and #31/07/2003#

any ideas what this syntax should really be?

Thanx in advanvce
 
Hi there, I have a customer account No, and contact No, and an order entry
date in a table. Contact no is a number that represents an individual on
that account.Im trying to find our companies reactivation success.

What im trying to do sounds fairly straight forward but i am having trouble
executing it.

Im trying to find all those unique account nos, contact no that have placed
an order 25months + ago and placed an order in the last 12 months but not
13-24months ago.

So for july 04 the order entry date would look something like.

< #31/07/20002# and between #01/08/2003# and #31/07/2004# but not between
01/08/2002# and #31/07/2003#

any ideas what this syntax should really be?
Click to expand...

For one thing, any literal date in a query must be in American
mm/dd/yy format - i.e. 01/08/2003 is interpreted as January 8, not
August 1.

For another, this query as written will find those records where the
date - IN EACH RECORD - is simultaneously prior to July 31, 2002 and
*also* between August 1, 2003 and July 31, 2004. There will of course
be no such record since a single date cannot satisfy both criteria!

What you need instead is an IN clause with a subquery:

SELECT <whatever> FROM Contacts
WHERE ContactNo IN (SELECT ContactNo FROM YourTable AS Old WHERE
Old.EntryDate < #7/31/2002#)
AND ContactNo IN (SELECT ContactNo FROM yourtable AS Recent WHERE
Recent.EntryDate BETWEEN #8/1/2003# AND #7/31/2004#)
AND ContactNo NOT IN (SELECT ContactNo FROM yourtable As DeadYear
WHERE DeadYear.EntryDate BETWEEN #8/1/2002# AND #7/31/2003#);

Don't expect blazingly fast performance... <g>

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Back
Top