Query Help Please

[Karin]

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.

 

[John Spencer]

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]

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 W. Vinson]

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>)

 

[Karin]

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.