MS Access 2003

I am looking at your tuitiontable.xls and if this is how your data looks in Access then you will be better off changing the way you store the data in this table. Databases should grow by adding records, not columns.  To me, it appears that each child is getting their own column.  This is not a good practice and will result in difficulties.

You should restructure your Tuition table like this:
1) Create a primary key in the Tuition table. (Access wants to do this for you automatically when you create a table.)
2) Create another column in the Tuition table for Student ID.
3) Add a Tuition column.
4) Remove the Child columns.
5) Create a one-to-many relationship between the Student.StudentID and the Tuition.StudentID.

This will invalidate the integrity of your data in that table so it will be like starting over.  I attached a modified tuition table for reference.
TuitionTable.xls

Thanks for the quick answer but I'm not sure if I explained my issue correctly. The Tuition table will never grow. The values may change from year to year, but the table will not increase in the number of records. The Student table will as I add more students. What I'm trying to accomplish is to have a query that contains the tuition amount for each child in the student table so I can make a report (Invoice) to send to the parents. The Student table has two key Items: Grade and Child Number. Based on these two values, every student will be associated with one value in the Tuition Table. IE: If a student is in the second grade and he is child number three, then the tuition for this child would be 3276.

Here is my attempt.

I made a query on Tuition to 'flatten it into rows instead of columns.

SELECT Grade, '1' as [Family Number], [1] as amount
FROM Tuition
union all
SELECT Grade, '2' as [Family Number], [2] as amount
FROM Tuition
union all
SELECT Grade, '3' as [Family Number], [3] as amount
FROM Tuition
UNION ALL 
SELECT Grade, '4' as [Family Number], [4] as amount
FROM Tuition;

Select allOpen in new window

Then created a query to join the flattened tuition data to the student data.  I also made the assumption that is the students number in family is >4 that it should join to tuition for family member 4.

SELECT *
FROM (SELECT [System Id], [Grade], [First Name], [Child Family Number], iif([Child Family Number]>"4","4",[Child Family Number]) as [Family Number] FROM StudentInfo) AS SI 
LEFT JOIN TuitionView AS TV ON SI.Grade = TV.grade and SI.[Family Number] = TV.[Family Number];

Select allOpen in new window

Q-27840660.accdb

woops ... here is the database in 2003 format
Q-27840660.mdb

Thank you for the response. I just finished doing exactly what you said to the Tuition Table in excel and then imported it as a table in access. The Key to the Tuition table is the Grade-Child Number. IE 1-3 meaning 1st Grade-Third child in family etc.  I then wrote a query for the Student Table with a calculated field combining the Grade and Child Number with a hyphen inserted. It was then easy to join the two to get the info needed for the invoice.

I misunderstood the tuition table. If it will always remain that size, I see no need to modify it. I think lwadwell's solution looks promising.