Help with Multiple joined fields Query

[jackg6]

I am attempting to create a query that I am going to use to create a report out of. The query needs to include 4 fields:

• Project_Title
• Resource_Name
• SumOfHoursAllocated
• SumOfDuration

I am currently trying to do this by joining two queries together that I have already made. The first one contains

• Project_ID
• Resource_ID
• SumOfHoursAllocated

And the second one contains

• Project_ID
• Resource_ID
• SumOfDuration

Projects and resources are each stored in separate tables (called Projects and Resources) with their names and ID fields as well as some other fields that I don't think are relevant.

My problem is that I cannot figure out how to join the two queries in such a way that it displays 2 numbers for each Project resource combo. I have gotten many different results from error messages to no data showing up when I run the query.

SELECT Projects.Project_Title, Resources.Resource_Name, [Grouped Hours qry].SumOfDuration, [Grouped Allocation qry].SumOfHoursAllocated
FROM [Grouped Hours qry] INNER JOIN (([Grouped Allocation qry] INNER JOIN Projects ON [Grouped Allocation qry].Project_ID = Projects.Project_ID) INNER JOIN Resources ON [Grouped Allocation qry].Resource_ID = Resources.Resource_ID) ON ([Grouped Hours qry].Resource_ID = Resources.Resource_ID) AND ([Grouped Hours qry].Project_ID = Projects.Project_ID);

The above code is the most recent one I tried and it only returned results that that had an entry for both allocation and duration. I need it to display all entries in allocations and duration with either zeros or nulls where there is only data for one or the other.

Also this code is returning the ambiguous outer joins message but I cant seem to figure out a work around.

Thanks for any help or advice anyone can offer.

[June7]

Change the join type. INNER JOIN requires related records in all tables.

If you want to provide db for analysis, follow instructions at bottom of my post.

[jackg6]

Ok im working with sharepoint tables and linked excel files so i just made a copy of everything relevant to this query. I tried playing with the join types but even after reading about ambiguous joins all day I still can't seem to find the solution. Thanks for taking a look!

[June7]

Names make poor unique identifiers. What if there are multiple John Smith? Text fields are also supposed to be slow performers for indexing.

If you want to save Project_ID as foreign key in Timesheet, then Project field needs to be a number type. Same for User field. Should Duration be number type? You have number fields in [Allocations and Goals] for FK values, why not in Timesheet?

Not really understanding data relationships. Setting Relationships would help. So would some records for testing. I added some dummy values.

The posted query tries to join ON ([Grouped Hours qry].Resource_ID = Resources.Resource_ID) AND ([Grouped Hours qry].Project_ID = Projects.Project_ID) but neither ID field is in [Grouped Hours qry]. I added the fields to the query then the posted query does not error but also does not show records. The Query ends up with INNER JOINs and circular relationship. This is not good.

[jackg6]

OK I definitely did a sloppy job with that example db. This one should be a little better.

[June7]

Same concerns about Timesheet fields.

No change in [Grouped Hours qry]

The posted query will have same issues and same fixes and same outcome that I already described.

[jackg6]

Same concerns about Timesheet fields.

No change in [Grouped Hours qry]

The posted query will have same issues and same fixes and same outcome that I already described.

Yes I agree that the identifiers of the timesheet are not ideal, however I have no control over how that table is set up. I have tried using the Grouped hours query to pull the Project and Resource ID fields rather than the values of the fields themselves, and this works fine, yet I still cannot figure out how to set up a query to combine it with the Grouped Allocations query that doesn't either get an error or simply displays no results.

[June7]

Possibly because the query establishes links as defined by Relationships builder. As noted, this results in circular relationship. Need to figure out which link to remove from the query.

[jackg6]

Possibly because the query establishes links as defined by Relationships builder. As noted, this results in circular relationship. Need to figure out which link to remove from the query.

It seems to me like I can't remove any links because they are all logically necessary for my query to do what I want it to. This query that I posted below is my best attempt but it's really not even close. I used the edited hours query that includes project and resource ID instead of the values from the timesheet and set up the joins in the only way that makes sense to me. It wont work with all of those joins or even any combination of three of them. It also won't work with any combo of join types that I have tried thus far. In order for it to work I have to remove at least 2 joins in which case it displays way more records than it should be. I also tried summing the Hours and allocations fields but the joins still create an error. The query definitely seems to result in a circular relationship but in a scenario where I want to group data from two tables based on two separate fields from two separate tables, I just cannot think of any work around. I think there must be some concept about circular relationships and ambiguous joins that I am just not understanding, but the more I read about it, the less it seems like any of the common work arounds apply even though this seems like it should be a fairly regular scenario.

SELECT [Grouped Allocation qry].SumOfHoursAllocated, [Grouped Hours qry].SumOfDuration, [Grouped Allocation qry].Resource_ID, [Grouped Allocation qry].Project_ID
FROM [Grouped Hours qry] LEFT JOIN (([Grouped Allocation qry] LEFT JOIN Projects ON [Grouped Allocation qry].Project_ID = Projects.Project_ID) LEFT JOIN Resources ON [Grouped Allocation qry].Resource_ID = Resources.Resource_ID) ON ([Grouped Hours qry].Project_ID = Projects.Project_ID) AND ([Grouped Hours qry].Resource_ID = Resources.Resource_ID);