select

[StanKorn]

Hi,
I am trying to create a bank reconciliation report and my brain refuses to work today. I need to extract only the records from my AP system that do not match any records in the bank trnsaction file I receive daily. Can anyone help me with this?
Thanks,
Stan

 

[Naith]

Is there some reason why you cannot use a left outer join between your AP and transaction file datasources, and use a selection criteria of AP.Records where IsNull(File.Records)? Or is it not possible to link the two datasources?

Naith

 

[StanKorn]

Dear Naith;
I can link the two on check number but i only get 1 to 1 matched data ie. chk# 123 = chk# 123. I want to get the data where AP chk# has no matching Bank Check # and where bank memo has no corresponding value in AP. Does this make sense. I am tracking approx 10,000 trnsactions peer week and it is incredibly time consuming.
Thanks for your help,
Stan

 

[lbass]

Are you able to do joins other than equal joins? Some databases have limitations on the kind of joins you can do.

From your second post, it sounds like you want to find all records that don't have matching fields in either table, and for this, you might want to try a "not equal join."

-LB

 

[Naith]

In your Visual Linking Expert, specifically try to set the join between your check ids to Left Outer Join. If you want to get "data where AP chk# has no matching Bank Check #" and "where bank memo has no corresponding value in AP" it seems you will have to have a Left Outer Join going from AP to the Bank Transaction file for the first one - but from what you're saying you want to reverse the join for the second criteria, for which you will need a subreport with the join reversed.

Rather than overcomplicate what you need to do really early on, just try getting a Left Outer Join from AP to the Bank Transaction file, and then setting your Record Selection Criteria to Not IsNull({AP.Field}) and IsNull({Bank.Field}).

Naith