Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

MS Access 2003

Posted on 2012-08-23
7
Medium Priority
?
405 Views
Last Modified: 2012-08-24
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
0
Comment
Question by:jolias
  • 3
  • 2
  • 2
7 Comments
 
LVL 4

Expert Comment

by:jekautz
ID: 38327889
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
 

Author Comment

by:jolias
ID: 38327973
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
 
LVL 25

Expert Comment

by:lwadwell
ID: 38329307
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 25

Expert Comment

by:lwadwell
ID: 38329381
woops ... here is the database in 2003 format
Q-27840660.mdb
0
 

Author Comment

by:jolias
ID: 38329590
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
 
LVL 25

Accepted Solution

by:
lwadwell earned 2000 total points
ID: 38329646
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
 
LVL 4

Expert Comment

by:jekautz
ID: 38329691
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question