Access query returns empty fields depending on how table is linked

Question

I've got an Access MDB I use for reporting that has linked table views from SQL Server 2005. I built a query that retrieves information off of a PO table and categorizes the line item depending on information from another table. I'm relatively certain the query was fine until approximately a month ago when we shifted from compatibility mode 80 to 90 on the Server as required by our primary application (which creates the data). I can't say this with 100% certainty, but that is the only major change made in the past 90 days. We noticed that suddenly data was not showing up in the query making the reports look odd.

This is a copy of the failing query:

SELECT dbo_porel.jobnum, dbo_joboper.opcode, dbo_porel.jobseqtype,
    dbo_opmaster.shortchar01, 
    dbo_porel.ponum, dbo_porel.poline, dbo_podetail.unitcost

FROM ((dbo_porel 
LEFT JOIN dbo_joboper ON (dbo_porel.assemblyseq = dbo_joboper.assemblyseq) 
    AND (dbo_porel.jobseq = dbo_joboper.oprseq) 
    AND (dbo_porel.jobnum = dbo_joboper.jobnum)) 
LEFT JOIN dbo_opmaster ON dbo_joboper.opcode = dbo_opmaster.opcode) 
LEFT JOIN dbo_podetail ON (dbo_porel.poline = dbo_podetail.poline) 
    AND (dbo_porel.ponum = dbo_podetail.ponum)

WHERE (dbo_porel.jobnum="367000003")

It returns the following:

jobnum    opcode  jobseqtype  shortchar01  ponum  poline  unitcost
367000003            S                     6624       2      15

The query normally should have displayed a value for opcode and shortchar01. If I remove the linked table dbo_podetail, it properly displays data for these fields (although I obviously don't have unitcost anymore). At first I thought it might be a data issue, but I found if I nested the query and then linked the table, it worked fine.

For example the following code works perfectly:

SELECT qryTest.*, dbo_podetail.unitcost

FROM (

    SELECT dbo_porel.jobnum, dbo_joboper.opcode, dbo_porel.jobseqtype,
        dbo_opmaster.shortchar01, dbo_porel.ponum, dbo_porel.poline

    FROM (dbo_porel 
    LEFT JOIN dbo_joboper ON (dbo_porel.jobnum=dbo_joboper.jobnum) 
        AND (dbo_porel.jobseq=dbo_joboper.oprseq) 
        AND (dbo_porel.assemblyseq=dbo_joboper.assemblyseq)) 
    LEFT JOIN dbo_opmaster ON dbo_joboper.opcode=dbo_opmaster.opcode

    WHERE (dbo_porel.jobnum="367000003")

) As qryTest 
LEFT JOIN dbo_podetail ON (qryTest.poline = dbo_podetail.poline) 
    AND (qryTest.ponum = dbo_podetail.ponum)

I'm at a loss for why it works in the latter case and not in the first case. Worse yet, it seems to work intermittently for some records and not for others (it's consistent about the ones it does and does not work for).

Do any of you experts have any ideas?

Answer 1

I'm always confused by Access' use of brackets in joins. Try stripping out the extra brackets.

FROM 
    dbo_porel 
LEFT JOIN 
    dbo_joboper ON (dbo_porel.assemblyseq = dbo_joboper.assemblyseq) 
        AND (dbo_porel.jobseq = dbo_joboper.oprseq) 
        AND (dbo_porel.jobnum = dbo_joboper.jobnum)
LEFT JOIN 
    dbo_opmaster ON (dbo_joboper.opcode = dbo_opmaster.opcode)
LEFT JOIN 
    dbo_podetail ON (dbo_porel.poline = dbo_podetail.poline) 
        AND (dbo_porel.ponum = dbo_podetail.ponum)

OK the above doesn't work - Sorry I give up

Answer 2

You definitely need to use subqueries for multiple left/right joins in Access.
I think it's a limitation of the Jet optimizer that gets confused if you're just chaining left/right joins.

You can see that this is a recurrent problem that surfaces often.