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::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
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?
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:
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.
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.
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.
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.