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.

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" <RoyCarlson@discussions.microsoft.com> wrote in message 
news:A3CF298A-F4B4-4A2D-8FF0-A656095DD1BE@microsoft.com...
>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. 

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.

"Pat Hartman" wrote:

> 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" <RoyCarlson@discussions.microsoft.com> wrote in message 
> news:A3CF298A-F4B4-4A2D-8FF0-A656095DD1BE@microsoft.com...
> >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. 
> 
> 
>