Look-Up Fields (Access 2000 9.0.3821 SR-1)

[chouse]

I am developing a database for our local public school. I have two tables: one containing students and one containing parents. The parent table as look-up fields for the children that references the fields for first and last name in the students table. My problems:

1. While the full names of all students appear in the look-up field, they appear in order of entry. Is there a way to make them appear alphabetically?

2. Although the full names appear in the drop down list, only the first names actually appear in the parents table. How can I get both first and last name to appear?

3. When I use mail merge in Word with my school database as the data source, the look-up fields come in as numbers, no names at all. How do I get names into my Word merge document?

Thanks for any help that anyone can provide. I am not a frequent user of Access, and have never done any programming within Access.

[charlotte]

What is the relationship between the two tables? Is the parent record ID inserted into the student record, the Student ID inserted into the Parent record, or what? If the same set of parents (or a single parent) can belong to more than one student, then you need a third table to hold the relationship between student and parent in order to accommodate as many student-parent combinations are may be necessary.

It sounds to me like you may be using lookup fields in tables, and that's not necessarily the best way to build a database. You, or whoever else uses the database, should always work through a form, so the lookups can be done at the form level making the table level lookups redundant. When you export the data to a mail merge or link to Access as a datasource, you need to use a query that includes the values themselves from the lookup table and use those expressions in your merge rather than the linking field value itself. You're getting numbers now because that is the real value in the field. The lookup values are for display only.

[chouse]

Thanks for the quick response. To answer your question, the student ID is inserted into the parent record. Each parent has his or her own record, and each parent may have more than one child.

You are correct that I am using the lookup field in the Parents table itself. Should I delete that field, and then proceed in a different mannner?

Thanks again.

[charlotte]

Does that mean you have a parent record that contains fields like Child1, Child2, Child3, etc? If so, this is a non-normalized design and will probably bite you sooner rather than later. The preferred method would be to have no Child fields in the parent record at all. Instead, you would have a third table called something like ParentChild, and it would contain a ParentID field (long, assuming the ParentID in the parent table is an autonumber) and a StudentID field. You would have a single record for each parent-child combination that applies. If you had two parents in the database, mother and father, you could have as many records as required to connect each of them to each of their children. Even if you never expect to need this structure, it will pay off the first time someone enrolls twins or siblings, and the reporting and presentation of the data is more easily controlled.

It doesn't matter a lot whether you have a lookup in a table like that, but you shouldn't be using it to do data entry. Data entry should be done through a form so that you can control not only what is displayed but also what happens when you change the value. In a form, you could have a parent record (based on a query joining the Parent table to the ParentChild table) displayed in the main form and a subform displaying the related student records (based on a query on the student table). The student subform would be linked to the parent form by the studentID in the ParentChild table.

[chouse]

Thanks again. Yes, I have created exactly the situation you described with Child1, Child2, etc. I understand that I should delete those fields, and then create a ParentChild table. But, I don't understand how I get the information into the three tables with the appropriate relationships, by using a form.

I think that this technique will also solve another problem with the database. Each parent may select one or more areas within which to volunteer. Right now I have each area set up as a separate yes/no field in the Parent table. Would I be better off having another table that establishes the relationship between each parent and the areas that he or she selected?

Thank you for your patience. While I have used Access on a limited basis before, I have never had to deal with issues like the ones posed by this one. It is important that I get it right so that it functions smoothly once school activities get under way.

[charlotte]

Handle your volunteer information the same way I suggested you relate parent and student, with a "join" table that contains a parentID and the volunteer information for each area the parent volunteers in.

As for getting the data into the tables, which way do you want to look at/enter it? Is the parent information subsidiary to the child information or the other way around? You can, of course, look at the data either way, but specific answers need specific information.

Edited later by Charlotte
I've attached a small database that will give you some rough ideas of ways to handle the records.

[chouse]

On balance I would say that the parent information is subsidiary to the child information. However, right now there are two projects that will come out of the data, one driven by child information and one by parent information: (1) We are producing a school directory that will list all child information by grade and contain an overall alphabetical index. (2) We are developing a master list of volunteers by category.

Thanks again for all your help. I'm now going to examine the database that you sent me.

[chouse]

I have taken a stab at setting up the forms that you mentioned through the form wizard, but cannot create a form that will accept anything other than data for the table to which the main form is linked. I cannot fathom how to input data that links the students to their parents, or their parents to their volunteer categories. I've re-read my Special Edition Using MS Office 2000 on Access forms, but it does not help.

Thanks for any help that you can provide.

[tmorken]

1. While the full names of all students appear in the look-up field, they appear in order of entry. Is there a way to make them appear alphabetically?

If this is a combobox or list box you can put ORDER BY with ASC or DESC after the SQL in the ROW SOURCE property of the control:
SELECT DISTINCTROW [tblTechCode].[TechCode], [tblTechCode].[TechName] FROM tblTechCode, ORDER BY [tblTechCode].[TechName] ASC;

2. Although the full names appear in the drop down list, only the first names actually appear in the parents table. How can I get both first and last name to appear?

Again assuming it's a combo or list box, As you have it , it only takes the info from the first data column to put in your assigned field. You will have to set the other fields to take their info from the appropriate column of the combo/list box.


3. When I use mail merge in Word with my school database as the data source, the look-up fields come in as numbers, no names at all. How do I get names into my Word merge document?

You need to set the bound column to the column with the info you want entered in the table. By default the combo or list box wizard takes the first field on the left as the bound column, and that field is simply a sequential number of the number of items on the list. You can change that in the control's property list to bind the column with the data field you want. You can make more than one field key off the combo or list box by setting the other fields data source equal to the appropriate column number from the combo/list box. Another method is that you can have your mailmerge run off a different query that combines related info from the appropriate tables.

[charlotte]

If you create a subform, it should be linked to the other table, not the same one the parent form is linked to. Take a look at the sample I posted and you'll see how the form and subform are based on two different tables.