I have a list of customers that I am trying to find out which ones have purchased Electronics first, and then show they’re next purchases.

I’m hoping I can get some help or some ideas as to how I can do this.

I have a list of some 9000 customers.

The rows I have are:

1. Customer number
2. Order number
3. Order Date (Date it was purchased)
4. Description (Tells me if it’s an Electronics or not)

This is what I was thinking.

The first order of each customer that purchased an electronic item, then take all the order numbers > the electronic order number that has the same Customer number. Leaving me with a list of customer had purchased electronics for they’re first purchase and what they purchased next.

If they didn’t have a 2nt purchase I would like to know that, maybe by adding a 1


Any help would be great. I’ve started this project over 20 times, but can’t get past the final separation.

Thanks for all your help and time

Corey

SELECT *
FROM tblData
WHERE Description = 'Electronics'

Save that query, say as qryElectronics, then:

SELECT tblData.CustomerNumber, tblDataOrderNumber, tblData.OrderDate, tblData.Description
FROM qryElectronics LEFT JOIN tblData ON CustomerNumber = CustomerNumber
WHERE tblData.OrderDate >= qryElectronics.OrderDate

That should give you what you want.

Kindler,

This is great!! I see from the code what you’re asking it to do. I don’t have a strong understanding of SQL so I'm not sure how to handle the error message I'm getting.

Maybe you can help

     ""Join expression not supported""

Bah, I get the same message from Access half the time I do joins, no idea why because they should be fine in SQL. Here's the workaround for the second query:

SELECT tblData.CustomerNumber, tblData.OrderNumber, tblData.OrderDate, tblData.Description
FROM qryElectronics, tblData
WHERE qryElectronics.CustomerNumber = tblData.CustomerNumber AND tblData.OrderDate >= qryElectronics.OrderDate

Oh, and if you add the line:
ORDER BY tblData.CustomerNumber, tblData.OrderDate

to the end you'll get it in an order that shows what you're looking for easily. The only thing is, and I'm not sure if that's what you want, but the query will show any orders including and after a customer's first electronic purchase. It does not check to see if their first electronic purchase is also their first purchase in general.

Kindler,

Is there a way I can identify the next couple of purchase.

So it would look to make sure it's the same customer number.
It would have a future date from the first purchase
The order number would be greater then the fist purchase.

I tried making a query that would go to the next order number and date, but it just added a number to the order number and date

I'm not sure what you're looking for based on that description. If you could post an example table and what kind of results you'd want from running the query, it might be clearer.

Hi Kindler, sorry for the delay, got pulled into another project.
Below is the data I have

CustNb OrdrNb OrdrEntryDt Class
24423 34449 6/25/2005 15
24423 34467 6/25/2005 15
24423 43140 7/24/2005 03
24423 51622 8/31/2005 04
24423 55509 9/23/2005 12
24423 58192 10/10/2005 04
24423 61590 10/29/2005 10
24426 29176 6/3/2005 15
24436 29193 6/3/2005 15
24443 29200 6/3/2005 15
24445 29202 6/3/2005 15
24445 29204 6/3/2005 15
24450 29239 6/3/2005 15
24471 55500 9/23/2005 15
24471 66724 11/19/2005 15
24471 67693 11/22/2005 12
24471 79592 12/16/2005 16
24481 29297 6/4/2005 15
24482 29299 6/4/2005 15
24483 29301 6/4/2005 15
24484 29302 6/4/2005 15
24486 29304 6/4/2005 15
24486 29304 6/4/2005 18
24486 43878 7/27/2005 18

I'm trying to identify all the purchase, even the ones purchased the same day.

Kind of like this:


CustNb OrdrNb OrdrEntryDt Class Purchase Number
24423 34449 6/25/2005 15 First Purchase
24423 34467 6/25/2005 15 Second Purchase
24423 43140 7/24/2005 03 Third Purchase
24423 51622 8/31/2005 04 Fourth Purchase
24423 55509 9/23/2005 12 Fifth Purchase
24423 58192 10/10/2005 04 Sixth Purchase
24423 61590 10/29/2005 10 Seventh Purchase

24426 29176 6/3/2005 15 First Purchase
24436 29193 6/3/2005 15 First Purchase

24443 29200 6/3/2005 15 First Purchase

24445 29202 6/3/2005 15 First Purchase
24445 29204 6/3/2005 15 Second Purchase
24450 29239 6/3/2005 15 First Purchase
24471 55500 9/23/2005 15 First Purchase
24471 66724 11/19/2005 15 Second Purchase
24471 67693 11/22/2005 12 Third Purchase
24471 79592 12/16/2005 16 Fourth Purchase
24481 29297 6/4/2005 15 First Purchase
24482 29299 6/4/2005 15 First Purchase
24483 29301 6/4/2005 15 First Purchase
24484 29302 6/4/2005 15 First Purchase
24486 29304 6/4/2005 15 First Purchase

The code I posted earlier should accomplish that. The only sticking point is whether you want to show the purchases subsequent to every customer's first electronics purchase, or only those customers whose first purchase was electronics.