access basic query Univ assignment...

[Micron]

To me, the answer to Should the tblStudentCourseInfo have the dates or the tblCourse? would be that the course dates are an attribute of the courses, so they belong there. The table for student/course info looks like a junction table since many students could take many courses, thus it joins the many to many.


As for #4, the layout suggests a crosstab query since the courses seem to be transposed from rows (in the courses table) to columns in the query result. If that is not the answer, then maybe a Union query. The problem with the Union approach is that the number of columns can only be fixed during design, whereas with the crosstab, you can get a column for every course type. Add more courses = more columns (if the query is designed correctly). If the dashes are to be taken literally, then the query fields have to be calculated; i.e. show "-" where the result is Null.

[Susy]

Thanks Micron, I haven't worked with Crosstabs yet... I will now check on that... is the SQL Code straightforward... I have been experimenting with Access but the idea, and it is something I have to work on, is to write only SQL

Thanks you all.

[Bulzie]

There is a wizard for crosstab query so go to query design and select that option in the ribbon.

[Micron]

...the idea, and it is something I have to work on, is to write only SQL

You realize this will raise the level of difficulty? Why, when there is an interface which not only makes query creation easier but modification as well.

[Susy]

Hi Micron, hi Bulzie..

Thanks both of you.

@Micron, I agree with you... but, it is an old exam... they want to test something, I guess, independent from system. Since I am learning Access, I thought I could try to solve it with Access and then see the code and make some adjustments here and there, but it is getting difficult... yeahhh! ;-)

Nice weekend you all!

P.S.: The whole assignment should have been done only with SQL...