Table Structure to use two different lists in a drop down.

[JMongi]

Ok. Thanks. I wasn't sure if that previous suggestion was still the way to go about this (assigning from the junction table w/ dummy unique s/n).

A slight zig if I may since this highlights more inexperience on my part in crafting queries. In this structure we've been going through there is the possibility of multiple engines assigned to the same unit. This concept applies to a few other pieces of equipment assigned to a unit. In the table structure, this information would be contained in multiple records (in this case the tblUnitEngine). How would I go about "flattening" this data for presentation? Meaning, creating a query where all of the assigned equipment shows up as a single pseudo "record" of the unit number similar to the output posted in #10? It may not be possible. But, I'd definitely like to know before I spend a bunch of time to arrive at the conclusion that it won't work.

 

[JMongi]

I think you just officially overloaded my brain! Sub-reports! Why don't I know anything about sub-reports? Was I not paying attention? I presume their like subforms. *Sigh* It's times like these that I have to fight off the thoughts, "I know nothing! Why am I even trying to do this?"

Off to the land of google for me...

 

[JMongi]

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.

Your trying to hurt my head with this one too!
I think I understand what you are going for. I just don't understand how adding a type field helps me accomplish this since I still need to have FKs for each detail table don't I?

 

[JMongi]

 

[JMongi]

@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.

 

[JMongi]

I didn't say you needed a junction table. I said the junction table ALREADY existed.

And I tried to spell out in great detail why that is not true. I'm saying that it did not already exist. It was created within the context of this thread. Perhaps I was a bit unclear in quoting your post #7. At the time you commented in post #7, I hadn't posted any relationship diagrams. The closest I came to a table structure was in post #6. No junction table in sight. I was going back and forth with @The_Doc_Man. Then you graciously entered to help as well with post #7. So the idea for the junction table specifically came in response to your post #7, me modelling it in Access and then posting a screen shot of the resultant relationship diagram to verify that I was understanding your post correctly. That's all I can say. I appreciate that you are busy and volunteer your time to assist us inexperienced ones. I'm trying to be very correct here so that we don't waste our time figuring out a junction table that may potentially be unnecessary.

I have no idea what "test" something is. You originally called the main "thing" an asset. Now you're using different terminology and making multiple types of them.

Wasn't trying to be confusing. These were just test tables to help me visualize what you were suggesting. So, I called them test.

I suggested that you think about a single table for all components rather than one for engines and more for each type of components. Think about how Sikorsky would handle this. Do you think they have a table for engines. A table for rotors. A table for radios, etc. No. They have one table for parts - PERIOD. And so should you. Parts have many common attributes and even more unique ones. The common ones all go in the table previously named engines. Other details can go into comments since this application will not likely be doing anything with them. They are just part of the description. You would need to add a new column to the "engine" table to specify what type of component the record is for. So far I've seene Engine, Compressor, Bottle. Make a lookup table to ensure only valid values are stored.

Thanks for the clarification! I now understand what you were originally referring to in your postscript. I'll think more on this. It definitely makes sense.

 

[JMongi]

Hmm...clearly I misunderstood your original intentions. But, thanks for working to clarify things!

It does complicate things but I do need the history of when components were installed and removed.

Update:

REMOVED FOR THINKING

 

[JMongi]

This will take awhile to break down. Thanks!

 

[JMongi]

Ok. The schema itself is not too difficult. I understand the concept of tblCompDetails where each field/value pair is connected to the appropriate component. All in all I believe I understand the broad concepts as well as most of the details. I'll try to put my struggle into database terminology. It's an intersection of my lack of experience and how I function with complex systems (i.e. I need to understand it ALL up front).

If I am understanding correctly, your schema is considered the internal model or how the conceptual model (ERD) maps to the specific database system, in this case MS Access. That involves normalization, dependencies, etc. It also can result in rather abstract structures like the generic component detail table idea that contains records of all component details for all components but stores the data efficiently.

However, I am apparently very light in the external model part or more specifically, what constructs are needed to bridge the internal model to the external model presented to the end user. For example, your tblFieldNamesbyType table seems to be a construct to "aid in data entry" as you say. I can make a pretty form or futz around in VBA to get some cool features but I lack any real experience bridging the raw data into its consumable form via queries or secondary table structures.

I'm going to try and create the data entry form using your table structure and hopefully that will illuminate some gaps in my understanding. You outlined the process but I only understand the actual steps (i.e. writing to tables and how that would happen) without seeing the whole system work in my head. I especially draw blanks on how to extract the specific data OUT of the table structure and present it in the way that the end user would like to use it when the data is ultra normalized as you say. I will work on that too. Hopefully that will be the final (relatively speaking) aha moment.

 

[JMongi]

Thanks for an example!

I also had a mini aha moment about the use of a unique index (aside from a primary key). I know you spelled out indices in previous posts but I hadn't connected the dots as to how you could use those indices (by making them unique) to prevent duplication when connecting multiple tables together using foreign keys. Hopefully its not too premature but I think another db puzzle piece of understanding just clicked into place.

 

[JMongi]

@Pat Hartman - Imported your lookup table manager and been cleaning up code, etc. to mesh with my app. I wanted to make sure I was understanding its use correctly.

1. The Codeid field from tblCodes is used as the FK in all the appropriate lookup field spots in other tables.
2. On form combo boxes using the master lookup their source will be a query based on tblCodes using a WHERE = the appropriate TableID.

Do I have this correct?

 

[JMongi]

Yes. That's how it works. I think the example might even have sample queries so you can see how I name them.

Thanks! I didn't see any, but, I'll check again. I learned a few side things from the app too. I looked over your form close code, some of your error specific trapping, learned about the difference between BeforeInsert and BeforeUpdate events and probably the most useful is that it never occurred to me that you could use Me.Fieldname on a form with an underlying query and write data to that without it being on the form at all. No need to have a date or username on the form when all you want to do is log record creation/update info. Very cool!