2 Replies Latest reply: May 15, 2014 9:53 AM by Grant Perkins RSS

    Find and Compare question

    Bradl Vogl

      I hope someone can help me with the following.

      I have 2 fields in a database

      "A"field has many duplicates and "B" field has an "S" or nothing.

      I need to find all the duplicates in "A" and display "Y" in a third field if ALL duplicates have an "S" in the "B" field.

      If duplicates in "A" do not have all "S" in the "B" field then display "N" in a third field.

      Example below duplicates 10346 found and not all have an "S"... result "N". Where as, 20449 ALL have "S"......result "Y"

       

      __A_______B_Result

       

      10346_____S___N   

      10346_____ ___N

      20449_____S___Y

      20449_____S___Y

      20449_____S___Y

       

      I appreciate any input.

       

      Thanks,

      Brad Vogl

        • Find and Compare question
          Bradl Vogl

          After reviewing my original post, it appears to be a bit obscure. I hope this additional post may help clerify what I am trying to do.

           

          A portion of the report is below.

          It is a picking list for kits of parts required to build an end product.

          If the entire bill of materials for a particular final product is in our stock the kit-puller pulls the parts and passes them on to the assembly floor.

          If the entire bill of materials is NOT in our stock the kit-puller disregards and does not pull anything.

          To determine if the kit is complete or partial the kit-puller sorts the report by our internal order (the partial report below lists 103828, 103835 and 103937 as our internal oreder #'s.)

          The last field displays whether we have stock with an "S" or displays blank for no-stock.

          I would like to have a summarized listing of orders to pull or not-pull.

          The example below would then result in

          [font="courier"]

          103828       Don't Pull       (Not all items in stock)

          103835       Pull             (All items in stock

          103937       Pull             (All items in stock

          /font[/quote]As there are a few thousand line-items in the report it would be a reall time saver.

          Is this possible to do?

          I appreciate any ideas you may have.

           

          Thanks,

          Brad Vogl

           

          [font="courier"]   160.00   10.00   -  0.00 AB1VV435URO       0        TERM BLOCK          103828 4/18/2005  1           MISC       S 

              0.00   20.00   -  0.00 AB1VV635UBL       0        BLUE TERM BLOCK     103828 4/18/2005  1           MISC2        

             70.00   20.00   -  0.00 AB1VV635UGE       0        ORG TERM BLOCK      103828 4/18/2005  1           MISC2      S 

             20.00    5.00   -  0.00 D2450             0        SOLID STATE REL     103828 4/18/2005  1           MISC2      S 

             20.00    5.00   -  0.00 FNQ-3             0        3A MIDGET FUSE      103828 4/18/2005  1           MISC2      S 

             20.00    5.00   -  0.00 FNQR-1 1/2        0        1.5A TIME DELAY     103828 4/18/2005  1           MISC2      S 

             20.00    5.00   -  0.00 HBC-098           0        HEATSINK            103828 4/18/2005  1           MISC2      S 

          6864.00  325.00   -  0.00 PLT1M             0        4 3/4 CABLE TIE     103828 4/18/2005  1           MICRO PL   S 

            200.00   25.00   -  0.00 PPHMS10-32X3/8    0        ZINC SCREW          103828 4/18/2005  1           MISC2      S 

            200.00   20.00   -  0.00 PPHMS8-32X3/8     0        ZINC SCREW          103828 4/18/2005  1           MISC2      S 

            284.00  200.00   -  0.00 1-480426-0        0        PAN PAC TH-4P I     103835  5/2/2005  1                      S 

          12970.00  800.00   -  0.00 39-00-0038        0        4K/REEL FEMALE      103835  5/2/2005  1           MOLEX      S 

            755.00  200.00   -  0.00 39-01-2040        0        4PIN HOUSING        103835  5/2/2005  1           MOLEX      S 

          5572.00  600.00   -  0.00 61118-1           0        5K/REEL PIN         103835  5/2/2005  1           AMP        S 

          4788.00  150.00   -  0.00 UL1007-18-BLK     0        18AWG 1007 BLK      103835  5/2/2005  1           WIRE       S 

          2128.00  150.00   -  0.00 UL1007-18-YEL     0        0716-84 8"  WIR     103835  5/2/2005  1           WIRE       S 

          33757.00  750.00   -  0.00 08-50-0113        0        PIN 10K/REEL        103937 4/25/2005  1           MOLEX      S 

            420.00  250.00   -  0.00 109P0412B301      0        SANYO               103937 4/25/2005  1           SANYO      S 

          2887.00  250.00   -  0.00 22-01-3037        0        3 POS HOUSING       103937 4/25/2005  1           MOLEX      S 

            492.20   63.00   -  0.00 ST221 1/8 BLA     0        CK  COLE FLEX       103937 4/25/2005  1           TUBING     S 

          2407.00  250.00   -  0.00 UL1007-24-BLK     0        24G WURE BLACK      103937 4/25/2005  1           WIRE       S 

          /font[/quote]

          • Find and Compare question
            Grant Perkins

            Brad,

             

            I suspect we were all looking for a clever way to do this but maybe the benefit of being able to do it at all (and save a load of time) suggests a less clever way would be really good right now!

             

            I see a simple two stage process PROVIDING you have a Pro version of Monarch with external lookup ability. (It is possible with standard Monarch as well but cannot be easily automated as far as I know.)

             

            Stage one would be a simple extraction of all the blank (non S) rows and the creation of a 2 field "table" (using a summary to reduce it to one row per Internal Order Number) for all the kits that cannot be satisfied. (Column 1 = Order number, Col 2 = "No" or whatever you want except S!)

             

            Then run the full model for all order lines. Perform a look up to the external "Table", which could be an Excel worksheet maybe, or an Access database or just a delimited or fixed wodth text file,  and simply add the indicator flag to those Order number lines which are known to be incomplete.

             

            So if the imcomplete flag is 'SET' mark the order as "Don't Pull". Otherwise mark it as "Pull".

             

            How does that sound?

             

            You could do something similar with a summary that displays the value in  the "S" column as an ACROSS field. If there is a record count value > 0 in the 'Blank' columnyou can't pull the order. However if you a specific field that suggests "Pull" or "DON'T PULL" in words you would, I think, still require a 2 stage process.

             

            In that case stage one would be to produce the summary suggested and then EXPORT it as a report.

             

            Stage 2 would then read the report just produced and identify whether an order could be pulled or not. One possible benefit to such an approch might be that the final output could indicate how many component parts were unavailable - but I don;t know if that is of any use to you.

             

            Does this help?

             

            Grant