Sounds like you are inputting data into table 1 that should never be entered into that table, but should be entered into table 2 from the beginning.
Components::__pkComponentID = Bulbs::_fkComponentID
See this thread for an explanation of my notation: Common Forum Relationship and Field Notations Explained
With this setup, you can place a portal to Bulbs on your Components layout and use it to list any number of bulbs for a given component and you can then use a second layout, based on Bulbs to produce the report that you want.
Ps. You might need yet another table between bulbs and components in this relationships if there is a lot of additional info that you need to record about each bulb type used.
Thank you for this feedback. I agree that initially, it appears that it would have been better to have started with all of the info in Table 2, but the reality is that the specifics of each "Bulb" is being dictated by the "Component" that it is connected. All Bulb numbering, types, sizes, ... are generated as a result of the component. I know it's very confusing to describe.
The quick way to make this work is to create 2 new layouts (1 for each component socket) and then just have them lookup the info from Table 1. I was just hoping to have a single, consolidated table with all of the Bulb info.
What I am describing fits what you are saying. The specifics of each "bulb" are controlled by which component record is current. Record 1 can have 2 bulbs of type "A" and "B". Record 2 can be for a component needing 3 bulbs, 2 of type "B" and one of Type "C". A join table can be used if there is data specific to a particular matching of a bulb to a component And all of this data can be managed from a single layout.
Thank you very much for your help and I'll give it a try and let you know how it goes.
BTW, this sounds like a BOM (Bill of Materials) such as is typical for documenting the assembly of products in a manufacturing plant.
Basically, yes. I am trying to extract/combine the data from the Master Table to create an equipment list for purhasing. This all started as the Master Table was originally produced in Excel and then given to me to try and manipulate. I would have preferred to start with FM. Thanks.
BOM, Purchase Orders and Invoices all have a very similar structure where you have a parent record that links to a detail record which in turn often links to a third table such as the table of products available in an invoicing or Purchase Order system. You may find reviewing the design of some of the starter solutions helpful in gleaning ideas that can apply to your database system.
I'd guess that this basic structure is the direction in which your design is heading:
Equipment::__pkEquipmentID = Component_Equipment::_fkEquipmentID
Components::__pkComponentID = Component_Equipment::_fkComponentID
See this link if my notation is unfamiliar: Common Forum Relationship and Field Notations Explained
A component record in the Components table documents one specific type of component (such as a bulb) and is where you can record information such as the bulb's wattage, link in specifications documents, unit cost, etc.
A record in component_equipment identifies a specific component linked to a specific item of equipment. A quantity field in this table can record the number of components of this type that are needed.
component_equipment, BTW, is simply a BOM or LineItems table depending on whether you are purchasing this item or assembling it.