David McAfee
davidmcafee at gmail.com
Tue Nov 16 15:23:56 CST 2010
Just type up some similar data so we can all see what you are trying to accomplish. On Tue, Nov 16, 2010 at 1:08 PM, Chris Swann <dc8 at btinternet.com> wrote: > 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 >>> > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com >