Display a list of Oldest Orders for each Customer

[Mase5701]

I'm trying to create a list of all the oldest orders my customer has. but I can't seem to get my subquery to list one record for each CustomerID. Whenever I add the Order Amount to the subquery every orders lists.

I have these tables
tblCustomers - Customer ID, Customer Name, etc.
tblCustomerOrders - OrderID, OrderDate, OrderAmount

I want a list that has Customer ID, DateOfOldestOrder, Amount


first query [qryCustomerOrdersOldest]

SELECT tblCustomerOrders.CustomerID, Min(tblCustomerOrders.OrderDate) AS MinOfOrderDate
FROM tblCustomerOrders
GROUP BY tblCustomerOrders.CustomerID;


subquery



SELECT tblCustomerOrders.OrderAmount, qryCustomerOrdersOldest.CustomerID, Min(qryCustomerOrdersOldest.MinOfOrderDate) AS MinOfMinOfOrderDate
FROM qryCustomerOrdersOldest LEFT JOIN tblCustomerOrders ON qryCustomerOrdersOldest.CustomerID = tblCustomerOrders.CustomerID
GROUP BY tblCustomerOrders.OrderAmount, qryCustomerOrdersOldest.CustomerID;

 

[welshgasman]

It will as you are grouping on that as well.
Make it a where > 0 instead

 

[Mase5701]

It will as you are grouping on that as well.
Make it a where > 0 instead

But If I use the where clause I cannot have it in my query result and I need that order amount to show in a column so I can use it for further action queries.

 

[welshgasman]

You do not need group by in the main query.?
You have identified a record in the first query that hopefully has the minimum date.? What happens if there are more than one order on that date.?
I'd probably look at the min ID as well.
Then use that in the main query.?

So identify ID for min date for each customer. Then use that ID for the main query and pull out whatever fields you want then.