using between ... and

[W]

Hi,

I want to be able to do the following :

I want to know if a customer has bought something within a predefined
period, starting from a varying date. My data are recorded in table tblA.

E.g. :

Customer A bought item a on date dteA 01/01/2008 (European date format :
dd/mm/yyyy).
Did he buy another item a in the period ranging from 01/01/2008 till
10/01/2008 ?
Say : customer A bought on 01/01/2008, 03/01/2008, 07/01/2008 and
15/01/2008. It is clear that I do not need the purchase on 15/01/2008.

I used two instances of tblA, namely tblA and tblA_1.
I used [blA_1]![dteA] between [blA1]![dteA] and [blA1]![dteA] + 10.

But how do I exclude to match with the same day ? I mean 01/01/2008 will be
the same in tblA and tblA_1. I presume there have not been purchases on the
same day.

Any help will be appreciated.

W

 

[John Spencer]

WHERE ([blA_1]![dteA] between [blA1]![dteA] and [blA1]![dteA] + 10 AND
[blA_1]![dteA] <>[blA1]![dteA] )

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Allen Browne]

You could use a subquery to get the previous purchase date for the customer.

Type an expression like this into the Field row in query design:
(SELECT Max(dtA) AS LastPurchDate
FROM tblA AS Dupe
WHERE Dupe.CustomerID = tblA.CustomerID
AND Dupe.dtA < tblA.CustomerID)

Once you get that working, you can change it to reflect the number of days
between purchases:
dtA - (SELECT Max(dtA) AS LastPurchDate
FROM tblA AS Dupe
WHERE Dupe.CustomerID = tblA.CustomerID
AND Dupe.dtA < tblA.CustomerID)

You can then use Criteria under this field.
(Careful how you handle the nulls.)

If subqueries are a new concept, see:
http://allenbrowne.com/subquery-01.html

 

[W]

Thanks for your answer, John.

But :

Say customer A bought on 01/01/2008, and on 03/01/2008, and on 05/01/2008, I
then get three rows :

one with : 01/01/2008 and 03/01/2008
one with : 01/01/2008 and 05/01/2008
one with : 03/01/2008 and 05/01/2008

Is there a possibility to get one row with three columns as in :

Customer A – 01/01/2008 – 03/01/2008 – 05/01/2008

I know there will be customers who can have 5 dates and customers who will
have only one date.

I ask this question because I’m already thinking of the report that will
have to be made, based on this query.

Thank you,


W

 

[John Spencer]

In that case you might want to look at using a multi-column subreport to
report the dates. And you would probably need to base the main report
(record source) on a query that only returned the unique Customers for the
specified date range.

Another option would be to use Duane Hookom's concatenate function to
combine all the dates into one field.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Thanks for your answer, John.

But :

Say customer A bought on 01/01/2008, and on 03/01/2008, and on 05/01/2008,
I
then get three rows :

one with : 01/01/2008 and 03/01/2008
one with : 01/01/2008 and 05/01/2008
one with : 03/01/2008 and 05/01/2008

Is there a possibility to get one row with three columns as in :

Customer A - 01/01/2008 - 03/01/2008 - 05/01/2008

I know there will be customers who can have 5 dates and customers who will
have only one date.

I ask this question because I'm already thinking of the report that will
have to be made, based on this query.

Thank you,


W

WHERE ([blA_1]![dteA] between [blA1]![dteA] and [blA1]![dteA] + 10 AND
[blA_1]![dteA] <>[blA1]![dteA] )

 

[W]

Hello John,

Umm, this seems to be complicated.

I'll have to take a thorough look into the subreport stuff, and for now I'll
take a look, prontissimo, at mr Hookom's tip.

I'll let you know if I can get out of trouble.

Thank you,

W