6 Replies Latest reply on Apr 10, 2012 1:54 PM by IanDouglas

    Display report in a matrix style - Drawing register

    IanDouglas

      Title

      Display report in a matrix style - Drawing register

      Post

      Hi,

      I hope this is the right place to post.

      I've got a database for storage details of drawings which we produce for individual projects and various related tables which allow details of revisions to be stored together with when sets of drawings are issued to clients and other members of the design team. The tables are basically as follows:

      Projects > Drawings > Drawing Revisions

      Projects> Issue Sheets > Issued drawings (Based on the Drawing Revisions)

      Drawings Issued to > Issue Sheets

      This all works fine and allows me to produce individual drawing issue sheets which lists the drawings I have issued to a set of clients on a particulat occasion. What I need hower is as follows:

      A report which is based on the project which will list all the drawings on the lft hand side as a vertical list. There are then a series of vertical columns which relate to each drawing issue with a date at the top and the drawing revision letter against each drawing that was included in that drawing issue.

      I essentally need a table where the rows could contain anything from 1 to 100+ items and the columns being the line items relating to the drawing issues with a date when any/ all or some of the drawings were issued.

      I hope I've explained this enough and someone might be able to help.

      I've attached a file which should help explain what I'm trying to achieve.

      Any queries, I'll be pleased to clarify the above.

      Many thanks,

      Ian

      25538576.png

        • 1. Re: Display report in a matrix style - Drawing register
          philmodjunk

          Does each column in the right hand section record what version (if any) was issued on that issue date?

          Projects----<Drawings----<Drawing Revisions

          Projects----<Issue Sheets---<Issued drawings

          Does "(Based on the Drawing Revisions)" mean that Issued Drawings and Drawing Revisions are occurrences of the same data source table?

          • 2. Re: Display report in a matrix style - Drawing register
            IanDouglas
             Yes and no. The drawing revisions are a separate table from drawings related through a unique drawing ID. The column to the right of the drawing reference would should the revision of any drawings issued on the date shown at the top of the column. Thanks
            • 3. Re: Display report in a matrix style - Drawing register
              philmodjunk

              I don't follow the 'no' here.

              I agree that issued drawings and drawings are separate related tables. But I asked if Issued Drawings and Drawing Revisions are occurrences of the same data source table or separate tables.

              I think we can get this part working with some one rww (possibly filtered) portals, but need to make sure that we can set it up to access the correct version info for each time the drawing is issued. That would appear to require that Issued Drawings be a separate table.

              • 4. Re: Display report in a matrix style - Drawing register
                IanDouglas

                Sorry, the 'no' was in relation to the question about drawings issued and drawing revisions being occurences of same table.  Issued drawings is a table which includes an drawing issued ID, drawing revision, issue sheet ID and drawing ID.

                Hope this helps.

                Thanks and sorry if I'm not being clear as I'm very much an amature at this.

                Ian

                • 5. Re: Display report in a matrix style - Drawing register
                  philmodjunk

                  Your relationships as I undestand them:

                  Projects----<Drawings----<Drawing Revisions

                  Projects----<Issue Sheets---<Issued drawings   (---< means one to many)

                  Will need some redesign to avoid having to enter data from Drawings and Drawing Revisions twice--once in those tables and once in the second set of tables.

                  I'm thinking in terms of these relationships:

                  Projects----<Issue Sheets----<Issued Drawings>----Drawings------<Drawing Revisions

                  Please note that you may need to create new table occurrences of Drawings and Drawing Revisions if you need the original relationship for use in other parts of your database.

                  Before I go much further with this, I took a fresh look at your example page and spotted something I don't understand: what does / mean in the section where you list the revisions issued each time? I thought it meant "not issued", but I see a case where you have / an empty cell and then another / before revision letters list so I am unclear as to what that means. (getting the correct revision data to appear on your form is the major challenge here as I see it.)

                  • 6. Re: Display report in a matrix style - Drawing register
                    IanDouglas

                    I've no problem creating additional occurnces for the drawings revisions or any other tables.  I have a "Drawing info for Revisions" which is a table of the drawings file with all the constant information related to the drawings revisions table. 

                     

                    In relation to the query, the blank cells relate to drawings which were not issued on that particular date.  The "/" used in the example is just a first issue drawing, although I intend to have the first issue as revision A with all subseqent drawings as B, C, D etc...

                     

                    It is importnt ot have the drawings listed to the left sorted by the drawing reference and that each issue give details of who it was issued to.  Hope this helps.

                     

                    Thanks again for the help so far.

                     

                    Ian