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

 

[Pat Hartman]

You will have to do this in a couple of steps and nest the queries. The inner query needs to find each customer's first order.
query1:
Select CustomerID, Min(OrderID) As FirstOrderID from tblOrders
Group by CustomerID;

The next query selects electronic items from the Order details table:
query2:
Select tblOrder.CustomerID, tblOrder.OrderID, tblOrderDetails.ProductID
From tblOrder Inner Join tblOrderDetails On tblOrder.CustomerID = tblOrderDetails.CustomerID
Where tblOrderDetails.ProductID In(.....); <---- the list of IDs you are looking for.

The final query brings together the first two.
Query3:
Select query1.CustomerID, query1.FirstOrderID, query2.ProductID
From query1 Inner Join query2 on query1.OrderID = query2.OrderID;

You can probably do this with sub queries but I prefer nested queries because they can be tested independently and then together.

 

[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

 

[Pat Hartman]

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"

Sorry, I made the assumption that you would have product IDs since most other order entry applications have them. If you use only description to identify the product being ordered, then substitute whatever is necessary to identify the electronic items.

 

[Corey]

but how would I handle the code

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

 

[Pat Hartman]

How do you identify that a customer has purchased an electronic item?

 

[Corey]

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

 

[Pat Hartman]

Then query2 would be:
query2:
Select tblOrder.CustomerID, tblOrder.OrderID, tblOrderDetails.ProductID
From tblOrder Inner Join tblOrderDetails On tblOrder.CustomerID = tblOrderDetails.CustomerID
Where tblOrderDetails.Class = 15; <---- the list of IDs you are looking for.

 

[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

 

[Pat Hartman]

I don't really have time to download the db. Post the error and the code or query if necessary.

 

[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;

 

[Pat Hartman]

You didn't say what the error message was. If Class_1 is numeric, remove the quotes around the 15.