Distinct select problems

[woodpd]

Scaled down table structure:

TblCustomer:

CustomerID
FirstName
Surname
etc

TblEnquiry:

EnquiryID
CustomerID
Source
etc

I am trying (as part of a larger query) to get a list of the EnquiryIDs for a customer's most recent enquiry (therefore the list will only have 1 entry for each customer along with the most recent enquiry). The tables need also INNER JOIN ON CustomerID.

In summary I am trying to do something similar to:

SELECT Customer.CustomerID, [all other customer details], [all other enquiry details]

FROM Enquiry INNER JOIN Customer ON Customer.CustomerID = Enquiry.CustomerID

WHERE EnquiryID = {Table of most recent EnquiryIDs for each customer}

ORDER BY Customer.Surname, Customer.FirstName



Any help would be massively appreciated.

 

[forecasting]

thread183-295526 contains an interesting discussion of the technique you want to employ. You want to modify the statement

WHERE EnquiryID = {Table of most recent EnquiryIDs for each customer}

to something like

WHERE EnquiryID = (SELECT MAX(EnquiryID) FROM TblEnquiry WHERE Customer.CustomerID = Enquiry.CustomerID)

 

[woodpd]

Thanks mate, worked a treat. I just got stuck in my thinking of trying to use DISTINCT (which I don't think would have worked). So easy when new eyes look at the problem!!