If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
update many fields in a form from existing info.
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. |
#2
|
|||
|
|||
update many fields in a form from existing info.
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" wrote in message ... 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 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. |
#3
|
|||
|
|||
update many fields in a form from existing info.
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" wrote in message ... 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 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. |
Thread Tools | |
Display Modes | |
|
|