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
0
Utf
12/29/2009 1:42:01 PM
access.tablesdbdesign 510 articles. 0 followers. Follow

6 Replies
785 Views

Similar Articles

[PageSpeed] 35

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@penn.com


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


0
Steve
12/29/2009 4:07:14 PM
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
0
Utf
12/29/2009 4:36:01 PM
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
0
Utf
12/29/2009 6:21:02 PM
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
0
Utf
12/29/2009 6:35:01 PM
On Tue, 29 Dec 2009 10:21:02 -0800, Dave Schoeff
<DaveSchoeff@discussions.microsoft.com> 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]
0
John
12/29/2009 9:35:15 PM
On Tue, 29 Dec 2009 10:35:01 -0800, Dave Schoeff
<DaveSchoeff@discussions.microsoft.com> 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]
0
John
12/29/2009 9:36:02 PM
Reply:

Similar Artilces:

Adding records to table from a form, with pre-populated values
I'm trying to construct a form that will let me translate pre-defined "base" sentences to several languages. I have the following tables: BaseTexts: ID, Text Languages: Language Translations: BaseTextID, Language, Translation but the Translations table only has records if a translation actually exists. So I have a query that gets me all (BaseTextID, Text, Language) triplets for which a translation is needed (Text <-> BaseTextID is 1-1 mapping, but users need to see the text itself in order to translate), and I want to present a multiple-records form with 4...

Updating two two tables based upon the response to questions in a subform
Me again. I'm trying to populate two tables based upon the results to questions on a subform. I have a form and one subform. The subform has a variable number of questions related to the form. Each question is limited to either "Pass", "Fail", or "N/A". If the user should select "Fail" on a question, I want to add a record to a table called "tblCARs" and a record to a table called "tbl CARLineItems". However, for every subsequent "Fail" response I want (need) to only add a record to the table "tblCARLi...

Need help with SQL tables
After running a security reconcile sa was no longer a user in GP. I received the answer below about re-adding it, but I don't know how to add a userid in to a table. I can't find anything on knowledgebase about this. We don't have the SQL Enterprise Manager but use the SQL 2005 Management Studio Express. Can anyone help me? I just need to know the script that will add it to these tables. Thanks. Tracey D "Therefore it will be just a matter of adding 'sa' USERID into the following tables in DYNAMICS database as a minimum. Once added, use ano...

Intelligent Pivot Table with Large Data Sets
Pivot Tables are great with small datasets. Does anyone have suggestions of products that can datasource from a ODBC source and manage large datasets (1M+ rows), visualizing the dat in a crosstab/pivottable format ? Unfortunately the underlying logic in the PivotTable/MSquery link isn' too intelligent in that is always wants to read the whole darn databas table before even presenting a field list. I assume this is due to th abstraction of the data created by sticking MS/Query inbetween th Excel pivot table service and the actual datasource? Seems pretty simply from a conceptual POV: 1. ...

2 pivot tables on one tab
Is it possible to put 2 Pivot tables on the same tab in my workbook? Or does anyone have any ideas on what would be the best way to chart Capital Pircahses? I have 5 coums and 4 rows. The rows are per division, and the columns are for the disposal amount, gain or loss, book value, depreciation & purchase price. I already have one pivot table in my report. Would this be too repeatative? Thanks You can put multiple pivot tables onto a worksheet. If the second one is based on the same data, select and copy the first, select a cell some distance away, and paste it. Each pivot table...

Please help with getting the right structure from dataset using GetXML()
Hi everyone, I need your help getting my getXML method to function the way I need it to. I have a stored procedure that returns data into a dataset (I am using VB.NET). I use getXML with dataset and I get the following result: <NewDataSet> <Table> <StudentFirstName>John</StudentFirstName> <StudentMiddleName>J</StudentMiddleName> <StudentLastName>Smith</StudentLastName> <StudentAddressType>Permanent</StudentAddressType> <StudentAddressLine1>123 Some Rd</StudentAddressLine1> <StudentAddressLine...

A save operation on table "PM_Transaction_Work" casused a sharing
Hi, When my user post a PM batch using VPN, the connection is broken and the batch posting is interrupted. The status is "Table Updates Interrupted". When I try to recover the batch through Batch Rocovery, it did not allow to. saying that" Batch NOV INVOICES" failed to complete posting. Use Batch Recovery window to complete the posting process. " (I am at the batch recovery window already!!!!) When I click "more" then I see the following: "A save operation on table 'PM_Transaction_WORK' caused a sharing error." Further invest...

Table object
There is a little table object that shows up on the top left of a table that you can use to move the table to the left or right. But I can't seem to figure out how to get that table object to appear. You have to click around select the whole table/ deselect the table and then at some point it appears. How do you get it to appear so you can move the table? I am getting tired of trying to randomly get it to appear. Thanks, Tom Which version of Word? We support about 20... On 7/04/10 2:14 PM, in article O4J9ajg1KHA.4832@TK2MSFTNGP04.phx.gbl, "tshad"...

Pivot Table Freezes
I have many pivot tables off a large database and they periodically freeze when I refresh the data. Only solution is to crash out. Any ideas, comments, solutions out there. The machine has been checked out OK. I work on a network - checked out OK. Maybe this is the problem: XL2000: PivotTable Updated Slowly with OLAP Data Source http://support.microsoft.com/default.aspx?scid=237469 Jimbo wrote: > I have many pivot tables off a large database and they periodically freeze > when I refresh the data. Only solution is to crash out. Any ideas, comments, > solutions out there....

Copying fields from combo box to a table
Hi, I am creating a database for our Machine Maintence Report (access 2003). I created a combo box from table1 and I want to have the selected fields (i.e. Machine, Technician, Engineer etc...) in table1 to be copied in table2. This table2 has the same fields Machine, Technician, Engineer plus the other field that will be updated when the maintenance is done such as remarks, issue and data readings. Looking forward for your help. If you really have a need to store this information in multiple tables, you can use code in the After Update event of the combo box to push the valu...

Find cell numbers in a table so I can multiply
Hello, I have a word document and in the documents header there is a table. This table a has diferent number of cells in each row like: Row one: 2 cells Row two: 4 cells Row three: 2 cells Row four: 10 cells Row five: 2 cells What I wanted to do is multipy 3 cells together thats in row four and show the total in the same row. I have a number in the 3rd cell that needs * by the number in the fith cell that needs * by the number in the seventh cell and totaled in the 9th cell. I know I have to select the ninth cell and select table/formula then what? -- Thanks, Chad I ...

SQL to get the lowest unique value from table
Hi I'm looking for some SQL to get the lowest unique value in a table: eg, <table> ID BID 1 1 2 1 3 1 4 2 5 2 6 2 7 3 8 4 3 is the lowest unique value. I would need to return the ID. Any suggestions? thanks Lee "Lee" <lee@digital-interactive.com> wrote in message news:a981d263-7f22-4b14-8493-014559da8590@z41g2000yqz.googlegroups.com... > Hi > I'm looking for some SQL to get the lowest unique value in a table: > eg, > > <table> > > ID BID > 1 1 > 2 1 > 3 1 > 4 2 > 5 2 > 6 2 ...

Table calculations. Season from Date in Hunting Harvest Database.
I am trying to set up a hunting harvest database. I would like to add a column to the original data table that automatically calculates the hunting season, which is based on a specific harvest timeframe. Each harvest record entry has a specific date. For example, when a record of a deer harvested on 12-3-2007 is entered; I want the database to calculate that the date occurred during the fall 2007 season. Thus, I want 2007 to be automatically entered for this record in a separate column. However, our seasons last into the new year, so if for example a record is entered of a ...

Pivot Table Data Ranges
I just upgraded to excel2000, and am finding out that keyboard shortcuts used in 97 do not exist in 2000. Does anyone know of a KEYBOARD shortcut to modify, expand and or create data ranges in the dialog box for entering the range for the pivot. I have many data 'lists' which are added to each month, and when I want to update the corresponding pivot table, I used to simply hit "shift" + "end" + "down arrow" to automatically go to the last populated cell in the list. In 2000, I either have to enter in the cell addy's manually or use the mouse...

Pivot Tables show zero balance
Hi - I have a pivot table.... I want to show customers that have an outstanding balance. when i sort by month, i see everything, totals of who paid and who ha not. I want to sort to see only who is outstanding. How do I do this? CURRENT SETUP: Sort by page = date of invoice (by month) Rows = client name, then the tota -- Message posted from http://www.ExcelForum.com You could add a Status column to your data source, and calculate the account status. For example, with customer names in column C and amounts in column H: =IF(SUMIF($C$2:$C$39,C2,$H$2:$H$39)>0,"Outstanding&...

State table lookup
Hi I have a form for entering Customer data. When entering a new record, I have a lookup combo box to validate the state that the user enters. The combo box displays the state code and the state name. (ie. Ca and California). The state code is the bound value and the state name is the display value. The only time I want to display the full state name is when the user is entering data into the form. I want the state code to be displayed on queries, forms, and reports. Is there a way to do this ? Thanks in advance Mark Create a table with 2 fields like this: StateID Text (say...

Web table to Excel...
Could someone help me exporting the tables from the following web-page into excel ? http://www.ekdd.gr/ESDD/IH/REBBBD-esdd-epityxontwn.htm (use unicode encoding) is this impossible ? ...

MFC String-table in C# ?
Hi, maybe a bit OT for this newsgroup, but I know that people here use C#, too. So, does anyone know how the MFC string-table technique map to C#/WinForm? I initially thought about using an integer->string map in C#, but is there some built-in mechanism to manage that? Thanks, Giovanni "Giovanni Dicanio" <giovanniDOTdicanio@REMOVEMEgmail.com> ha scritto nel messaggio news:ugqw1g7LJHA.1736@TK2MSFTNGP03.phx.gbl... > So, does anyone know how the MFC string-table technique map to C#/WinForm? ....after some web search, it seems that C# has a ResourceManager class ...

Copying and Pasting Excel Tables into Word
We do our financials in Word, and copy and paste special as enhanced metafiles the tables that we do in Excel. This works perfectly in Word 2003. In 2007 however, when we past the table and print it, the table looks different (letter spacing funny, not smooth). I have tried printing to five different printers and it still happens. I'd suggest a slightly different approach. If you insist on using enhanced metafiles, try the following: 1. Select the range that you want to copy. 2. Instead of Copy, select the dropdown under Paste>As Picture>Copy as Picture. 3...

Sizing Data Tables on a chart
When I have a data table attached to a chart and size the chart, the data table does not size proportionately ...

Inserting New rows into IV00108 Table
I am trying to insert new rows of data into the Iv0018 table I have another table in SQL with tthe data but when i use DTS to append this data it will not let me because of a null value being inserted in the DEX_row_Id column. Is there a SQL script that will allow me to apend this data to this table since the part numbers already exisits this is another pricelevel i am adding. This is SQL 2000 and GP 9.0. Thank you Ken DEX_ROW_ID is an identity column so it will auto populate. In DTS do not map a value for DEx_Row_ID (ignore it) "kfoster@pfeinc.com" <kfosterpfeinccom@disc...

how to link a .txt ( .dat) as a table by VB
Hi from Meudon (France) I want to link a .txt file as a table in my acces db. How to by VB : code and reference(s) (Acces 2003) Thanks by anticipation ...

Finding PostingAccounts in Tables
?Hello Folks, Under Tools>Setup>Posting>Posting Accounts. where there is a list of all the accounts to post the transactions, which I am trying to find in the Tables as well. I I can spot a Table under Company SY01100 but that displays the data in a different way, Also when I copied this table from A to B ; In A it has all the account #s but in B it is all Blank. Should I be looking somewhere else? please help Regards, Hi Viol-8-r, If you are looking for the GL Master files then you are looking in the wrong place. The Master files themselves are in GL00...

Query table using criteria from an Excel document
Hello, I have a Access table of names and addresses. There is a postcode (zipcode) text field. I also have an excel spreadsheet with a single column of postcodes; no duplicates. I need to get query to return each name and address in the table which has a postcode listed in the spreadsheet. Typing each postcode manually into the query seems clumsy. Any ideas? Add a link to the Excel data. Create a new query joining the linked Excel data and your Access table on the postcode field. Those that match will result. -- Regards Jeff Boyce www.InformationFutures.net Microsoft Office/A...

Problem inserting calculated pivot fields into Pivot Table (2007)
Working with Excel 2007 and a Pivot Table. Created a Calculated field. The field shows in the Field List, but Excel will not let me drag the Calculated field into the Pivot Table areas (other than into the Values area). What am I doing wrong? Hi A calculated item or field, can ONLY be allocated to the data area. It's position within the data area can be modified, but it cannot be moved to any other area -- Regards Roger Govier wamiller36 wrote: > Working with Excel 2007 and a Pivot Table. Created a Calculated field. The > field shows in the Field List, b...