[AccessD] 8 columns of data to 6 output fields

Chris Swann dc8 at btinternet.com
Tue Nov 16 15:08:06 CST 2010


Hi,

Thanks for getting back.

What would be the best way to get the data to you ? I can't supply the 
whole table as it contains confidential data in a lot of the fields ?

C

On 16/11/2010 20:48, David McAfee wrote:
> Can you show some sample table data and output data?
>
>
>
> On Tue, Nov 16, 2010 at 11:41 AM, Chris Swann<dc8 at btinternet.com>  wrote:
>> 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
>> --
>> AccessD mailing list
>> AccessD at databaseadvisors.com
>> http://databaseadvisors.com/mailman/listinfo/accessd
>> Website: http://www.databaseadvisors.com
>>



More information about the AccessD mailing list