Getting Cartesian Product in query but Access 2003 prevents outer

[Ann Scharpf]

I am creating my first database, so I might be doing things
incorrectly/inefficiently here ... but this, in part, is what I've done.
Pertinent to this question are three tables and a query. I am working at a
goverment facility and am calculating salary expense data for a fiscal year
(crossing 2 calendar years with different pay rates.)

1. Table:Civilian payroll. 57 records. Salary level info & FY BEGIN/END
dates for each person (to accomodate mid-year hires & terminations.) Has
benefits rate, COLA etc.
2. Table:Standard variables (COLA, Benefits rate etc)
3. Table:Raw timesheet data, imported from time system
4. Query:Civilian overtime cost. Query is agains timesheet data. Totals
time with overtime time categories and multiplies by civilian overtime rate.
(Same is used for all employees.) Results table has 4 rows.

I am now trying to create a second query. The query worked fine when it
just used the first two tables (payroll & standard variables). Worked like a
gem for calculating the salary for each part of the year. Adds up correctly
and everything.

Now I want to add the overtime values for the 4 people who have overtime.
When I add the overtime cost query to the current query, I get 228 rows. So
I looked in my handy Access book and thought a left outer join would fix my
problem. (Payroll table on left, OT query on right). But when I try to run
the query, I get an error that says:

The SQL statement could not be executed because it contains ambiguous outer
joins. To force one of the joins to be performed first, create a separate
query that performs the first join and then include that query in your SQL
statement.

How do I get Access to do this? Thanks very much for any help you can give
me.

 

[KARL DEWEY]

Post your SQL.
Open the query in design view and click on menu VIEW - SQL View. Copy and
paste in post.

 

[John Vinson]

When I add the overtime cost query to the current query, I get 228 rows. So
I looked in my handy Access book and thought a left outer join would fix my
problem. (Payroll table on left, OT query on right). But when I try to run
the query, I get an error that says:

Please go back to the inner join and open the query in SQL view, and
post the SQL here.

An outer join WON'T get rid of records - rather the opposite, it will
usually give you more.

John W. Vinson[MVP]

 

[Ann Scharpf]

Here is the SQL view as you and John Vinson directed:

SELECT [Civilian Payroll].[Employee Name], Round(([CY1 End Date]-[FY Start
Date])/365,2)*[Salary] AS SalaryCY1, Round(([FY End Date]-[CY2 Start
Date])/365,2)*[Salary]*(1+[COLA]) AS SalaryCY2,
(([SalaryCY1]+[SalaryCY2]))+(([SalaryCY1]+[SalaryCY2])*[BenefitsPercent])+[Bonus] AS TotalSalaryBenefitsBonus, CivilianOvertimeCost.OTcost AS Expr1
FROM [Standard Variables], [Civilian Payroll] LEFT JOIN
CivilianOTCostOutputTable ON [Civilian Payroll].[Employee Name] =
CivilianOTCostOutputTable.EmployeeName;

Thanks for helping me out!

 

[Ann Scharpf]

My post to Karl has the outer join intact. Here is the SQL when I change to
an inner join. (But now I only get ONE record in the Datasheet view!)

SELECT [Civilian Payroll].[Employee Name], Round(([CY1 End Date]-[FY Start
Date])/365,2)*[Salary] AS SalaryCY1, Round(([FY End Date]-[CY2 Start
Date])/365,2)*[Salary]*(1+[COLA]) AS SalaryCY2
FROM [Standard Variables], [Civilian Payroll] INNER JOIN
CivilianOTCostOutputTable ON [Civilian Payroll].[Employee Name] =
CivilianOTCostOutputTable.EmployeeName;

Thanks again.