update many fields in a form from existing info.

[Roy Carlson]

I have 2 tables. One table stores various information about companies
including company name, address, etc. The second table stores student names.
In a form, I am able to enter mutiple student names into a memo field and,
as of right now, type in the company name, address, etc. (it is not linked to
the company name table at all).

What I want to do is this: from the student add form, search the company
table for a company based on partial name and/or zipcode, select one company
from a possible 1 or more companies, and have the fields in the form
automatically fill in - pulling the information from the companies table.
If the company searched for does not exist, I want to be able to prompt the
user of this, and give the user the ability to add the company.

The end result is that I merge this information in a Word document to print
envelopes/receipts to be sent to the company.

First, do I need to have the company information in BOTH the company table
AND the student table? I'm thinking NOT, as I could use a query. But I'm
not the expert here, so please advise me.

 

[Pat Hartman]

Step away from the keyboard - you need to rethink your table structure
before you move on. Adding multiple student names into a memo field is the
WRONG way to do whatever it is you are trying to do. The way to handle a
1-many relationship is by placing a foreign key in the many-side table.
That means that if a student can be associated with a SINGLE company, you
would add a companyID to the student table. However if a student can be
associated with many companies and a company can be associated with many
students, you have a m-m relationship and to implement that, you need a
third table which is commonly referred to as a relation or junction table.
This third table will contain the keys to the two tables it is linking and
usually nothing else but we'd need to know more about your application to
say for sure.

Do some reading on normalization and take a look at some of the template
databases created by Microsoft as examples. Their datanames are poor
practice examples, but usually their schemas are ok.

 

[Roy Carlson]

I have to do the memo field thing for the student names. The document I
print them out on requires it...

The receipt for one company will print out like this:

StudentA
StudentB
StudentC...and so on. There may be 25 students for this one company. Once
I print these receipts/envelopes/letters, I DELETE ALL of the student data,
as it is no longer necessary, and may change for the next semester's
scholarships. The students aren't the issue here at all...

Right now I type in many students, and add the company to those many
students. It works fine. But I'm leaving after this semester and I want the
people who take my place to have an easier job than I do. SO, I'm trying to
keep a listing of companies in the database that can be linked to constantly
changing lists of students. This way the user won't have to type in a
company more than once, ever, while still having to type in student names.

The company table has nothing in it right now...once I get this database
working the way I want, it will have a long list of companies which, using
the student form, can be searched for and added to the temporary student
record. Once the stationary is printed, we delete the student record.