Crystal v10 report missing records when criteria entered

[samprocom]

I have a crystal report set up based on an access query, the query is using linked tables from Sage MMS accounts. The query has been set up with left outer joins and I can see that all data exists in the query, however, when I enter criteria I only get matching records. I have tried pulling the tables into crystal and using left outer joins but again as soon as I put criteria in I only get the matching records.

I have created a make table and running a query from that I get all records when I enter criteria. The problem with this is that when I try to run a report pointing at this new query I get the following error message:

Query Engine Error: 'DAO Error Code: 0xc4f'
Source: DAO.Database
Description: ODBC -- connection to 'MMSSTDAVCC' failed.

The ODBC is valid as I can still retrieve data via this ODBC using MS Query.

Any ideas?

 

[synapsevampire]

You need to read up on how filtering works.

if you filter rows, then that's what is returned by the database first and foremost, the database won't guess that you only meant to filter for the rows that aren't part of the null side of the left outer.

Since you didn't share the tables, the joins nor the criteria we can't really help with the first concern, which you must have understood and your question is with the Make table connectivity.

Not sure about the Make table side, I've never tried that, perhaps it's permissioning, not even sure why you went that route.

You should be able to use a stored procedure though, or perhaps Views.

-k

 

[samprocom]

Thanks. Here's the sql code for the query join in Access. I want to return all records that exist in the nominal ledger table and matching records from the nominal transactions table which is what the query does but as soon as I enter criteria to only see records for a specific department I only get the records that exist in both tables table. (eg 3 records exist in nominal ledger but only the 2 that exist in nominal transactions are returned with criteria on). I've brought the table directly into crystal with left outer joins not enforced but again when I enter parametres I only get the matching records. I've only used the make table to make sure I get all records returned.

FROM ((ACCOUNTING_SYSTEM_NOMINAL_LEDGER LEFT JOIN ACCOUNTING_SYSTEM_NOMINAL_TRANSACTIONS ON ACCOUNTING_SYSTEM_NOMINAL_LEDGER.THIS_RECORD = ACCOUNTING_SYSTEM_NOMINAL_TRANSACTIONS.PARENT_RECORD) LEFT JOIN ACCOUNTING_SYSTEM_PURCHASE_TRANSACTIONS ON ACCOUNTING_SYSTEM_NOMINAL_TRANSACTIONS.UNIQUE_REFERENCE_NO = ACCOUNTING_SYSTEM_PURCHASE_TRANSACTIONS.UNIQUE_REFERENCE_NO) LEFT JOIN ACCOUNTING_SYSTEM_PURCHASE_LEDGER ON ACCOUNTING_SYSTEM_PURCHASE_TRANSACTIONS.PARENT_RECORD = ACCOUNTING_SYSTEM_PURCHASE_LEDGER.THIS_RECORD;

 

[Turkbear]

Hi,
If your criteria are using data fields from the Joined Table ( the right hand one), then the Left Outer is 'converted' to an Equi Join and will behave as described..






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