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 >>