Comparing Tables/files

[mcgrcoAgain]

As part of my job I am constantly comparing files from a production environment against those from a UAT environment. The checks are generally the generic before I reconcile the monetary values I check that the static data population is the same. Basically I import the files into access and then do a like for like comparison on the number of blanks per field. Eg.

Field Blanks_UAT Blanks_Pro Diff
Coustomer_Number 10 2 8



Is there any way of doing this programmable so I can reuse it or are there any tools out there that do this work for me. Some of the files contain a large number of fields so the work can become rather cumbersome!

Any help is appreciated

 

[Dennisk]

I'm not quite sure I understand what you wish to do. Do you want to report on all blank fields in a row, shoing the key of the row and the name of NULL columns?

 

[mcgrcoAgain]

I guess what i want to do is import a file and automatically know how many blanks are in each column

 

[stallyon]

have a group query with no grouped field, then for each field that you want to check have the following iif expression:

Sum(iif([Field name to check goes here] Is Null,1,0))

if the field is blank (null) it will return a 1 if it isnt blank it will return a 0, then we add up all the 1s