[AccessD] 8 columns of data to 6 output fields

Hi All,

This is causing me a bit of a headache and I wondered if anyone could 
think of of a neat and easy way to accomplish my needs.

Basically I have a table where I need to extract data from a combination 
of 8 columns in order to fill 6 output fields.

I have created 8 lookup tables as I also need to map the values in the 8 
fields to a standard set of codes required for the output.

Two of the fields contain slightly different data to the other 6 which 
also complicate things further.

Not all of the fields may contain data that is needed for the output either.

So, I need to process the two "odd" fields first to ascertain if they 
contain data that needs outputting, and if they do, potentially fill the 
first two output fields.

If the first of the "odd" fields contains data that has to be output 
this needs to go in the first output field. If this "odd" field does not 
contain suitable data then I need to check the second "odd" field to see 
if this contains data for the first output field.

If both of the "odd" fields contain suitable data then they need to fill 
the first two output columns.

So, after processing these fields I may have none, one or two of the 
output fields populated.

I then need to check the remaining 6 fields and put the mapped data into 
the output fields. So, I need to check the first of the second set of 
fields and, if the first output field is empty after the first steps 
above, put this into the first output field. If the first output field 
is filled then this would need to go in the second output field. If this 
was filled from the steps above, then this would need to go in the third 
output field.

The same procedure then needs repeating for the other 5 fields to 
populate the output fields as described above. As the output fields fill 
up the next field needs to be filled until I possibly fill all six 
output fields. This may happen before I finish checking the second set 
of six fields as there would possibly be two fields filled from the 
first pass leaving only 4 to be filled from the other six fields.

I have sort of got something running by a horrible iif statement for the 
first of the set of fields but not only is this horrible I can't now get 
my head around what I need to do next !!

Hope that someone might be able to come up with something.

Thanks in advance,

Chris Swann