Chris Swann
dc8 at btinternet.com
Tue Nov 16 15:19:49 CST 2010
Hi again, This is some of the data that I have to work with. As mentioned previously, not all the columns contain data. The first two columns are the "odd" ones that need processing first. ADZ LARC CONU LARC 01A CONFDIS 01A LARC 02B CONFDIS 02B 01A LARC CONFDIS 02B 01A LARC 6 4 27A 23 9 7 LARC LARC 9 CONFDIS 01A 01C LARC LARC 02B 01A CONFDIS 01A 02A 09C 27B ECSC LARC 09B 01B This is the mapping table for the "odd" fields *tlkpSRH_CA_RH012* RH012_Printable Result 1 Mapped 1C ZZZ 1D ZZZ 2 ZZZ 22 22 23 23 3 21 3C 21 3N 21 4 ZZZ 4A ZZZ 5 ZZZ 6 20 6A 20 7 ZZZ 7A ZZZ 8 ZZZ 9 19 9N 19 F2 ZZZ F3 ZZZ F4 ZZZ F5 ZZZ LARC 1 LARCNOT ZZZ and this is the mapping for the other 6 fields *tlkpSRH_CA_RH016* RH016_Printable Result 1 Mapped 01 1 01A 1 01B 1 01C 1 02A 2 02B 2 03A 3 03B 3 03C 3 08 8 09A 9 09B 9 09C 9 09D 9 09E 9 11 11 12A 12 12B 12 13A 13 13B 13 17A 17 18A 18 24A 24 25 25 27A 27 27B 27 27C 27 27D 27 33A 33 33B 33 ABA ZZZ ABPAS ZZZ ABZ ZZZ ADC ZZZ ADS ZZZ ADZ 1 AEXTNHS ZZZ AFC ZZZ APASY ZZZ CC ZZZ CONFDIS ZZZ CONU ZZZ DAR ZZZ DRD ZZZ ECSC ZZZ ECSGC ZZZ LARC 1 LARCNOT ZZZ MEA ZZZ NONLCON ZZZ OTP ZZZ PRC ZZZ PRI ZZZ PSC ZZZ PSR ZZZ PTN ZZZ PTP ZZZ UCH ZZZ USC ZZZ USD ZZZ USO ZZZ YTA ZZZ The fields shown ZZZ in these tables are not mapped to appear in the out data, a sample of which is below This is sort of what the data is meant to look like when finished. Its not complete because I haven't worked out how to fill the last 5 columns yet !! SRH_CA1 SRH_CA2 SRH_CA3 SRH_CA4 SRH_CA5 SRH_CA6 1 1 1 1 1 19 1 1 1 1 1 11 Thanks again for any help. 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 >>