@Pat Hartman - Sorry to be confusing you. My pictures of relationship diagrams are my attempts to model the table structure per your suggestions.
1. You have a generic engine with some attributes that apply to ALL instances of this type of engine.
Correct.
2. You have a specific engine with a serial number and more detail attributes that apply to only a single engine..
Correct.
3. What I said about the serial number is that you need to create a row in tblEngineDetails with a dummy serial number if you want to link the engine to an asset BEFORE you actually know the serial number. This sounds a little strange to me but not so strange that I think it is wrong. You know what kind of engine you want long before you know what specific engine arrived on the loading dock so this request isn't actually farfetched.
Understand the suggestion (dummy s/n's). As you say, strange, but effective and also similar to the suggestion of
@The_Doc_Man.
If you didn't need history, this would be the end of it. You would add the AssetID to the EngineDetails table to assign this specific engine to a specific asset. But for some reason you made a junction table. OK. You don't need it to implement this relationship as originally defined. BUT if you sometimes remove an engine from one asset and then attach it to a different asset or even put it back on the original asset, having the junction table works to provide history. So instead of putting the AssetID in the EngineDetails, you can put this FK in the junction table.
"For some reason I made a junction table" - this junction table was made based on my understanding of this comment from you in post #7. (bold is mine)
tblEquipmentEngineSerial is the junction table btween a specific engine from tblEngineSerial and tblEquipment and includes the date on which it was installed. If you want to keep history of where an engine was installed at a particular time, you could do that by adding a RemovedDT field to this table. You might also want an OutOfServiceDT if you want to retire engines and not allow them to be moved to a different piece of Equipment.
If there is a different, non junction table way to model this data, I'm all ears.
4. This is where we now have to be careful about our foreign keys and our unique indexes because you CANNOT have one engine assigned to multiple assets at the same time. You cannot do this entirely with Referential Integrity, you will need code in your forms to force an engine to have a value in the DateRemoved field before you can add a second instance of that specific engine to a different asset.
I understand that I will need to enforce the proper data structure via VBA code in the FE to check that a specific engine is available to be assigned to a unit.
That leads us to this relationship diagram from post #19.
Here we have a unique autonumber PK for tblUnitEngine so that it works better with combos. We have two FKs to connect the junction table. That aligns with this comment of yours from post #23.
I would NOT use a natural key instead of an autonumber as the PK for tblUnitEngine. Leave the autonumber as the PK. But make a unique index on the THREE fields I mentioned - the two PK being connected AND the date the engine was installed. This satisfies your history as well as the multi-engine case.
....
The reason I use autonumbers plus a unique business rules index for junction tables is because of the way combo's work. They ASSUME single-field unique index. Without that, you may have trouble using them.
All of this makes sense and I believe I understand everything.
However, that still leaves this final comment from post #31 for me to understand:
PS, rather than having multiple attachment tables, add a type field to the Engine table and rename the table to Attachments or something more generic.
I presume this comment was made to highlight an efficient way to handle attaching other equipment that would be structured similarly. While I think I understand the concept (it's similar to how you structure your lookup list table), this particular implementation I don't fully understand because I don't quite see how to make all of the relationships work. So, I created the final relationship diagram in post #35 as an attempt to model what I thought you were suggesting and also to highlight that clearly I'm missing something important in order to understand your "type field" suggestion. I created two additional fake detail tables for compressors and bottles to use to model the relationships. They are meant to be equivalent to tblEngineDetails.
Setting aside your "type field" suggestion for a moment, adding the additional detail tables would yield the following structure:
(EDIT - Replaced picture with one with the correct PK-FK relationship for tblUnitBottle)
Now, we have 3 "attachment" tables as you called them. If I'm understanding correctly, your "type field" suggestion is meant to combine these three "attachment" tables into one table and the "type field" is what makes that possible. So, now we've come full circle to my relationship diagram from post #35.
This is where I'm struggling to see how to relate these tables to accomplish the same things as having 3 "attachment" tables.
I hope this extra long post has made things clearer.
Edited to replace screenshots that disappeared somehow.