I’ve started this project over 20 times ***Help***

[Corey]

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 attached list shows what the data looks like that I have to work with..

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

 

[Corey]

Pat, Thank you very much for walking me through this.

I'm not understanding what the ProductID stands for, and the last line in the 2 query:
"Where tblOrderDetails.ProductID In(.....); <---- the list of IDs you are looking for"

 

[Corey]

Hi again Pat,

Thank you for helping me figure this thing out.

I attached a spread sheet to help myself and you “if you need to” visualize what the data I have looks like and what I'm shooting for.

Thanks again.

Corey

 

[Corey]

but how would I handle the code

"Where tblOrderDetails.ProductID In(.....);

 

[Corey]

Under "Class". If there is a 15 then it's a electronic item

 

[Corey]

I think we almost have it completed.

I have attached a small sample db. Could you please review and let me know why I'm getting the error messages and I'm not sure why it's not working

Thank you

 

[Corey]

Thanks for looking at this for me.

Below are the three query's

query1:
SELECT Temp_Summary.CustNb, Min(Temp_Summary.OrdrNb) AS FirstOrdrNb, Min(Temp_Summary.Class) AS Class_1
FROM Temp_Summary
GROUP BY Temp_Summary.CustNb
ORDER BY Temp_Summary.CustNb, Min(Temp_Summary.OrdrNb);



query2:
SELECT DISTINCT Temp_Summary.CustNb, Temp_Summary.OrdrNb, tblOrderDetails.Class
FROM Temp_Summary INNER JOIN tblOrderDetails ON (Temp_Summary.OrdrNb=tblOrderDetails.OrdrNb) AND (Temp_Summary.CustNb=tblOrderDetails.CustNb)
WHERE (((tblOrderDetails.Class)="15"));



Query3:
SELECT Query1.CustNb, Query1.FirstOrdrNb, Query2.Class AS Expr1, Query1.Class_1
FROM Query1, Query2
WHERE (((Query1.Class_1)<>"15"))
ORDER BY Query1.CustNb, Query1.FirstOrdrNb;