Dear all,
I am a not-so-experienced ACCESS user, having trouble with combining information from two tables.
My database is designed as follows:
Table1: ListOfAllCourses
Columns: ID, CourseCode, CourseName
Table2: CoursesIn2014
Columns: ID, CourseCode, CourseType, CourseWeight, NofParticipants
The courses in Table2 are a subset of all courses in Table1 and CourseCode is a look-up field that gets its values from Table1.
Aims:
1. Compute a total number of credits, based on course type and number of participants;
2. Include the course name in the result.
Rules for determining the total number of credits:
a. If CourseType == "A", TotalCredits = NofCredits * NofParticipants
b. If CourseType == "B" AND CourseWeight == 2.5, IF(NofParticipants * 1 <= 10, TotalCredits = NofParticipants * 1, 10)
c. If CourseType == "B" AND CourseWeight == 5, IF(NofParticipants * 2 <= 15, TotalCredits = NofParticipants * 2, 15)
d. If CourseType == "B" AND CourseWeight == 7.5, IF(NofParticipants * 3 <= 22.5, TotalCredits = NofParticipants * 3, 22.5)
Thus, for courses of type B, the credits depend on the course weight (2.5, 5, or 7.5) and the number of participants, with a maximum depending on the course weight (10, 15, or 22.5, respectively).
The way I have been trying to do this as follows:
1. Formulate four different SELECT queries according to the rules in a-d.
2. Formulate a UNION query to combine the four SELECT queries.
The final query output should have the following columns:
CourseCode, CourseName, CourseType, CourseWeight, NofParticipants, TotalCredits.
Everything works well until the last step: the column CourseCode, which looks correct in the four separate queries, is still called CourseCode, but curiously contains the values of ID (the key) from Table1.
I first thought that this was due to the fact that I had not specified explicit relations between Table1 and Table2. Indeed, when I checked the relation that ACCESS automatically establishes between the two tables (in the query window), it turned out that it had matched ID from Table1 to CourseCode in Table2.
But how then could the simple queries produce the correct result in the first place?
I have tried changing the relation, so that CourseCode in Table1 matches CourseCode in Table2, but that leaves me with an empty table as a result.
Any thoughts on how to solve this properly are very welcome. Thanks for your time and effort.
Simone
I am a not-so-experienced ACCESS user, having trouble with combining information from two tables.
My database is designed as follows:
Table1: ListOfAllCourses
Columns: ID, CourseCode, CourseName
Table2: CoursesIn2014
Columns: ID, CourseCode, CourseType, CourseWeight, NofParticipants
The courses in Table2 are a subset of all courses in Table1 and CourseCode is a look-up field that gets its values from Table1.
Aims:
1. Compute a total number of credits, based on course type and number of participants;
2. Include the course name in the result.
Rules for determining the total number of credits:
a. If CourseType == "A", TotalCredits = NofCredits * NofParticipants
b. If CourseType == "B" AND CourseWeight == 2.5, IF(NofParticipants * 1 <= 10, TotalCredits = NofParticipants * 1, 10)
c. If CourseType == "B" AND CourseWeight == 5, IF(NofParticipants * 2 <= 15, TotalCredits = NofParticipants * 2, 15)
d. If CourseType == "B" AND CourseWeight == 7.5, IF(NofParticipants * 3 <= 22.5, TotalCredits = NofParticipants * 3, 22.5)
Thus, for courses of type B, the credits depend on the course weight (2.5, 5, or 7.5) and the number of participants, with a maximum depending on the course weight (10, 15, or 22.5, respectively).
The way I have been trying to do this as follows:
1. Formulate four different SELECT queries according to the rules in a-d.
2. Formulate a UNION query to combine the four SELECT queries.
The final query output should have the following columns:
CourseCode, CourseName, CourseType, CourseWeight, NofParticipants, TotalCredits.
Everything works well until the last step: the column CourseCode, which looks correct in the four separate queries, is still called CourseCode, but curiously contains the values of ID (the key) from Table1.
I first thought that this was due to the fact that I had not specified explicit relations between Table1 and Table2. Indeed, when I checked the relation that ACCESS automatically establishes between the two tables (in the query window), it turned out that it had matched ID from Table1 to CourseCode in Table2.
But how then could the simple queries produce the correct result in the first place?
I have tried changing the relation, so that CourseCode in Table1 matches CourseCode in Table2, but that leaves me with an empty table as a result.
Any thoughts on how to solve this properly are very welcome. Thanks for your time and effort.
Simone