If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Finding First salesperson
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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200702/1 |
#2
|
|||
|
|||
Finding First salesperson
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 wrote: 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. |
#3
|
|||
|
|||
Finding First salesperson
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 John Spencer wrote: 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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200702/1 |
Thread Tools | |
Display Modes | |
|
|