Keeping like row amounts in related tables

[chestyxbond]

Okay, maybe this is something really simple and I'm just having a temporary lapse, but how do you keep multiple related tables having the same number of rows?

When I create a form based on a multi-table query, I can't enter data into anything but the primary table because the related tables (there are four tables, all with one to one relationships to a different table) don't have rows to match the rows on the main table.

Basically, I want access to add a row to all of the related tables every time I add a row to the main one. How do I do this?

Daniel

 

[JerryDennison]

It is poor design to populate tables with "empty" records. It is truly rare to need tables that have a one-to-one relationship with another table, much less 4 such tables. It sounds like your database structure needs to be re-evaluated. What are you storing in these related tables that you can't simply include the fields in the main table?

 

[chestyxbond]

The problem is that I need a great deal of space for comments, and the maximum size for a record is very small and restrictive. So, the only way I could think of to get around the problem was to have space for comments in other tables that are related one to one. However, some records will have lots of comments, some few, and some may even have none at all, resulting in many unpopulated records in the tables that consist entirely of comments.

If you have a concept that would accomplish my goal of having ample comment-room I'd love to hear it.

Thanks,
Daniel

 

[JerryDennison]

Yes, eliminate all but one of the comment tables and change the relationship from one-to-one to one-to-many. In this way you can have none or thousands of comments for any one record. To make use of this comment table(which needs only two fields, one to link to the pk of the main table and one memo field for your comments. You may also want to include a date and/or time field to log when the comment was entered, and possibly a link to your employee or some other table to capture who made the comment. But at a minimum all you need are the link and memo fields). Then base a subform of your main form on this table. This way you need never have empty fields or records.