Question on Find Unmatched Query Wizard

[vickster3659]

Hello,


I have built a query using the wizard to find unmatched records. The two tables are the same, except for one field. The left table is tblImportIHOSData, the right table is tblIHOSIssuedPayments, with the join on field name DraftNumber. This query gives me most of the expected results.

I would like to match on two more fields (DraftDate and Amount), as I feel this would give the complete results. Some records dropped because the DraftDate was different in each table. If I can match on DraftDate would that show me the records that dropped?

Thank you, in advance!
Vickster3659

[June7]

Is DraftNumber a unique value in both tables?

You mean join on only the DraftDate fields? Doesn't hurt to try and see what happens. However, since more than one record in each table likely have the same date, the output might be misleading. Let us know how it goes.

If you mean DraftNumer and DraftDate, try it.

[JoeM]

By default, the Unmatched Query Wizard only matches on one field. But you can match on more than one. It is pretty easy to change that.
Just go ahead and do the match on one field, like you normally would.
Then open the Query in Design View. You will see the two tables linked on a field. To add another field, click the field in one table, hold the mouse button, and drag and drop on the field in the other table that you want to link to.
Then note how the original link has an arrow at one end. Double-click the new join line you just set up, and choose the appropriate link option to get an arrow pointing in the same direction (if you pick the wrong option initially, just try it again).
If you have more than two fields you need to link on, just repeat these steps for adding joins. When complete, all your joins should have arrows pointing in the same direction.

[vickster3659]

Is DraftNumber a unique value in both tables?

You mean join on only the DraftDate fields? Doesn't hurt to try and see what happens. However, since more than one record in each table likely have the same date, the output might be misleading. Let us know how it goes.

If you mean DraftNumer and DraftDate, try it.

Hi June7,
DraftNumber is not a unique field in the sense that it is a key field, as multiple records can have the same DraftNumber and also the same DraftDate.

A little history: this file is a daily file, in which new records come on each day, and old records come off each day. On the next day, when I get the file, it will have the prior day's(Issued) records that are left after the old (Paid) records come off. I am only interested in the Issued each day, as I am trying to match bank files on DraftNumber later on, on a monthly basis. So when I get the next day's file, I only want the records that are new to that day. Some records are new but may have a back-dated DraftDate, so it is not a simple matter of appending records with the current DraftDate. Each day's file is >250,000 records, but I may only have to append <1,000 each day.

I currently have a very manual process to reconcile each day, I was hoping that the Unmatched query would work, as the manual process takes quite a while.

Thanks,
Vickster3659

[vickster3659]

By default, the Unmatched Query Wizard only matches on one field. But you can match on more than one. It is pretty easy to change that.
Just go ahead and do the match on one field, like you normally would.
Then open the Query in Design View. You will see the two tables linked on a field. To add another field, click the field in one table, hold the mouse button, and drag and drop on the field in the other table that you want to link to.
Then note how the original link has an arrow at one end. Double-click the new join line you just set up, and choose the appropriate link option to get an arrow pointing in the same direction (if you pick the wrong option initially, just try it again).
If you have more than two fields you need to link on, just repeat these steps for adding joins. When complete, all your joins should have arrows pointing in the same direction.

Hi JoeM,
I selected the additional two fields and made the join the same as the DraftNumber join, but I get no records. As I mentioned in my response to June7, I do have a very manual process, but hoping I can get this Unmatched to work. Worse case scenario, I may still use it, and then manually go find the remaining records missing from my reconciliation, as my first Unmatched did return quite a few of what I was missing.
Thanks,
Vickster3659

[June7]

So what is the rule?

Find records in daily that do not have a DraftNumber/DraftDate pair in main?

[ItsMe]

...When complete, all your joins should have arrows pointing in the same direction....

And don't forget to add the field from the correct side and an Is Null criteria.

[vickster3659]

Hello,
I have been working with this query, and I think I have it working correctly. I want to test it for a couple of days, and if my results match my manual reconciliation, I thank you all for your assistance. If not, I may have more questions.
Thanks,
Vickster3659