Query To Find Next Purchase Category Based On Previous Purchase?

[The Brown Growler]

Hi,

I would be grateful if anyone could please help me with a query that will provide details of a customers next purchase category based on the category of their previous purchase?


Using the example below:

[Customer] [Site] [OrderDate] [OrderCategory]
Joes Bakery, Rennes, 01/09/2011, C001
Joes Bakery, Rennes, 05/09/2011, C005
Joes Bakery, Rennes, 07/09/2011, C019
Joes Bakery, Rennes, 03/10/2011, C001
Joes Bakery, Rennes, 23/10/2011, C007
Joes Bakery, London, 04/09/2011, C013
Joes Bakery, London, 05/09/2011, C001
Joes Bakery, London, 17/09/2011, C056
Joes Bakery, London, 23/10/2011, C001
Joes Bakery, London, 28/10/2011, C083



I would like to get details of the next customer order when the previous customer order category was C001. The next customer order and previous customer order are defined by the OrderDate field. The result of the query should be as below:



Joes Bakery, Rennes, 05/09/2011, C005
Joes Bakery, Rennes, 23/10/2011, C007
Joes Bakery, London, 17/09/2011, C056
Joes Bakery, London, 28/10/2011, C083



I have though about trying to rank the order dates per Customer and Site then getting the ranking number when the OrderCategory = C001, followed by another query to get the order details for Rank+1. However, if there is another option which is more efficient then I would be grateful for any advice?



Rgds

 

[Taruz]

Hi..:

table_name = name of your table should be..

please try to..:

select 
         
         customer, 
         Site, 
         (select last(orderdate) from table_name where customer=trz.customer and ordercategory=trz.orcategory) as ordate, 
         orcategory

from (
             select 
                          customer,  
                          Site, 
                          (select first(ordercategory) from [table_name]  
                                              where site=tt.Site and orderdate>=tt.orderdate 
                                                          and 
                                                          ordercategory<>'c001'
                                                          and
                                                          customer=tt.customer) as orcategory
            from table_name as tt
            where  iif([ordercategory]='c001' 
                                                            and 
                              ordercategory <>(
                                                                select first(ordercategory) 
                                                                  from [table_name]  
                                                                where site=tt.site and customer=tt.customer and orderdate>=tt.orderdate and ordercategory<>'c001'),0)=0
              ) as trz

 

[The Brown Growler]

Taruz,

Many thanks for taking the time to create the code and post it onto the forum.

The code runs in an SQL query but it is not returning the hoped for results. However, that is probably due to my forgetting about a possible occurence in the data set. There could be cases where a [Customer] and [Site] place and order on {OrderCategory] = 'C001' and the next order they place is also for [OrderCategory] = 'C001'.

I have occurences of this in the data set and this might be causing the query to return results that are out of line with what they should be. I have placed the updated source data set below with the hoped for result.


Using the example below:

[Customer] [Site] [OrderDate] [OrderCategory]
Joes Bakery, Rennes, 01/09/2011, C001
Joes Bakery, Rennes, 05/09/2011, C005
Joes Bakery, Rennes, 07/09/2011, C019
Joes Bakery, Rennes, 03/10/2011, C001
Joes Bakery, Rennes, 12/10/2011, C001
Joes Bakery, Rennes, 23/10/2011, C007
Joes Bakery, London, 04/09/2011, C013
Joes Bakery, London, 05/09/2011, C001
Joes Bakery, London, 17/09/2011, C056
Joes Bakery, London, 23/10/2011, C001
Joes Bakery, London, 28/10/2011, C083


There is an additional line in the source data for Rennes, 12/10/2011 which is the purchase of 'C001' which is a purchase directly after a previous purchase of 'C001' The results should appear as below if the query runs correctly.


Joes Bakery, Rennes, 05/09/2011, C005
Joes Bakery, Rennes, 12/10/2011, C001
Joes Bakery, Rennes, 23/10/2011, C007
Joes Bakery, London, 17/09/2011, C056
Joes Bakery, London, 28/10/2011, C083


If you have the time to look at the updated example I would be very grateful for your feedback.

One last point, can I just paste the code directly into an SQL query, it looks like it might be meant to run in VBA? When saving and closing the SQL query then reopening the query it reaasigned some of the round brackets to square brackets then gave a message about invalid bracketing on save?


Rgds

 

[vbaInet]

Do you have a CustomerID field?

 

[The Brown Growler]

Do you have a CustomerID field?

Not at the moment but I can add one to the data set if it makes things easier? It would be in the format of an autonumber.

Thx for looking at the topic.

 

[vbaInet]

You have a Customers table too right? You should have had a CustomerID as the first thing when you created your table.

 

[The Brown Growler]

You have a Customers table too right? You should have had a CustomerID as the first thing when you created your table.

Yes, got one of those also

 

[vbaInet]

Ok. Here are the steps:

1. Create a query to include CustomerID, Customer, Site, OrderDate and OrderCategory and sort by Customer > OrderDate > OrderCategory (in that order) in Ascending order. Call this query qryOrdersFind
2. Go back to your original query and put the following in a new column:

(SELECT TOP 1 qryOrdersFind.OrderCategory FROM qryOrdersFind WHERE qryOrdersFind.CustomerID =[COLOR=Red] TableName[/COLOR].CustomerID AND qryOrdersFind.OrderDate < [COLOR=Red]TableName[/COLOR].OrderDate)

Amend the red bits.
3. In the criteria under the field in step 2, put 'C001'