Join Query Without Duplicates

[Stang70Fastback]

Hello everyone. I'll try to explain this intelligently. I need to generate a report that uses a Left Join query. The tables being joined are a "Projects" table and an "Assignments" table. The Projects table, as one might expect, has one entry for each project being worked on. The Assignments table has many entries for each Project, each entry for a different engineer assigned to the Project.

What I'm trying to do is create a report as a continuous form, that lists every project. Not too hard. The tricky part is that for each project, I want it to show the "Design Lead" for each project. In other words, one of the engineers in the "Assignments" table is designated as the design lead for that project.

Initially I thought that would be easy. In my left-join statement, my where clause simply has a statement that tells it to exclude any records where the design lead isn't "true." This kind of worked, except some projects don't have a design lead, so now those projects don't show up at all.

So what I'm looking for is the following: How do design a query that will give me only ONE entry for each project, and for each one of those entries, have a design lead, or have it blank if there isn't one (or if there aren't any employees assigned to the project at all)?

This is the current query, which you can see will exclude any projects without a design lead, which is a problem:

SELECT Assignments.ProjectNum AS Assignments_ProjectNum, Assignments.Badge, Assignments.LastName, Assignments.FirstName, Assignments.DLead, Projects.JOCNum, Projects.ProjectNum AS Projects_ProjectNum, Projects.CapitalNum1, Projects.CapitalNum2, Projects.ProjectName, Projects.Type, Projects.DStartDate, Projects.CStartDate, Projects.DEndDate, Projects.CEndDate
FROM Projects LEFT JOIN Assignments ON Projects.[ProjectNum] = Assignments.[ProjectNum]
WHERE (Assignments.DLead<>False Or IsNull(Assignments.Badge)) AND (Projects.Type)=Forms!MAINWINDOW!TypeCOM;

 

[James Deckert]

I'm questioning your placement of where the Design lead is. Shouldn't this field be in the projects table, instead of the assignments table? Your query would be much easier if it were, as well as this seems like the right place to put the field for normalization.

 

[Stang70Fastback]

Thanks for the reply.

It is possible to have multiple design leads for a project.

The way the database is set up now, users complete a form for the project, and then there is a subform (continuous form) where they can enter a bunch of engineers who will be working on the project, along with information for each regarding how many hours they will work. Each of those has a checkbox that can be toggled to identify them as one of the design leads for the project.

How do you suggest I do it, if I'm to have it associated with the project records?

 

[jdraw]

Can you show us a jpg of your tables and relationships? Make sure to extend the boxes to show all fields before capturing the screen.

I could see a set up like this if people can be assigned to multiple projects and/or have multiple roles.

Project-->Assignment<---Engineers

 

[Stang70Fastback]

That's more or less what it is. However, the engineers table isn't linked because they add/remove them at will, so that information is copied into the assignments table for each entry.

See the attached screenshot.

 

[namliam]

Solution is pretty easy, simply make a query first that is called qryProjectLead, which holds only the leads.
Then Left join this query as a seperate object instead of trying to do the two things in one go.

 

[Stang70Fastback]

Okay. I see what you're driving at. I'll try that and see what happens! Thanks!

EDIT: Yup! That was what I needed to do. Thanks again!