Finding First salesperson

[Jordan_n22 via AccessMonster.com]

Hello,

I am trying to create a report that will show me the first salesperson for
each of our clients. I have two table that are related by ID. One table has
the clients contact info and one table with the sales information including
the sales person and the sales date. What I want to show is to show the the
salesperson and the date for the first sale. WITH OUT showing all the
subsequent records since the first sale. I am not very good with SQL yet.
Thanks for your help and I find this forum very useful.

 

[John Spencer]

Assumption:
Sales table has clientid and salesperson id.

SELECT Clients.ClientName, Sales.SalesPerson
FROM (Clients INNER JOIN Sales on
Clients.ID = Sales.ClientID)
INNER JOIN (
SELECT Sales.ClientID
, Min(Sales.SalesDate) as SaleDate
FROM Sales
GROUP BY ClientID) as FirstSale
On Sales.ClientID = FirstSale.ClientID
And Sales.SalesDate = FirstSale.SaleDate

Another way is
SELECT Clients.ClientName, Sales.SalesPerson
FROM (Clients INNER JOIN Sales on
Clients.ID = Sales.ClientID)
WHERE Sales.SalesDate =
(SELECT Min(S2.SaleDate)
FROM Sales as S2
WHERE S2.ClientID = Sales.ClientID)




'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Jordan_n22 via AccessMonster.com]

Thank you very much! The second way you said worked like a charm. I couldn't
get the the first one to work though.

Jordan_n22

Assumption:
Sales table has clientid and salesperson id.

SELECT Clients.ClientName, Sales.SalesPerson
FROM (Clients INNER JOIN Sales on
Clients.ID = Sales.ClientID)
INNER JOIN (
SELECT Sales.ClientID
, Min(Sales.SalesDate) as SaleDate
FROM Sales
GROUP BY ClientID) as FirstSale
On Sales.ClientID = FirstSale.ClientID
And Sales.SalesDate = FirstSale.SaleDate

Another way is
SELECT Clients.ClientName, Sales.SalesPerson
FROM (Clients INNER JOIN Sales on
Clients.ID = Sales.ClientID)
WHERE Sales.SalesDate =
(SELECT Min(S2.SaleDate)
FROM Sales as S2
WHERE S2.ClientID = Sales.ClientID)

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Hello,

[quoted text clipped - 5 lines]

subsequent records since the first sale. I am not very good with SQL yet.
Thanks for your help and I find this forum very useful.