Difference between 2 tables

[chris-uk-lad]

Hi,

I have 2 tables of which have several different values. One is a before changes table and one is a after changes table (tblbefore / tblafter). I want the after table to show all records where the Total is different to what it was before.

So:
Name Ref Total
J Johns A123 1001

If total changed to 2001, it would be displayed in the query. I thought it was <>"" but doesnt work.

Thanks

 

[dkinley]

In the query builder how about something like ...

Compare: IIF([tblBefore].[Total]<>[tblAfter].[Total],1,0)

Then the critieria for this is simply: 1.

Replace the "tblBefore" and "Total" with appropriate information. Picture in the attachment.

I only propose the usage of <> because I wasn't sure how you were doing the evaluation and it should work in this instance. If not, try = and then change criteria to 0.

-dK

 

[chris-uk-lad]

that works fine for 2, thanks, another question, how would 3 work?

So i could have a tblbefore, an expected (tblexpected, same fields), and a tblafter where the before and after tables are compared to the expected table?

 

[dkinley]

Programmers choice .... could nest the IFF statements or have another entry of sorts. There might even be a slicker way than all of this but this is a tried and true method for me and never sought to go that extra mile. Under the guise of Pareto analysis ...

Compare1: IIF([tblBefore].[Total]<>[tblExpected].[Total],1,0)
Compare2: IIF([tblBefore].[Total]<>[tblAfter].[Total],1,0)
Compare3: IIF([Compare1]<>[Compare2],1,0)

With criteria of 1 only under Compare3 to limit the records. Again could nest all of these up under one column, but I like to see all of the data to guarentee it is working right. I would even leave off the criteria initially and randomly test a few lines by viewing to ensure correct result.

-dK

 

[chris-uk-lad]

again worked a treat. Final thing (i hope)

If the expected table was to be blank, the above wouldnt work thus would only want to compare the initial 2 tables. If there a way to incorperate this exception?

 

[dkinley]

No problem. There is a way, I am thinking of using the Nz function ...

IIF(Nz([tblBefore].[Total],0)<>Nz([tblAfter].[Total],0),1,0)

That way if there is nothing in the field, it will be "0" and thus be able to compare.

-dK