3 Replies Latest reply on Oct 11, 2013 2:52 AM by TimDietrich

    Help required ASAP to design a new layout within my database

    danielle-gwa

      Hi everyone,

       

      I need some help asap:-

       

       

      The main table in my database contains 4000 parts (PART table)

       

      I have other linked tables- for example a table that show usage of parts at different sites on specified dates- which links back to the PART table via part number

       

       

       

      I need to create a report that shows me which of the 4000 parts are used on 3 of the sites within a certain 2 year time period chosen individually for each site.

       

      e.g.

      Part number 12345 is used 3 times on SITE A within the date period specified 01/01/2010 to 01/01/2012

       

      Part number 12345 is used 2 times on SITE B within the date period specified 01/01/2006 to 01/01/2008

       

      Part number 12345 is used 5 times on SITE C within the date period specified 01/03/2010 to 01/03/2012

       

       

      Can anyone help me design a layout within my system to allow me to do this??

       

      Help asap would be very very appreciated!

       

      thanks

       

      Danielle-gwa

        • 1. Re: Help required ASAP to design a new layout within my database
          TimDietrich

          Danielle --

           

          What makes this tricky is the need to get not only the number of times that a part was used at each site, but the specific date range usage for each part during the two year period.

           

          If I were working on this, I would use ExecuteSQL to get the summarized data, store it in a reporting table, and then base the report / layout on that table. The ExecuteSQL would look something like this:

           

          ExecuteSQL ( "

          SELECT

                    Part_Number,

                    Site,

                    Count (*),

                    MIN (Usage_Date),

                    MAX (Usage_Date)

          FROM

                    Part_Usage

          WHERE

                    Usage_Date BETWEEN ? AND ?

          GROUP BY

                    Part_Number,

                    Site

          ORDER BY

                    Part_Number,

                    Site

          "; "|"; "\n"; $date_range_begin; $date_range_end );

           

          Obviously, I've guessed at the field names that you are using - and there would be more to it than just this ExecuteSQL call. (You'd need to loop over the results and store them in a temp table, etc.) But hopefully this will give you something to get started with...

           

          Good luck!

           

          -- Tim

          • 2. Re: Help required ASAP to design a new layout within my database
            danielle-gwa

            Hi tim thanks for the advice

             

            I think i have managed to pull together a search portal to give me the data i want for the 3 sites within each of their date period specified.

             

            Next step:

             

            How do i now within this found set search for only those part numbers that have usage on ALL 3 SITES? Is there a search i can do within this found set to show me the say 5 part numbers within the found set that are used on SITE A, SITE B and SITE C?

             

            Thanks for the help

             

            Danielle

            • 3. Re: Help required ASAP to design a new layout within my database
              TimDietrich

              Danielle --

               

              I know you said that you'd trying to use a portal, but again, I'd approach this using ExecuteSQL. This time, I'd use two queries.

               

              The first would identify the part numbers that were used in all three sites during the date range. Let's set this to a variable named $part_numbers and the calculation would look like this...

               

              ExecuteSQL ( "

              SELECT DISTINCT

                        A.Part_Number

              FROM

                        Part_Usage A

                        JOIN Part_Usage B ON ( ( B.Part_Number = A.Part_Number ) AND ( B.Site = 'SITE B' ) )

                        JOIN Part_Usage C ON ( ( C.Part_Number = A.Part_Number ) AND ( C.Site = 'SITE C' ) )

              WHERE

                (A.Site = 'SITE A')

                        AND ( Usage_Date BETWEEN ? AND ? )

              "; ","; ""; $date_range_begin; $date_range_end );

               

              That would give you a comma-separated list of the part numbers used in sites A, B, and C during the date range.

               

              Then, I'd use that variable in a second query...

               

              ExecuteSQL ( "

              SELECT

                        Part_Number,

                  Site,

                        Count (*),

                        MIN (Usage_Date),

                        MAX (Usage_Date)

              FROM

                        Part_Usage

              WHERE

                   ( Part_Number IN ( ? ) )

                        AND ( Usage_Date BETWEEN ? AND ? )

              GROUP BY

                        Part_Number,

                        Site

              ORDER BY

                        Part_Number,

                        Site

              "; "|"; "\n"; $part_numbers; $date_range_begin; $date_range_end );

               

              That second query is similar to the one that I posted earlier, but it has been adjusted to only include the part numbers from the first query.

               

              Again, I hope this helps.

               

              -- Tim