Querie Joins

[StateGov]

I have 3 tables. The first table is the one side that contains project specific information with a primary key (Indexed, No Dups). The second table has the same primary key as a foreign key, however, it contains duplicate information (duplicate primary keys) so it is a many table that contains departmental unit, departmental budget codes, approved $/hours. The third table also has the same primary key as a foreign key and it contains duplicate information (duplicate primary keys) and it also is a many table that shows contains departmental budget codes, actual (spent) $/hours. When I link the project table to either one of these using an inner join, I have no problem, however, when I attempt to link them both...I get no returned recordset. I want to be able to see each departmental unit, the approved $/hours and on the same row, the actual (spent ) $/hours. How can I accomplish this. I first join the main to the approved table and then join the main table to the spent table. What am I doing wrong. Please help, I have been struggling with this for some time and I need to finalize it, as it will be a montly process that I would have to perform. I want to be able to see all of the approved as well as all of the spent so I can see if departmental units that should not be using the budget are charging. Thank you so very much for any assistance that you can offer.

 

[Darrylles]

Hi,

Thank God for the birth of grammar in the English language.

Regards,

Darrylle "Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[Transcend]

Do you think you could word this a little less confusingly .. or use spaces. I just read it three times and i'm still confused.

Transcend

 

[annsolomon]

You don't want to use an inner join because you'll have projects where the hours haven't been approved yet and/or where hours haven't been spent yet. When you set up your relationships you'll want to include ALL records from the project table and ONLY those records from the other two table that have matching records.

By the way, here's an example to illustrate Darrylle's and Transcend's point (which I agree with):

=====================================================
I have 3 tables.

The first table is the one side that contains project specific information with a primary key (Indexed, No Dups).

The second table has the same primary key as a foreign key, however, it contains duplicate information (duplicate primary keys) so it is a many table that contains departmental unit, departmental budget codes, approved $/hours.

The third table also has the same primary key as a foreign key and it contains duplicate information (duplicate primary keys) and it also is a many table that shows contains departmental budget codes, actual (spent) $/hours.

When I link the project table to either one of these using an inner join, I have no problem, however, when I attempt to link them both...I get no returned recordset.

I want to be able to see each departmental unit, the approved $/hours and on the same row, the actual (spent ) $/hours. How can I accomplish this.

I first join the main to the approved table and then join the main table to the spent table.

What am I doing wrong. Please help, I have been struggling with this for some time and I need to finalize it, as it will be a montly process that I would have to perform.

I want to be able to see all of the approved as well as all of the spent so I can see if departmental units that should not be using the budget are charging.

Ann

 

[StateGov]

I wrote this in a hurry. I am very capable of writing correct english, however, when I attempt to explain a problem I am encountering, it doesn't always come up the way I want.

Excuse me for attempting to get help.

 

[annsolomon]

Nobody was chastising you for trying to get help. On this site there are many folks for whom English is a second or third language. We were only trying to help you get better help. Ann

 

[annsolomon]

By the way, you didn't say whether my information resolved your problem or if you still need some help. Holler back and let us know, okay? Ann

 

[StateGov]

No, I still have not been successful. I am pasting my query below, maybe this will provide some insight into exactly what I am trying to accomplish:

SELECT tblProjs.ProjID, tblProjs.Perc, tblAppr.DeptID, tblAppr.Dept_name, tblAppr.Budgeted_hours, tblActuals.Spent_Hours
FROM (tblProjs INNER JOIN tblAppr ON tblProjs.ProjID = tblAppr.ProjID) LEFT JOIN tblActuals ON (tblAppr.ProjID = tblActuals.ProjID) AND (tblAppr.DeptID = tblActuals.Dept_name)
ORDER BY tblProjs.ProjID;

This privides me with all data from the tblProjs as well as all data from tblAppr, however, it does not provide the data from the tblAppr (i.e. Spent_Hours).

I am attempting to see how budgeted hours have been spent against what has been budgeted. Additionally, I would like to see Spent_hours where no budgeted hours have been allocated.

I spoke with someone today and they suggested that I remove any references to DeptID and Dept_name and create a separate table with just this information and to use it as a joiner table so that I can obtain the results I am seeking.

The problem is this:

I have tried every join possible and still cannot produce the results I am seeking.

Any suggestions that can be offer would be greatly appreciated. Thank you.

 

[StateGov]

PS. I forgot to mention that I would also like to know which table the DeptID and Dept_name would be extracted from since it does exist in tblAppr as well as the newly created table(not listed above).

Thank you.

 

[annsolomon]

Your first join should also be a LEFT join.

I would include the DeptID and Dept_name in the query but would not attempt any relationship there. You can always sort/group on the department in queries or reports if you'd like.

You could set up aliases in your queries to show you which table the field is coming from. Instead of just typing DeptID in the QBE grid you could type ApprDeptID: [tblAppr].[DeptID] in one column and ActualsDeptID: [tblActuals].[DeptID] in another column. Ann