Outer Join not working properly?

[Rwathen10]

Hello, I have been struggling and missing deadlines due to this, so I will REALLY appreciate a solution. NOTE: For an expert, this should be very easy, so please help me . I included a sample already. If possible, please show me the way to accomplish this through a query; I don't know SQL yet. If not, please solve with SQL.

I basically have two tables that I want to do a sort of join (perhaps outer join) on. I work in Payroll so the challenge is about payroll data. When I try to do calculated queries for variances, not all my entries on both tables are evaluated against each other.

I have two tables
1) Budgeted positions by cost center
2) Actual Payroll charges by position/cost center

Table (1) shows all the Jobs in my company and how much is budgeted for their salaries each year by each cost center. Each position has a unique “Position#” and some positions are funded by more than one cost center. For example, Position#1064 is funded/budgeted 100% by cost center 14589, while position# 5015 is funded 50% by cost center 20530 and 50% by cost center 20531.



Table (2) Has Payroll Data from our database, Cognos, which shows how much was actually drawn from each cost center by each position. I discovered that position#1064 actually drew funding from 14589 AND 25732 (vs. just 14589). And that position 5015 drew 100% of its funding from cost center 20530 (vs. 50/50 with cost center 20531)

Question:
How can I build a query so that I can see a variance when a position that is not budgeted in that cost center, charges to that cost center? And also see when a position that should have charged two different cost centers only charges one? I basically need the output to show me when the actual position#/costcenter# combination is different from the budgeted position/cost center combination.Question.accdbQuestion.accdb

Please see my attachment for your convenience!!!!! I will do anything for you! I am new at this job, so I really need this!

[orange]

In relational data base every table requires a primary key to uniquely identify each record in that table.
You have no primary keys defined.
Access does not like embedded spaces or special characters in field names.
Combo Code (Account) and Position # are bad examples of field names in Access.

What exactly do you think the query is doing? And why do you think the RIGHT join isn't working?

Here are a few counts that may mean something to you.
There are 1293 records in Actuals, but only 1239 unique position numbers.
There are 754 records in the Budgeted but only 751 unique position records.

I think this query gives you the Hours and Amounts for Positions and Cost Centers that are in Actuals but are not in the Budgeted, but I don't know your data.

SELECT [ActualHours&Salary(1 payperiod)].[Position Nbr]
, [ActualHours&Salary(1 payperiod)].Charge
, [BudgetHrs&Salary(Per Payperiod)].[Position #]
, [BudgetHrs&Salary(Per Payperiod)].[Combo Code (Account)]
, [ActualHours&Salary(1 payperiod)]![ActualHoursWorked] AS Varhrs
, [ActualHours&Salary(1 payperiod)]![ActualEarned] AS VARaMT
FROM [ActualHours&Salary(1 payperiod)] LEFT JOIN [BudgetHrs&Salary(Per Payperiod)] 
ON ([ActualHours&Salary(1 payperiod)].Charge = [BudgetHrs&Salary(Per Payperiod)].[Combo Code (Account)]) 
AND
 ([ActualHours&Salary(1 payperiod)].[Position Nbr] = [BudgetHrs&Salary(Per Payperiod)].[Position #])
WHERE
((([BudgetHrs&Salary(Per Payperiod)].[Position #]) Is Null) AND
 (([BudgetHrs&Salary(Per Payperiod)].[Combo Code (Account)]) Is Null));

Here are a few sample output records from that query. Run the query against your table to see all.

Position Nbr
Charge
Position #
Combo Code (Account)
Varhrs
VARaMT

00001064
25732




18.739995
1423.59

00001456
17232




53.582913
3786.04

00001594
21520




75
5504.86

00001602
21520




75
1900.88

00001846
14280




9.370004
579.04

00003112
25722




46.93
1123.71

00003133
14470




28.11006
1807.67

00003143
25733




37.439994
2854.38

00003154
25722




60
1444.43

00003811
16630




75
4492.54

00003813
16570




56.25
3547.65

I hope this is helpful to you.

[Rwathen10]

Thank you, wise one. Believe it or not, all you see there was accomplished with about one month of exposure to Access! I think that my query is looking at all the Positions in the "budget" table and telling me whether there was any corresponding position on the "actual" table and, if so, then subtracting the actual payroll charges from the budgeted payroll charges. I don't believe this join is ideal because at times there are ACTUAL expenses that are not budgeted (see Actual table, position# 00004029), where if there wasn't a value on the table on the left, it will not come up in the output. Is there a solution for this?

[Rwathen10]

Actually, I read your reply. Thank you for the fast response. I see what you did with your query, I will dissect it. Your Query DID help. Thanks again

[orange]

Happy to help.
Good luck with your project.

To return a 0 if a field/variable is Null, you could use the NZ() function.

NZ(fieldThatIsNull,0)

see http://www.techonthenet.com/access/f...dvanced/nz.php