4 Replies Latest reply on Dec 2, 2011 3:36 AM by kevork

    How to create a database with multiple database subsets (record lists) within the same database

    DanielleThompson

      Title

      How to create a database with multiple database subsets (record lists) within the same database

      Post

      I am trying to create a database which has all the parts of 5 different turbine types. Some of the parts will however be compatible with more than one turbine type.

      I need urgent help on how to set up the database with the 5 different record subsets for each of the 5 turbine types. Each turbine type will have approximately 300 parts with corresponding information on each part. I want to be able to select a turbine type and view all the corresponding parts for that turbine type.

      I produced tabs for each turbine type and added all the parts for the first turbine type (n=300). Now when I select the second tab named with the second turbine type I had hoped to get add a list of separate part records for that turbine. However when I select the second tab the interface within the tab is just blank. When I click to add a new record, the record just adds on to the list of records for the first turbine type (i.e. n=301) instead of n=1 for the second turbine type.

      Thanks for your help.

        • 1. Re: How to create a database with multiple database subsets (record lists) within the same database
          philmodjunk

          Before we talk about layout design, we need to set up a table and relationship level design that will work for you.

          As I understand your post, a given part can be linked to more than one turbine type and a given trubine type lists many parts. This is called a many to many relationship and is a classic relational databse design challenge. The standard solution is to add a third table, PartsList that links TurbineType records to TurbinPartsRecords:

          TurbineTypes----<PartsList>-------TurbineParts

          TurbineTypes::TurbineID = PartsList::TurbineID
          TurbineParts::PartID = PartsList::PartID

          With this set up, a portal to PartsList placed on a TurbineTypes layout can be set up to list all parts for that turbine type. A portal to parts list on the TurbineParts layout can, conversely, list all the TurbineTypes that list that part.

          Here's a demo file. It matches "contacts" to "events" but all you need to do is rename it's tables to match that shown above to have a file that demonstrates both simple and also more  sophisticated layout designs for working with such a set up:http://www.4shared.com/file/dZ0bjclw/ManyToManywDemoWExtras.html

          • 2. Re: How to create a database with multiple database subsets (record lists) within the same database
            DanielleThompson

            I am still confused as my set up isnt quite as you think. My intention is to set up a database with 20+turbine types of around 300 parts per turbine. I began setting up my database with the first turbine model. To set up the initial database, I had a list of all 300 parts and their information for the first turbine. I set a an appropriate layout and began adding each of the 300 records. However now I want to add the second turbine type and all of its 300 parts. And so on for the other turbine types.

            I want to be able to initial select the turbine type I am interested in and look at the 300 records for that turbine type. Then I might select a different turbine type and look at all the 300 parts that belong to this turbine type. However if I select add new record the new record just adds on to the first turbine types record list. I want to be able to see separate subsets for each turbine type,

            Please advise me urgently if you know how to help me with this?

            Many thanks

            • 3. Re: How to create a database with multiple database subsets (record lists) within the same database
              philmodjunk

              I know your set up isn't what I described. I am recomending that you change your set up.

              When you list parts for your turbine types, some of the same parts are common to more than one turbine type, correct?

              If so, you have a many to many relationship. Please look at the demo as it demonstrates several methods that you could adapt so that you can select from existing parts for parts that are common to a turbine type that you have already documented and yet can add new part records for parts that are not part of the parts lists for the existing turbine type records.

              Three tables, each with a different purpose:

              TurbineTypes

              One record in this table identifies and records data about a single turbine type. It should include an auto-entered serial number field so that you can link specific parts to this turbine type record.

              PartsList

              One record in this table links a TurbineType Record to a Part record. This is the "join" table and might only have two fields for storing TurbineType and Part ID numbers. ON the other hand, it may also have other fields that specify details for this part specific to its use/function in this specific turbine type. You might have a Qty field in this table, for example, that specifies how many of this part are used for this turbine type. TurbineType ABC might use 2 Parts numbered 1234 and TurbineType XYZ might use only one of the same exact part.

              Parts

              One Record in this table identifies and records data about a single part that is used in one or more TurbineTypes. The ID number, description and any other details specific to this one part would be stored here.

              In the demo file, you can treat "events" as "TurbineTypes", "contacts" as "Parts" and "Event_Contact" as "PartsList".

              I want to be able to see separate subsets for each turbine type,

              A portal to PartsList on a TurbineType layout will list such a subset. You can also set up a layout based on PartsList and perform a find for a specific TurbineTypeID to pull up such a sub set. You can even set up a summary report on a PartsList based layout that lists multiple parts lists for multiple turbine types all grouped by turbine types to give you your sub sets of listed parts.

              • 4. Re: How to create a database with multiple database subsets (record lists) within the same database
                kevork

                Thanks, PhilModJunk, I looked at your demo file and it has got me past first base. You have a portal from Contacts into Events but not the other way around. So I thought I would create the portal from Events into Contacts. It would not work - just like the research database I have been trying to set up. But I looked at your relationship diagram and looked at the value list and eventually worked out what I had done wrong and you had done perfectly. Thanks.