MS Access 2003

I have two tables. One table has children names, grade, and child number. Table two is a tuition chart that lists tuition by Grade and then has columns for Child number. I need a query that will return the child's name and his tuition based on his grade and child number. I have enclosed two excel files showing the tables. I would like to do this by just using queries if possible.

Thanks for the help.
TuitionTable.xls
Students.xls
joliasAsked:
Who is Participating?
 
lwadwellConnect With a Mentor Commented:
Depending on how the Tuition data is maintained ... I though the old format made sense for editing; having a query to take the columns into rows is not a real issue as the table is only ever going to be small.
Creating composite fields from two values ... I would not recommend it normally.  It is easy to concatenate in queries ... harder to split sometimes.  Doing joins on "col1=col2 AND col3=col4" is simple enough.

But I am glad that you have a solution that works for you.
0
 
jekautzCommented:
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
0
 
joliasAuthor Commented:
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.
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
lwadwellCommented:
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;

Open 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];

Open in new window

Q-27840660.accdb
0
 
lwadwellCommented:
woops ... here is the database in 2003 format
Q-27840660.mdb
0
 
joliasAuthor Commented:
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.
0
 
jekautzCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.