Responses in-line.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
Billiam said:
Which of the options you suggested is the best as far as performance
is concerned?
The first alternative would be my suggestion. (More a matter of what's the
best design for what you need than performance.)
...wondering if you could clear a couple of things up for me...
1. I thought with a properly normalized design and relationships set up
that you would use a query to "reassemble" the tables to build your form
from...is this correct? The reason I ask, is I thought that I could simply
seperate the existing attachment field (called BMP) from the original
table
that the form was based on, and reattach it through a query so as to not
interfere with the requirements of not having a multi valued Field in the
Audit procedure you created...I believe when i tried this I received an
error
such as "You cannot base a query on a table that the form is already based
on...or something like that?
You can create a query from multiple tables, and then base a form on that,
but the results may not be desirable. For example, if one person has
multiple pictures associated with them, they will appear multiple times in
the query, and therefore multiple times in the form. It would be better to
use a form, with a subform for the pictures. Then the person appears once
only in the main form, and the multiple pictures show in the subform.
You may also find that a multi-table query is read-only, so you can't edit
anything in the form. Here's a check list if you hit that:
Why is my query read-only?
at:
http://allenbrowne.com/ser-61.html
But again, basing the main form on a table averts that issue also.
My goal is to not have to change the original Instructor Input Form which
the enduser, my boss, is used to...simply there is a bound image frame in
the
original Instructor Input form...in other words, I do not want to add a
subform because I think it will not look right since I do not know if it
can
be made to simply look like a picture frame...which is how it looked
before
adding the audit procedure.
You can decide which is best for you, given the limitations of each
approach.
Finally, when you resolve a many to many relationship with a
junction/intermediate table, how do you turn all the foreign keys into
something usable?
For example, I have a tblInstructor and a tblAddress both in a one to many
relationship with tblInstructorAddress (the junction/intermediate table).
Also, a seperate table called tblAddressType is in a one to many
relationship
as a foreign key in the junction table. When I design my form, do I use a
query to link up all the info again (Instructor name, Instructor Address
type, Address), or merely the address info as a subform of the Instructor
for
which contains Instructor details such as name? I seem to be unable to get
all of the address info connected, specifically the address type with the
address...I know this is off topic, and so if you would rather i post as a
new question in the form Forum, I would be happy to.
For a many-to-many relation, it's best not to try to do all of this in one
form. You will need a form for instructors (with a subform for selecting the
relevant addresses for them), and another form for entering the addresses
themselves.
That is a valid design, but it may be more complex than you need. Are there
many instructors who share the same address? There might be (e.g. if the
instructors live in army bases, or work out of the same buildings), but
often you might get by without the junction table (i.e. one instructor has
many addresses.)
If you do need to use a many-to-many, here's a screenshot of what it might
look like:
http://allenbrowne.com/unlinked/JunctionAddress.jpg
That's just the top of a form, where you select a client for a job, and then
select one of the client's addresses to deliver the job to (on the right.)
Code in Form_Current and ClientID_AfterUpdate limits the addresses to those
for this client. The button to the right of the address combo pops up
another form for entering a new address and associating it with the client
if you need to do that.
Again, you may not even need the junction table here.
HTH.
