Table relations seem to mess up union query (1 Viewer)

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

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.

Click to expand...

I stopped after reading that. Your two tables are not properly structured; or named. Let's tackle that first. You shouldn't store attribute information in table or field names. '2014' shouldn't be stored in any table/field name, but as a value in a field. You don't have an algebra table and a geometry table and a history table--you have a field to store what value a class has for its type. Same thing with year--make a field for it.

Onto the structure--why did you put ID fields in each table? You got the part where you are suppose to do it, but you didn't grasp the concept. When you make a relationship between two tables, you use the ID field as a foreign key into the other. You did that with the CourseCode field instead of the ID field. If you have an ID field, that's the one you use, not any other.

That second table (which you should rename to something like 'CoursesOffered' is going to be really simple. First, [CourseType] isn't an attribute of when it was offered but of the course itself--that field should go in the ListOfAllCoursesTables. [CourseWeight] should go there as well. So this should be the structure of that second table:

CoursesOffered
OfferedID, CourseID, CourseYear, Participants

That's it, all the other information can be linked to via the CourseID.


Work on your strucutre before trying to tackle whatever issue you are currently trying to resolve.

Thanks a lot for your response. Your remarks about the table design made a lot of sense. I started afresh and redesigned the database according to your suggestions and now... it works!

Simone