Paul Hartland
paul.hartland at googlemail.com
Wed Nov 17 01:18:44 CST 2010
Chris, If you still havent solved this could you mock up a quick access db with some dummy tables/data, zip it up and mail me off list ( paul.hartland at googlemail.com) and I will have a quick look today, as I think I know exactly what you want. Paul On 16 November 2010 21:19, Chris Swann <dc8 at btinternet.com> wrote: > 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 > >> > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > -- Paul Hartland paul.hartland at googlemail.com