Using the First or Min function.

[Mase5701]

I have a table with all my Customer Orders and I'm trying to return a single record for the Earliest OrderPlaced but I cannot for the life of me return the single oldest Order Number according to the CustomerID.

I have 2 tables:
CustomerOrders Customers
OrderNumber (pk) CustomerID (pk)
OrderDate
OrderAmount*

I have 2 queries as such:

SELECT tblCustomers.CustomerID, Min(tblCustomerOrders.OrderDate) AS MinOfOrderDate
FROM tblCustomers INNER JOIN tblCustomerOrders ON tblCustomers.CustomerID = tblCustomerOrders.CustomerID
GROUP BY tblCustomers.CustomerID;


SELECT tblCustomerOrders.OrderNumber, tblCustomerOrders.OrderAmount, qryCustomerOrderMinDate.MinOfOrderDate
FROM qryCustomerOrdersMinDate LEFT JOIN tblCustomerOrders ON qryCustomerOrdersMinDate.CustomerID = tblCustomerOrders.CustomerID;

my subquery shows every record in the CustomerOrders table. I just want one record for each Customer #.




Am I stupid? This seems like such a simple issue but I've been stuck on it forver!

 

[Joe4]

Welcome to the Board!

In your second query, you need to join on 2 fields, the CustomerID and the Date fields.
Otherwise, you really aren't selecting the single, minimum record for each Customer ID.