GoToRecord stopped working during Form_Load()

Yes on your current understanding of why the R in Relational Database Management System (RDBMS) is a critical component. It is also the hardest part to get right. Nobody is born with that knowledge. It is acquired through study of relational design principles.

Once such design is in place, and you "get" it, it will indeed return dividends over the life span of the app. A bad db design also is a "gift" that keeps on giving :-)

This might sound flippant but it's not intended that way; I'm asking because I generally don't understand. What is the purpose of having multiple tables?

A relational database has multiple tables because it is a model of the reality with which the database is concerned in terms of the real world entity types, the attributes of those entity types, and the relationships between the entity types.  By having multiple tables in which each column represents an attribute of that entity type and that entity type only, i.e. with no redundant information, the possibility of update anomalies is removed.  Say for instance you have a table of addresses with City and State columns, the table could contain the information that Dan Francisco is in California multiple times.  It would thus be possible for San Francisco to be mistakenly attributed to a different state in one or more rows in the table – an update anomaly.  By decomposing the table into Addresses, Cities and States tables in an hierarchical relationship this redundancy is eliminated, and with it the possibility of such update anomalies.  The integrity of the data is this maintained.

The problem I'm seeing is that my fields aren't necessarily unique. For example, grant entity 1 and grant entity 2 may name their grants the same thing. I think it would be rare, but would the workaround here be to make some trivial difference and put it into the second one?

Ex: Multiple agencies may have a Building Back Better grant b/c their funding all comes from the Building Back Better bill passed by congress. I'm not clear how to deal w/ the lack of exclusivity (but Monday I'm going to have time to go into your folder and start figuring stuff out so maybe it'll be clearer then).

In this case, maybe Grant = Building Back Better - DOE or something like that.

 

That's not the end of the story however.  A relationship type between two or more tables is really just a special kind of entity type, and as such has its own attributes which enforce constraints in the database.  My Relationships demo illustrates this.  A drug might only be available in one or more forms, e.g. tablets and/or capsules, and in specific doses only.  Such a relationship type is modelled by a table which represents a ternary (3-way) many-to-many relationship type between the entity types drugs, forms and doses, each of these being modelled by a referenced table in the relationship type.  This further maintains the integrity of the data.

😅 OMG hahahaha I need a nap, a blanket, and a support animal after reading that. I'm convinced that database people are just built different.

 

When designing a relational database therefore the first step is to decide on what entity types are present in the reality being modelled.  This is the logical model.  Then the relationship types between those entity types are identified, and implemented by having foreign keys in one table referencing the primary key of another (a unary one-to-many relationship type).  (One-to-one unary relationship types are also possible but relatively rare.  The ClassHierarchy demo in my OneDrive folder is an example). Or by a separate table modelling a many-to-many relationship type by resolving it into two or more one-to-many relationship types.  The set of tables representing entity types and the relationship types between them is the physical model.  Once the physical model is established it is tested by imagining every possible real world scenario you can think of.  This might result in further modifications to the model.

Decomposition of a table into a set of related tables is achieved by the process of normalization, for which there are formal rules. See my Normalization demo for further details.

What I'm understanding (which is probably wrong lol): Basically I'm pulling the same data but it's coming from different tables. The utility of that is that it minimizes input error and inconsistencies via superior organization. Also, as time goes by, the tables could get increasingly complex. I can get away w/ what I'm doing right now b/c I'm assuming max 100-150 records and a limited # of variables, but as the university grows a manually manageable 100-150 records could evolve into 1500 and my lack adequate organization from the start would make it into a logistical nightmare. By properly organizing it like you're saying now, in 5 years the table will still work as intended, whereas my hodge-podge "screw it - it works" version works b/c my dbase is very small and manageable, but will create an avoidable nightmare in the future as we grow.

At least, that's what I hope you said b/c that's what I got from it lol.

So this is what the form currently looks like. At your advice, I did away w/ the other form. This is 100% functional EXCEPT for some weird reason when the form first loads, the PI is blank. I can tell from other fields that it's record 1... I'm not really sure why it isn't populating. It's not something that adversely affects anything, I just don't see why it's doing it. I'm too worried about breaking something to programmatically populate it with PI from record 1 - everything is working perfectly. I haven't started the multi-table version b/c I don't really understand the PK/FK or really why we're using multiple tables to begin w/ so I don't know where to start (other than making the tables which is simple). I'll make the form prettier and put our logo up there and some other stuff, but it works and I couldn't be happier lol. My boss, too.

My Boss: "You're done?"

Me: "I think so. Time to beta test."

My Boss: "You said you'd be all week."

Me: "Two random strangers on the internet did the hard parts for me."

My Boss: <rolls her eyes and laughs> "Rightttt...."

My face:

This might sound flippant but it's not intended that way; I'm asking because I generally don't understand. What is the purpose of having multiple tables?

A relational database has multiple tables because it is a model of the reality with which the database is concerned in terms of the real world entity types, the attributes of those entity types, and the relationships between the entity types.  By having multiple tables in which each column represents an attribute of that entity type and that entity type only, i.e. with no redundant information, the possibility of update anomalies is removed.  Say for instance you have a table of addresses with City and State columns, the table could contain the information that Dan Francisco is in California multiple times.  It would thus be possible for San Francisco to be mistakenly attributed to a different state in one or more rows in the table – an update anomaly.  By decomposing the table into Addresses, Cities and States tables in an hierarchical relationship this redundancy is eliminated, and with it the possibility of such update anomalies.  The integrity of the data is this maintained.

That's not the end of the story however.  A relationship type between two or more tables is really just a special kind of entity type, and as such has its own attributes which enforce constraints in the database.  My Relationships demo illustrates this.  A drug might only be available in one or more forms, e.g. tablets and/or capsules, and in specific doses only.  Such a relationship type is modelled by a table which represents a ternary (3-way) many-to-many relationship type between the entity types drugs, forms and doses, each of these being modelled by a referenced table in the relationship type.  This further maintains the integrity of the data.

When designing a relational database therefore the first step is to decide on what entity types are present in the reality being modelled.  This is the logical model.  Then the relationship types between those entity types are identified, and implemented by having foreign keys in one table referencing the primary key of another (a unary one-to-many relationship type).  (One-to-one unary relationship types are also possible but relatively rare.  The ClassHierarchy demo in my OneDrive folder is an example). Or by a separate table modelling a many-to-many relationship type by resolving it into two or more one-to-many relationship types.  The set of tables representing entity types and the relationship types between them is the physical model.  Once the physical model is established it is tested by imagining every possible real world scenario you can think of.  This might result in further modifications to the model.

Decomposition of a table into a set of related tables is achieved by the process of normalization, for which there are formal rules. See my Normalization demo for further details.