Prev: Player Statistics
Next: Linking First & Last Names in a table to a Full Name field in anot
From: Dave Schoeff on 29 Dec 2009 08:42
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
From: Steve on 29 Dec 2009 11:07
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
santus(a)penn.com


"Dave Schoeff" <DaveSchoeff(a)discussions.microsoft.com> wrote in message
news:B668B8E0-F9D4-43B8-83A2-0216F2818BFC(a)microsoft.com...
>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


From: Duane Hookom on 29 Dec 2009 11:36
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.

--
Duane Hookom
Microsoft Access MVP


"Dave Schoeff" wrote:

> 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
From: Dave Schoeff on 29 Dec 2009 13:21
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.
--
Dave


"Duane Hookom" wrote:

> 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.
>
> --
> Duane Hookom
> Microsoft Access MVP
>
>
> "Dave Schoeff" wrote:
>
> > 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
From: Dave Schoeff on 29 Dec 2009 13:35
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.
--
Dave


"Duane Hookom" wrote:

> 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.
>
> --
> Duane Hookom
> Microsoft Access MVP
>
>
> "Dave Schoeff" wrote:
>
> > 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
 |  Next  |  Last
Pages: 1 2
Prev: Player Statistics
Next: Linking First & Last Names in a table to a Full Name field in anot