Table structure for Course Attributes

D

Dave Schoeff

I have a database which stores information about courses. This Course table
structure is very simple: CourseID, Description, Title, etc. I need to
store other information about the courses, but this information is more
irregular. I was thinking about creating a CourseAttribute table with just a
few fields: CourseID, AttributeType, AttributeValue, SortOrder. If I need to
store books needed, it would be in Attribute records: CourseID, AttributeType
= "Book", AttributeValue= bookID. A course may have several books, or no
books. If I need to store lists of Course Objectives, each objective would
have a record in the Attributes table: CourseID, AttributeType = "Objective",
AttributeValue= text of the objective. I'm looking at an attribute-oriented
solution because my users keep coming up with new attributes for the courses
(test questions, vocab, etc). I don't want to keep adding new specialized
tables -especially since some ayttributes are unique to particular courses.
My design makes sense, until I try to aggregate the data about a course by
combining the Course record and the attribute records. In SQL, I would
probably use a cursor to assemble all of the attributes into a single
value(probably a comma separated list). I'm currently using VBA to solve
this problem in Access, but there should be a more elegent solution. --
Dave
 
S

Steve

Hello Dave,

How about:
TblCourse
CourseID
CourseTitle
CourseDescription
etc

TblCourseObjective
CourseObjectiveID
CourseID
CourseObjective

TblBook
BookID
BookTitle
BookAuthor
etc

TblCourseBook
CourseBookID
CourseID
BookID

TblCourseVocab
CourseVocabID
CourseID
CourseVocabText

TblCourseTestQuestion
CourseTestQuestionID
CourseID
CourseTestQuestion

All attributes like Vocab and TestQuestion would have the following table
structure:
TblCourseNameOfAttribute
CourseNameOfAttributeID
CourseID
CourseNameOfAttribute

All attribute tables are linked to a Course in TblCourse by CourseID.

Steve
(e-mail address removed)
 
D

Duane Hookom

Why do you need to assemble all of the attributes into a single value? You
can use subforms and subreports to display this information. There is a
generic concatenate function (search google on my name and concatenate) that
could work but it also requires vba.
 
D

Dave Schoeff

Your Concatenate function is all over Google. Took a little bit to find it.
So I can use a function inside of a SQL statement? What a concept - just
like a udf in Sql Server. This will do exactly what I want. The VBA is no
barrier, I just didn't know how to avoid writing a specific function for
every attribute I wanted to aggregate. Can I use a function in a query
designed in the Query interface? That would make it possible for my advanced
users to write their own queries. They get real pale when I show them the
VBA window.
 
D

Dave Schoeff

This application has forms which could display the data in subforms or
subreports. The problem is I need to get output in Excel. I was hoping to
get a query that would roll all of the data for each course into records
that could be exported.
 
J

John W. Vinson

Your Concatenate function is all over Google. Took a little bit to find it.
So I can use a function inside of a SQL statement? What a concept - just
like a udf in Sql Server. This will do exactly what I want. The VBA is no
barrier, I just didn't know how to avoid writing a specific function for
every attribute I wanted to aggregate. Can I use a function in a query
designed in the Query interface? That would make it possible for my advanced
users to write their own queries. They get real pale when I show them the
VBA window.

Sure. If you want the result of a function as a calculated field in a Query
just type

NewFieldName: YourFunction(argument1, argument2)

in a vacant Field cell in the query grid.

Note that searching or sorting by a field calculated in this way will be slow,
since Access cannot index calculated fields.
 
J

John W. Vinson

This application has forms which could display the data in subforms or
subreports. The problem is I need to get output in Excel. I was hoping to
get a query that would roll all of the data for each course into records
that could be exported.

Not a problem. Create the Query and export *the query* (rather than the
table). See the VBA help for "TransferSpreadsheet" if you would like to
automate the export process.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top