many to many - forms

[Guest]

Hey! I am building my first 'major' database, which i did to improve an existing database that my employer was already using but wasn't designed logically. Over the last month, i have finally finished getting all the relationships correct and the design is as normalised as can be! Now i have arrived at the forms section and im stuck. I have multiple many to many relationships within the database, and have of course broken them up into one to many relationships with the use of junction tables. Im just wondering how to set the forms up to incorporate these various relationships. Do i design a query first and then a form based on this, or do i use a form-subform system that is based upon the tables? Even just access to some information on the theory of setting up forms with many to many relationships would be great, i am living in a country that doesn't speak english and i cant get my hands on an access book in a language i can read! Anyone know somewhere on the net where i can find the info? Thanking people in advance....

 

[Roger Carlson]

On my website (see sig below) is a small sample database called
"ImplementingM2MRelationship.mdb" which illustrates a couple of ways to do
this, depending on what you are trying to accomplish.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

Hey! I am building my first 'major' database, which i did to improve an

existing database that my employer was already using but wasn't designed
logically. Over the last month, i have finally finished getting all the
relationships correct and the design is as normalised as can be! Now i have
arrived at the forms section and im stuck. I have multiple many to many
relationships within the database, and have of course broken them up into
one to many relationships with the use of junction tables. Im just
wondering how to set the forms up to incorporate these various
relationships. Do i design a query first and then a form based on this, or
do i use a form-subform system that is based upon the tables? Even just
access to some information on the theory of setting up forms with many to
many relationships would be great, i am living in a country that doesn't
speak english and i cant get my hands on an access book in a language i can
read! Anyone know somewhere on the net where i can find the info? Thanking
people in advance....

 

[Andy]

Danny;

Here is the link for Access Support in English.
http://support.microsoft.com/default.aspx?scid=fh;EN-US;acc

In respect to Your question re: What to base a form on?

Long, Long, LONG time ago when Microsoft offered free tech support I was
fortunate enough to be passed up to a 2nd level tech. He told me to always
base a form on a table and base a report on a query when possible.

Andy

Hey! I am building my first 'major' database, which i did to improve an

existing database that my employer was already using but wasn't designed
logically. Over the last month, i have finally finished getting all the
relationships correct and the design is as normalised as can be! Now i have
arrived at the forms section and im stuck. I have multiple many to many
relationships within the database, and have of course broken them up into
one to many relationships with the use of junction tables. Im just
wondering how to set the forms up to incorporate these various
relationships. Do i design a query first and then a form based on this, or
do i use a form-subform system that is based upon the tables? Even just
access to some information on the theory of setting up forms with many to
many relationships would be great, i am living in a country that doesn't
speak english and i cant get my hands on an access book in a language i can
read! Anyone know somewhere on the net where i can find the info? Thanking
people in advance....

 

[Roger Carlson]

With respect, this is nonsense.

You generally want the information in your form to be in some sort of order.
In order to do this, you must use a query. Another reason involves
performance. If you had a table of a million records, your form will take
forever to load. In a query, you can restrict the number of records that
load initially. Further, you may want to base your form on a JOIN of
multiple tables, which, of course, demands a query.

Forms as well as reports should most often be based on queries.