|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
December 29th, 2009, 01:42 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
Table structure for Course Attributes
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 
|
|
#3
December 29th, 2009, 04:36 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Table structure for Course Attributes
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
|
|
#4
December 29th, 2009, 06:21 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Table structure for Course Attributes
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
|
|
#5
December 29th, 2009, 06:35 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Table structure for Course Attributes
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
|
|
#6
December 29th, 2009, 09:35 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Table structure for Course Attributes
On Tue, 29 Dec 2009 10:21:02 -0800, Dave Schoeff
wrote: 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. -- John W. Vinson [MVP]
|
|
#7
December 29th, 2009, 09:36 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Table structure for Course Attributes
On Tue, 29 Dec 2009 10:35:01 -0800, Dave Schoeff
wrote: 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. -- John W. Vinson [MVP]
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
