3 Replies Latest reply on Apr 17, 2015 6:34 AM by philmodjunk

    Design Question

    tbcomputerguy

      Title

      Design Question

      Post

      Hi there gang.  I have a design question.  We currently use excel to track what we call our gov't tracking database.  Accounting isthe game.  the spreadsheet uses a grid to show all the companies that we do books for and which month each of the required services are due.  Services: basically 3; hst, recgen, wsib.  Companies: approx 100.  Months: pretty obvious.  I can't seem to figure out from which context I should design this.  From the company context, they could see all 12 months  and the required service due and if done.  Or from the Months context showing all the companies and required services due. This is a sample of what we have.  Just some thoughts would be helpful.  Not sure why I am stumped on this...I thought it would be straight forward.

      DAve

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
                     

       

                     
                     

       

                     
                     

      JAN

                     
                     

      JAN

                     
                     

      FEB

                     
                     

      FEB

                     
                     

      COMPANY

                     
                     

      Service

                     
                     

      DUEDATE

                     
                     

      DONE

                     
                     

      DUEDATE

                     
                     

      DONE

                     
                     

      1013957

                     
                     

      HST

                     
                     

      31-Jan

                     
                     

      YES

                     
                     

      N/A

                     
                     

      N/A

                     
                     

      1013957

                     
                     

      RECGEN

                     
                     

      15-Jan

                     
                     

      YES

                     
                     

      15-Feb

                     
                     

      YES

                     
                     

      1013957

                     
                     

      WSIB

                     
                     

      31-Jan

                     
                     

      NO

                     
                     

      N/A

                     
                     

      N/A

                     
                     

      COMPANY2

                     
                     

      HST

                     
                     

      31-Jan

                     
                     

      YES

                     
                     

      N/A

                     
                     

      N/A

                     
                     

      COMPANY2

                     
                     

      RECGEN

                     
                     

      15-Jan

                     
                     

      YES

                     
                     

      15-Feb

                     
                     

      NO

                     
                     

      COMPANY2

                     
                     

      WSIB

                     
                     

      31-Jan

                     
                     

      YES

                     
                     

      N/A

                     
                     

      N/A

                     

        • 1. Re: Design Question
          philmodjunk

          Do you have a table of companies, a table of services, a table linking a company to the services provided for it and perhaps a table of due dates also linked to that "link" or "join" table?

          • 2. Re: Design Question
            tbcomputerguy

            I do indeed have tables for both companies and services.  I do not have a table for due dates.  I thought about one for Months only because each month has many companies that have services.  Then have a join table with Months as the primary and a portal with all the companies that are needed.  But that would be silly having a portal with all those companies.  Probably better to have a company primary and Month portal, then only 12 lines are needed in the portal.  Then link the portal (join table) to now a due date/done table.  Sorry but I had to edit this.  I know that my boss (father in law) would want to see a grid like view with all the months across the top and companies down the left side for a quick view of the year.

            Am I thinking correctly.

            • 3. Re: Design Question
              philmodjunk

              A "cross tab" report is definitely possible, I'm just asking for your current data model. A table of months would not necessarily result in a portal of  only 12 rows as each year will add to that list of 12 and there are other ways, such as your columns for due dates and status.

              Here's what I am thinking in terms of tables and relationships:

              Services----<Service_Company>------Companies-----<Due Dates>---------Services|Duedates

              Services and Services|DueDates would be two occurrences of the same table.

              To created your report, you would set up a list view layout based on Service_Company sorted by company so that all services for the same company will then appear in a group. 12, one row portals to Due Dates can then be set up with filters that select for the current record's service and for a specific month and year (or they might be sorted to show the most recent due date and filter only for service.)