[AccessD] 8 columns of data to 6 output fields

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



More information about the AccessD mailing list