Joining a Budget and Actual Table

[Guest]

I am attempting (have been trying to work this out of a few hours now) to
join two tables. Normally this is a simple process (even for someone of
limited abilities such as myself). I know this is a bit long winded but
please bear with me.

TABLE 1 - this contains Acutal $ for Projects and associated tasks.

Project Code Task Code Actual $
1000 501 $10.00
1000 509 $20.00
1000-1 PRO01 $25.00
1000-1 501 $8.00
1000-2 TWC02 $5.00

TABLE 2 - this contains Budget $ for Projects and associated tasks.

Project Code Task Code BUDGET $
1000 973 $35.00
1000-1 PRO01 $19.00
1000-1 501 $75.00
1000-2 YPT55 $38.00
1000-3 501 $99.00

I need to be able to join these two tables by PROJECT CODE & also TASK CODE
in order to get one line for the combination.

So an example of the resulting table would be:

Project Code Task Code ACTUAL $ BUDGET $
1000 501 $10
1000 509 $20.00
1000 973 $35.00
1000-1 501 $75.00
1000-1 PRO01 $25.00 $19.00
1000-2 YPT55 $38.00
1000-2 TWC02 $5.00
1000-3 501 $99.00

My issue is 4 fold:

1) There may be projects that have Actual $, but no Budget dollars.
2) There may be projects that have Budget $ but no Actual dollars.
3) There may be Tasks that have Actual $, but no Budget dollars.
4) There may be Tasks that have Budget $, but no Actual dollars.

I cannot figure out how to join the 2 tables, matched on both Project and
Task codes and include ALL information from both tables.

Any help would be greatly appreciated.

Regards

James

 

[Guest]

Maybe I'm missing something, but maybe what you need is a query, not a join.
A query where Project Code and Task Code match, display the Budget and Actual.

 

[rtiste]

Unless I have misunderstood you, this should work...

Let's say your two tables are called tblActualDollars and
tblBudgetDollars, with respective fields --

ProjectCode
TaskCode
ActualDollars

ProjectCode
TaskCode
BudgetDollars.

1) Make a table comprised of the matching records in both tables,
called, say, tblFinal --

SELECT tblActualDollars.ProjectCode, tblActualDollars.TaskCode,
tblActualDollars.ActualDollars, tblBudgetDollars.BudgetDollars
INTO tblFinal
FROM tblActualDollars
INNER JOIN tblBudgetDollars ON (tblActualDollars.ProjectCode =
tblBudgetDollars.ProjectCode) AND (tblActualDollars.TaskCode =
tblBudgetDollars.TaskCode);

2) Append the records in tblActualDollars *not* in tblBudgetDollars --

INSERT INTO tblFinal ( ProjectCode, TaskCode, ActualDollars )
SELECT tblActualDollars.ProjectCode, tblActualDollars.TaskCode,
tblActualDollars.ActualDollars
FROM tblActualDollars
LEFT JOIN tblBudgetDollars ON (tblActualDollars.TaskCode =
tblBudgetDollars.TaskCode) AND (tblActualDollars.ProjectCode =
tblBudgetDollars.ProjectCode)
WHERE (((tblBudgetDollars.ProjectCode) Is Null) AND
((tblBudgetDollars.TaskCode) Is Null));

3) Append the records in tblBudgetDollars *not* in tblActualDollars --

INSERT INTO tblFinal ( ProjectCode, TaskCode, BudgetDollars )
SELECT tblBudgetDollars.ProjectCode, tblBudgetDollars.TaskCode,
tblBudgetDollars.BudgetDollars
FROM tblBudgetDollars
LEFT JOIN tblActualDollars ON (tblBudgetDollars.TaskCode =
tblActualDollars.TaskCode) AND (tblBudgetDollars.ProjectCode =
tblActualDollars.ProjectCode)
WHERE (((tblActualDollars.ProjectCode) Is Null) AND
((tblActualDollars.TaskCode) Is Null));

Ross La Haye

 

[John Vinson]

I need to be able to join these two tables by PROJECT CODE & also TASK CODE
in order to get one line for the combination.

What you need is a "Full Outer Join" query - which unfortunately is
not dircectly supported in Access. However, you can solve the problem
by creating two partial solutions and using a UNION query to connect
them:

SELECT [Table1].[Project Code], [Table1].[Task Code],
[Table1].[Actual $], [Table2].[Budget $]
FROM [Table1] LEFT JOIN [Table2]
ON [Table1].[Project Code] = [Table2].[Project Code]
AND [Table1].[Task Code] = [Table2].[Task Code]
UNION
SELECT [Table1].[Project Code], [Table1].[Task Code],
[Table1].[Actual $], [Table2].[Budget $]
FROM [Table1] RIGHT JOIN [Table2]
ON [Table1].[Project Code] = [Table2].[Project Code]
AND [Table1].[Task Code] = [Table2].[Task Code]
ORDER BY [Table1].[Project Code], [Table1].[Task Code];

The first part of the query, before the UNION, finds all Table1
records and any matching Table2 records; the second part finds those
Table2 records which don't have any Table1 match. The UNION removes
the duplicates - those records in both tables - and shows you all
records in *either* table.

The suggested MakeTable queries will work but have a lot of extra
overhead and aren't necessary, unless you want the result to be
updateable (the UNION query won't be).

John W. Vinson[MVP]