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
|
|||
|
|||
Query Help Please
I have a relational db with a Client Table and a Project Table. The Client
table holds general info that usually doesn't change. The Project table has a record for each project we've done for the client, and contains a Manager field. The manager could change from year to year. I want to show by current manager all the other projects for that client (regardless of who previous manager was). Current manager is just the manager on the latest project. I'm guessing I need a query of a query, but I'm stumped. Any advice would be appreciated. Thanks. |
#2
|
|||
|
|||
Query Help Please
Do you have a query that shows the latest project for the client and the
project manager for that project? If so, please post the SQL of that query. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Karin wrote: I have a relational db with a Client Table and a Project Table. The Client table holds general info that usually doesn't change. The Project table has a record for each project we've done for the client, and contains a Manager field. The manager could change from year to year. I want to show by current manager all the other projects for that client (regardless of who previous manager was). Current manager is just the manager on the latest project. I'm guessing I need a query of a query, but I'm stumped. Any advice would be appreciated. Thanks. |
#3
|
|||
|
|||
Query Help Please
This gets a list of all clients' latest projects
****** SELECT tblClients!IDClients & "-0" AS IDSched, tblClients.ClientName, tblProjectType.ProjectType, tblProjects.Partner, tblProjects.Manager, tblProjects.ProjectFYE FROM tblProjectType RIGHT JOIN (tblClients LEFT JOIN tblProjects ON tblClients.IDClients = tblProjects.IDClients) ON tblProjectType.RowIDProjType = tblProjects.ProjectTypeID WHERE (((tblProjects.ProjectFYE)#1/1/2007#) AND ((tblProjects.CreateNewProject)=0) AND ((tblProjects.SignatureDate) Is Not Null) AND ((tblProjects.ArchivedDate) Is Null) AND ((tblClients.InactiveClient)=False) AND ((tblProjects.Billed)2)) OR (((tblProjects.ProjectFYE)#1/1/2007#) AND ((tblProjects.CreateNewProject)=0) AND ((tblProjects.SignatureDate) Is Null) AND ((tblProjects.ArchivedDate) Is Null) AND ((tblClients.InactiveClient)=False) AND ((tblProjects.Billed)2)) OR (((tblProjects.ProjectFYE)#1/1/2007#) AND ((tblProjects.CreateNewProject)0) AND ((tblProjects.SignatureDate) Is Not Null) AND ((tblProjects.ArchivedDate) Is Null) AND ((tblClients.InactiveClient)=False) AND ((tblProjects.Billed)2)) ORDER BY tblClients.ClientName, tblProjects.Partner, tblProjects.Manager, tblProjects.ProjectFYE; ****** "John Spencer" wrote: Do you have a query that shows the latest project for the client and the project manager for that project? If so, please post the SQL of that query. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Karin wrote: I have a relational db with a Client Table and a Project Table. The Client table holds general info that usually doesn't change. The Project table has a record for each project we've done for the client, and contains a Manager field. The manager could change from year to year. I want to show by current manager all the other projects for that client (regardless of who previous manager was). Current manager is just the manager on the latest project. I'm guessing I need a query of a query, but I'm stumped. Any advice would be appreciated. Thanks. . |
#4
|
|||
|
|||
Query Help Please
On Thu, 11 Feb 2010 07:06:02 -0800, Karin
wrote: I have a relational db with a Client Table and a Project Table. The Client table holds general info that usually doesn't change. The Project table has a record for each project we've done for the client, and contains a Manager field. The manager could change from year to year. I want to show by current manager all the other projects for that client (regardless of who previous manager was). Current manager is just the manager on the latest project. I'm guessing I need a query of a query, but I'm stumped. Any advice would be appreciated. Thanks. Try a subquery, something like SELECT Clients.thisfield, Clients.thatfield, Projects.thisfield, Projects.thatfield FROM Clients INNER JOIN Projects ON ClientID = Projects.ProjectID WHERE Clients.ClientID IN(SELECT ClientID FROM Projects WHERE Projects.Manager = whatever) -- John W. Vinson [MVP] |
#5
|
|||
|
|||
Query Help Please
I have figured out how to run a query on one manager and then with a 2nd
query, link that with a join to all other projects for those clients, which will work, but I haven't figured out how to do it for multiple managers so I can run a report by manager. "Karin" wrote: This gets a list of all clients' latest projects ****** SELECT tblClients!IDClients & "-0" AS IDSched, tblClients.ClientName, tblProjectType.ProjectType, tblProjects.Partner, tblProjects.Manager, tblProjects.ProjectFYE FROM tblProjectType RIGHT JOIN (tblClients LEFT JOIN tblProjects ON tblClients.IDClients = tblProjects.IDClients) ON tblProjectType.RowIDProjType = tblProjects.ProjectTypeID WHERE (((tblProjects.ProjectFYE)#1/1/2007#) AND ((tblProjects.CreateNewProject)=0) AND ((tblProjects.SignatureDate) Is Not Null) AND ((tblProjects.ArchivedDate) Is Null) AND ((tblClients.InactiveClient)=False) AND ((tblProjects.Billed)2)) OR (((tblProjects.ProjectFYE)#1/1/2007#) AND ((tblProjects.CreateNewProject)=0) AND ((tblProjects.SignatureDate) Is Null) AND ((tblProjects.ArchivedDate) Is Null) AND ((tblClients.InactiveClient)=False) AND ((tblProjects.Billed)2)) OR (((tblProjects.ProjectFYE)#1/1/2007#) AND ((tblProjects.CreateNewProject)0) AND ((tblProjects.SignatureDate) Is Not Null) AND ((tblProjects.ArchivedDate) Is Null) AND ((tblClients.InactiveClient)=False) AND ((tblProjects.Billed)2)) ORDER BY tblClients.ClientName, tblProjects.Partner, tblProjects.Manager, tblProjects.ProjectFYE; ****** "John Spencer" wrote: Do you have a query that shows the latest project for the client and the project manager for that project? If so, please post the SQL of that query. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Karin wrote: I have a relational db with a Client Table and a Project Table. The Client table holds general info that usually doesn't change. The Project table has a record for each project we've done for the client, and contains a Manager field. The manager could change from year to year. I want to show by current manager all the other projects for that client (regardless of who previous manager was). Current manager is just the manager on the latest project. I'm guessing I need a query of a query, but I'm stumped. Any advice would be appreciated. Thanks. . |
Thread Tools | |
Display Modes | |
|
|