Help with reports, returning different values of more than one record?

Any help on this would be great.

I have a table "Gap Analysis" and a form "Gap Analysis", the form/table consists of Tick boxes and although the data imputed goes back to the Gap Analysis table as ticks (which I don't want to change), I'd like a second table called "IQ Report Table" where the data from the Gap Analysis Form is recorded differently:

I'm looking for a code to calculate the following:

i.e

Data Imput in the Gap Analysis Form

Record Id /Asset No /Test Satisified /Not Applicable

1 /60001 /Yes /No
2 /60001 /No /No


Returned Values recorded to the "IQ Report Table" as:

Record Id /Asset No /Test Satisfied /Not Applicable

1 /60001 /"No Gap" / Blank
2 /60001 / Blank / Blank


But in the same form, the results in the form might be:

i.e

Record Id /Asset No /Test Satifisfied /Not Applicable
3 / 50000 /No / No
4 /50000 /No /Yes

So the result in the IQ Report Table is:

Record Id /Asset No / Test Satifisfied / Not Applicable
3 /50000 / Blank / Blank
4 /50000 / Blank / "N/A"

Or all the results might be negative:

i.e

Gap Anaylsis Form

Record Id /Asset No / Test Satifisfied / Not Applicable
5 /70002 / No / No
6 /70002 / No / No

IQ Report Table

Record Id /Asset No /Test Satifisfied / Not Applicable
5 / 70002 / "Gap" / Blank
6 / 70002 / Blank /Blank

Or if their is only a single record:

Record Id /Asset No /Test Satifisfied / Not Applicable
7 /85000 /Yes / No

So the results:

Record Id /Asset No /Test Satifisfied / Not Applicable
7 /85000 / "No Gap" / Blank

Is there a code that can do all this.




Thanks you in advance for your help!!