One form for many tables

[Guest]

I am trying to create a Database of schools. I have 1 main school table with
the name and then seperate tables for teacher info, billing address, campus
address and orders. These are all 1 to many relationships with the school
name as the primary key of the school info table and a foreign key in the
other tables.
I would like to have a single form where I can enter all the schools initial
information at once. I created a query with all the tables but I can't seem
to enter new information this way. I tried creating a form based on the
query and setting it to dynaset inconsistent, but I have to enter the school
name again for each table. I also have read that this is not good practice.
Is there a better way?

 

[Steve Schapel]

BP,

Is the name of the school the Primary Key field in the Schools table?
If so, then this also needs to be in the other tables as a foreign key,
but you would not need to enter this in to the other tables separately
if you use a form based on the Schools table as a main form, and
suitably set up subforms based on the other tables, using the school
name as the Link Master Fileds and Link Child Fields property settings
of the subforms. I imagine many people would use some sort of school
code or ID rather than the school name for this purpose, though
technically there would be no need to if the school name can be
guaranteed to be unique.

Do you really have the possibility of multiple billing addresses and
campus addresss for each school?

 

[Guest]

Thank you,
I have the primary key of the schools table set to the school name and am
using it as the foreign key in the other tables. I could make it an
autonumber, is that better development practice? I don't imagine that more
than one school will have the same name, but I guess it is possible. I have
wondered if any school really will have multiple campuses or billing
addresses. I thought that this would give me more flexibility just in case,
but it is very unlikely. Would you suggest making it all 1 table or seperate
tables linked in a 1 to 1 relationship? The teachers, students and orders
tables will have to still have to be many to one.

How do I go about setting up subforms? I tried something, but it didn't look
very proffesional. My priority is to do it in a way that will be as self
explanatory as possible for the sales people whow will be entering the data.

Thank you,
Brian

 

[Steve Schapel]

Brian,

As mentioned before, there is really no technical reason not to use a
unique school name as the primary key. As for "better development
practice", you will hear arguments on both sides. I would personally
use a code or autonumber. To me, the biggest danger with the "natural"
data in this case is making sure edits are propogated in the case of a
school changing its name, or spelling errors being corrected, and such
like, but there are certainly other means of taking care of these
eventualities.

Creating a subform is simple. As an example, make a form based on your
Teachers table. Open the Schools form in design view, so that you can
also see the database window on your screen at the same time, and then
just drag the Teachers form from the database window and drop to the
detail section of the Schools form. Open the properties of the subform,
and set the Link Master Fields and Link Child Fields properties to the
name of the primary key / foreign key fields. However, making it look
good, your other question, is another matter. Form design is an art,
which involves attention to a number of factors, and sometimes requires
considerable time and effort to make it attractive and user-friendly,
but it is worth it in the end.

 

[Brendan Reynolds]

The company I work for develops software for schools. In this part of the
world at least, not only is it possible for multiple schools to have the
same name, it is very common. Schools here have unique 'Roll Numbers' that
are assigned to them by the Dept. of Education, so we use that.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.