Table Structure to use two different lists in a drop down. (1 Viewer)

JMongi

Active member
Local time
Today, 14:09
Joined
Jan 6, 2021
Messages
802
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
 

Attachments

  • 1631219652389.png
    1631219652389.png
    130 KB · Views: 119
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 14:09
Joined
Feb 19, 2002
Messages
42,971
Please go back to #19 and work on combining the components into a single table. Making separate tables is not the way to solve this problem. You wouldn't make a separate customer table for each customer you do business with and you wouldn't make a separate table for each type of component.

Change the name of tblEngineModels to tblComponents. Add a ComponentType field Then for now, add the additional compressor fields and the "parts" fields. We need to get through this sameness concept first. You can manipulate the visible properties of controls based on the Type field so that you don't confuse the user. When the Type is "Engine", you can show only the common fields and the "Engine" fields. When the Type is "Compressor", you can show only the common fields and the "Compressor" fields. This method works if you have only a few types and a few fields. If you have more than you are telling us about or you think there might be more in the future, it would be best to use the ultra normalized scheme with a subform so you don't have to have a lot of code behind the form to show the relevant controls.

Or, if you really want the three tables, you need a Component table with all the common attributes and this is the table that links to everything else. You can then hang the three specific tables off the component table. They are not involved in any other join. They are just additional attributes of a component. I moved Description to the components table and I removed Type from the extra tables because I didn't know what it was. "Type" is a really generic term. Could this mean Make/Model? You should make common fields for manufacturer's descriptions of the product. PartNumber could be a Model. These things can be standardized.

I created what I think is the schema. I only added Engine and Components because I moved fields around and I don't think the Part table needs a separate table. There is also a sepreate rendation of what I think is a better option below. Instead of the two(or more) tables, one for each type of component, I created the ultra normalized version which is tblCompDetails. There is a lookup table named tblFieldNamesByType. This table would be used to help with data entry. On the components form, when you add a new component, you run a query in the form's afterUpdate event to copy all the fields for the field type and append rows to tblCompDetails. Here's what that table looks like:
Code:
FieldNameID    CompType    FieldName
1    Engine    Cylindars
2    Engine    Displacement
3    Engine        RPM
4    Engine        Torque
5    Engine        HP
6    Compressor    Stages
7    Compressor    MaxRPM
8    Compressor    RodLoad
9    Compressor    Gearing
10    Other        PartNumber
11    Other        Description
12    Other        Manufacturer
13    Other        Type

When the type is Engine, you add the 5 engine fields as rows waiting for data entry. When the type is Compressor, the query adds the four Compressor fields as rows waiting for data entry
AssetSchema.JPG

And finally, I also attached the db so you can play with it.
 

Attachments

  • AssetDB1.accdb
    584 KB · Views: 128

JMongi

Active member
Local time
Today, 14:09
Joined
Jan 6, 2021
Messages
802
This will take awhile to break down. Thanks!
 

JMongi

Active member
Local time
Today, 14:09
Joined
Jan 6, 2021
Messages
802
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 14:09
Joined
Feb 19, 2002
Messages
42,971
The data entry form is probably best in Data Sheet view. As I said earlier, the user won't need to append all the rows himself, creating the parent record will generate that. This is probably the only case where I ever generate empty records ahead of time. I have one application where I use this technique and that application is used to collect data for various insurance policy applications and use it to generate the necessary word documents. Because of the schema, there isn't any way that you can really enforce RI because all the fields are in separte records. So what I did was to add a flag to the definition table so that I knew which fields were required for completeness and which were optional. Then at the time the user asked to generate certain documents, the app ran a query that determined if all required fields were populated. I didn't mention this earlier because you need some kind of trigger to invoke the validation and I'm not sure you have one. I also thought that for this application, validation of the field/value data was probably not required. Her's an example. The subform is the field/value list
DGT_DataEntry.JPG
 

JMongi

Active member
Local time
Today, 14:09
Joined
Jan 6, 2021
Messages
802
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 14:09
Joined
Feb 19, 2002
Messages
42,971
I have a flat spot in the middle of my forehead from all the aha moments I've had or maybe it's from listening to politicians lying to me. I can't remember anymore but instead of being flat, it is getting dents the lies are so fast and furious these days:)

The picture you are looking at is from a very interesting app. It was for a startup insurance company and every time they wanted to issue a new type of policy, they needed to create a bunch of new word documents that would need to be printed to support the sale. It had been up and running for a year when I left and it was up to over 5,000 documents for 10 different policy types defined so far. And everything was managed by the user. They could define new "fields". They could assign them to new policies, they could define new documents for those policies and they could map the entity/attribute fields to bookmarks so the documents could be populated automatically. Coming up with the Attribute/Value solution was a real aha moment for me. I later learned that I wasn't the first person to do this. It is not a particularly efficient way to store data but it is enormously useful when you keep getting "new" data requirements almost weekly since the alternative would be constantly changing tables. I don't think you'll have that problem so I wouldn't worry about the efficiency.
 

JMongi

Active member
Local time
Today, 14:09
Joined
Jan 6, 2021
Messages
802
@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?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 14:09
Joined
Feb 19, 2002
Messages
42,971
Yes. That's how it works. I think the example might even have sample queries so you can see how I name them.
 

JMongi

Active member
Local time
Today, 14:09
Joined
Jan 6, 2021
Messages
802
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!
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 14:09
Joined
Feb 19, 2002
Messages
42,971
Wow, I don't think anyone has ever inspected the app that closely before so good for you. I try very hard to do things "correctly" when i make these samples. They are intended to be teaching tools and use little techniques to help you evolve although this one is more involved since I actually use it in production apps. THANK YOU for noticing:):)
 
Last edited:

Users who are viewing this thread

Top Bottom