1 Reply Latest reply on Jan 11, 2011 10:19 AM by philmodjunk

    Connect 2 Similar Tables To A 3Rd For Portal Viewing



      Connect 2 Similar Tables To A 3Rd For Portal Viewing


      I would like to be able to connect two similar tables together for the purpose of viewing the data in a common portal.

      Background: I have a main table that houses customer data. (i.e. Name, Address, Phone Number, Primary Key)

      There is a second table that houses service call data. (1.e. Date of  call, type of call, work performed, tech, Foreign Key) This table  connects to the main table via Primary key from Main table.

      There is a third table that houses Weekly maintenance data. (i.e. Date  of maintenance, Type of maintenance, tech, Foreign Key) This table also  ties back to the main table via the same primary key.

      I would like to link the two tables together via the same  primary/foreign key from the Main table. I would then like to view the  data through a portal on the Main tables layout that would list the  services in chronological order.

      I believe this could be accomplished through a possible fourth table.  I'm not sure if I need to use a global value to link the items  temporally. I don't want to populate the possible fourth table  permanently for storage space reasons.


        • 1. Re: Connect 2 Similar Tables To A 3Rd For Portal Viewing

          I suggest you merge your records for service calls and maintenance into a single table and add a field to two to distinguish between scheduled maintenance and service calls. From your example, I can see that you have fields in the two tables with different names that collect the same type of data:

          Date of call = Date of maintenance, work performed = Type of maintenance,  Foreign Key= Foreign Key

          It's possible to create a table like you describe, but then you have to create a record in this table every time you log a new service call or perform maitenance--so this becomes a much more complex approach and to no benefit that I can see.