Join Error Help

[DeanFran]

What I have
3 tables
Projects
Tasks
Owners
Tasks are linked to Projects via FK (Project PK)
Both Tasks and Projects have FKs, that are the Owners PK.

I am trying to create a query to be used to build a report where Tasks are grouped by Project, and each Project and Task Owner is also shown.

My query skills are the most meager of my already meager Access skills, so I have been fighting with getting a working query all morning. Any help is greatly appreciated.

 

[jdraw]

Please expand on the overview description.
eg Tasks are linked to Projects

1 Project may have 1 or many Tasks
1 Project is owned by 1 Owner

Are these statements true?
Can a Task have a different Owner than the Owner of the Project?
Can a Project have multiple( 1 or Many) Owners?

 

[DeanFran]

1. A Project may have many associated tasks
2. A Project will have one owner
3. A Task will have one owner
4. A Task may or may not have the same owner as its associated Project

 

[DeanFran]

I have figured out that I can work around this by having two Owner tables
1. Project Owners
2. Task Owners

Is there no way to use a single table? In the real world of my little database, many of the names on these two tables will be the same, though there will be some task owners that won't be project owners, and vice versa, it just seems to be poor practice to create two tables with somewhat redundant data.

 

[jdraw]

Yes. Since your Owner table is really a list that can be used to resolve owners whether Task or Project, you can use the table twice in your relationships window.

  Project------>Task   (indicates a project may have many tasks)
     |              |
     |_Owner        |_Owner_1

It's the way Access relationship deals with this situation.

I expect a Project record will have a field for ProjOwner
and a Task record will have a field for TaskOwner

Your owner table would be something like

tblOwner
OwnerId PK
OwnerFirstName
OwnerLastName
other Owner Info

And in the ProjOwner field you would record the OwnerId
in the TaskOwner field you would put the OwnerID

When you build a query in the query window you put 2 instances of Owner table, and Access will suffix the second one with "_1".

Good luck.

 

[DeanFran]

This is a revelation! I didn't know this was possible. This solves the problem. I now have a query that aligns tasks with their related projects, and tasks and projects with their owners. Thank you so much. I also learned how to change the alias for the tables, so the names make more sense than the default.

 

[jdraw]

Try a few tests to make sure we are on the same page.

The set up attached is a general approach.

Good luck.

 

[DeanFran]

I built a sandbox for testing functionality of the idea, and it looks just like that. The ProjectOwner and TaskOwner tables are just renamed aliases of the Owner Table. I was able to construct a report from this query that groups by ProjectName, that looks just as I had hoped. Thanks again.

By the way, I'm not far from your northern location. Just cross at Cornwall and travel east a few miles. I hope you are in Florida because it is COLD up here.