Form Design Strategy on schema with many M-M relationships

[Ayat]

Hello,

I have a couple of elementar level questions on Form creation process and standards invloving a schema with several M-M relationships. I have a highly normalized design (thereby producing several junction tables). I have attached a picture of my schema to give an ideal of the schema I am talking about.

1. What is typcially the process of creating Forms on such a schema for the purpose of data-entry. Meaning, should there be several Forms (on the order of # of tables) or should there be a few that enables simulataneous data entry to many tables through one Form. What is typically the practise?

2. If there are 3 tables (A, B, C) so that A and C have a M-M relationship and B is a junction table that makes A &B and A & C have a 1 - M relationship, then how does one enter data for A, B, and C so that all the three get populated?

3. Can someone provide a copy or link to a sample database that has a similar normalized schema and Forms built into it that takes care of the data entry to the various tables?

Thank you very much for some light on this path.

 

[Uncle Gizmo]

>>>> typically the practise? <<<

How is the data entry process performed now? How would you want to enter the data yourself? What I am trying to say is, a data entry form is where your user will interface with the program, therefore the form should be designed to make the users job as easy as possible. You shouldn't be designing the form or forms with any relevance to the data structure.

 

[Ayat]

Good point.

Currently, the job is done is an ~50 column Excel spreadsheet. The user takes each row (which is a product) and puts in information painfully for the 50 fields for that row and then goes on to the next one. When information for a product and any of its field/s is updated, the user opens up the Excel sheet and finds the particular row and then scrolls to the specific column and updates the info. Overall, it is a painful and error prone process. Most of the schema I have designed is attached to this posting.

So my question is how to populate the data given this schema. My general question is and as posted in the post is how one created forms for tables that have a M-M relationship and thereby have a third junction table as well. How do the tables get populated?

Thanks for the reply.

 

[Pat Hartman]

Each of the m-m relationships needs its own subform. The subform needs to be bound to a query that joins the junction table to the "lookup table". Use a combo to select the item you want to "relate" and Access will automatically populate the other columns so you can see the related data. Set the locked property to yes for all controls on the subform, except for the combo.