Compare two fields in different tables and report differences

Hi all,

I am struggling with the following, can't figure it out, sorry.

I have a non-Nav database used for ERP. In this database I have a Jobs table containing header-information for a planning/agenda item (similar to a Job Task i assume) and a BookedRes table containing the booked resources within the Job.

Example of a few fields in these tables:
- Jobs
+ JobKey (unique ID)
+ JobDate
+ JobClientKey
+ JobMProjKey (link to MainProjects table)
- BookedRes
+ BResKey (unique ID)
+ BResDate
+ BResJobKey (link to the Jobs Table, one jobs-record links to several bookedres-records)
+ BResClientKey
+ BResMProjKey

Due to bugs in the software there are sometimes differences between these tables in for example the values in the MPRojKey.

What I would like to do is get a NL("Rows"…) showing the BResKey for those records where the BResMProjKey is not equal to the JobMProjKey of the linked Jobs-record.

Can anyone show me how to do this? Or point me in the right direction?
Any help is appreciated, thanks in advance!!!!

Best regards,
Gerard

Jet Reports Historic Posts October 31, 2016 13:38

Hi

I have done something similar, the solution I found was to return lines from the Master system that included the matching fields as:

NL Rows
Data Source = A

1. Field One
2. Field Two (Etc.)

NL First (Child Table and Field)
Data Source =B
Filter: Field 1, 2 etc.

Then use the Excel If function to compare the results

I hope this helps

Malcolm

Jet Reports Historic Posts November 04, 2016 08:54

Hi John,

Thanks for your answer.
Sorry but, because of the nature of the report, for me this in not the way to go.

Best regards,
Gerard

Compare two fields in different tables and report differences

2 comments