Problem with Join between 5 tables - tables described at bottom

[DESTINEEJSH]

Crystal 9.0
Prtime is the driving program.
Budget may have a value in it, but not a corresponding record in Prtime.
Currently all joins are set as Inner Joins, except the join between PayPd and Budget.

I want all Budget records to display, even if there isn’t a corresponding record in Prtime, but am currently only getting records in both Prtime and Budget. I know the outer join is in the wrong place, but when I try to switch the joins I get messages that the “engine doesn’t support this”.

Do I have to disable all of the joins and make Budget the driving program? Or does anyone know how I would switch the joins so that I get both what’s in Budget and Prtime and what’s only in Budget?

See tables described below.

Prtime table (inner join to Depts table - Dept to Dept)
Relationship: Many
Employee
Dept
Year
Per_End-Date
PayGrp

Depts table (inner join to Budget - Dept to Dept)
Relation: One
Dept
Descr
Manager

Budget table (left outer join from PayPd to Budget)
(Year to Year, PayPerNo to PayPerNo)
Relation: One
Dept
PayPerNo
Desc
Year

PayPd table (inner join between Prtime to PayPd)
(Year to Year, PayPerNo to PayPerNo)
Relation: One
PayPerNo
Year
PayEndDate

PayClass table (join to Prtime - PayGrp to PayGrp)
Relation: Many but One with Index set: PayClass=FTE
Company
PayClass (many)
PayGrp
PayGrpDesc

 

[Turkbear]

Hi,
The “engine doesn’t support this”. message will only help us if you supply the following:
Database( including and Version and OS)
Connection method..






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[DESTINEEJSH]

Sorry about that, I forgot the database. It's SQL Server 2000.

Deb S.

 

[synapsevampire]

Make the budget table your primary table, and left outer join it to since you want all rows from that table.

Note that if you have criteria on any child table, it will observe it, so you'll have additional comncerns with that.

A View or SP might be your best method, or you may end up using a subreport.

-k