Relatively new user - need to create 3D table

[Myshkin]

I am experienced with Excel but only moderately so with Access. I am not much of a VB programmer.

I have several textual tables with one column each - a sample of text parsed into one word per record. e.g. Table 1: "The / quick / brown / fox" (four records), Table 2: "My / name / is / Joe / Brown" (five records).

I want to perform the same functions on each table, such as determining the number of words, the number of verbs, the number of adjectives, etc...

The text is actually very long (hundreds of records) for each table and I am likely to have over a hundred tables (student essays).

I have created a set of queries that does all the functions (yea, me!) but so far I have only been able to do this for one table.



What I think I need is a way for Access to essentially create a table of tables and then be able to run the queries on that master table in order to have the functions run for each essay. I'm not sure how to have Access essentially call each of the essays in turn, lookup the records in that called essay and perform the functions.

Thanks for any help.

[jzwp11]

The rule is that like data should be stored in 1 table, so the words should be in 1 table not hundreds of tables. Since the words are related to essays which in turn are related to students, I assume that you would want to capture that info as well. A structure similar to this would be appropriate

tblStudents
-pkStudentID primary key, autonumber
-txtFName
-txtLName

tblStudentEssays
-pkStudentEssayID primary key, autonumber
-fkStudentID foreign key to tblStudents
-txtEssay

tblStudentEssayWords
-pkStuEssayWordsID primary key, autonumber
-fkStudentEssayID foreign key to tblStudentEssays
-txtWord (your field to hold the word)

Out of curiosity, how do you identify a word as an adjective, noun, verb etc.? Do you do that manually? If so you could add another field to the tblStudentEssayWords that you could use to flag the type of word.

tblStudentEssayWords
-pkStuEssayWordsID primary key, autonumber
-fkStudentEssayID foreign key to tblStudentEssays
-txtWord (your field to hold the word)
-fkPartsOfSpeechID foreign key to tblPartsOfSpeech

tblPartsOfSpeech (essentially a lookup table that identifies how the word is used: noun, verb etc.)
-pkParstOfSpeechID primary key, autonumber
-txtPartOfSpeeech

You would run an aggregate query and group by the part of speech field to do your counting

[Myshkin]

In tblStudentEssays, txtWord is the field that holds the word. But if I have hundreds of words in hundreds of essays, then is the table just one long running list where as I switch to the next essay I am just changing the kfStudentID number? As a new essay comes in I would just append it to the running list? I suppose I could make a Crosstab to show the essays in their own field. Do you know the maximum number of records that a database can hold?

I had done as you suggested to query other tables with lists of adjectives, nouns, etc...

Thanks much.

The rule is that like data should be stored in 1 table, so the words should be in 1 table not hundreds of tables. Since the words are related to essays which in turn are related to students, I assume that you would want to capture that info as well. A structure similar to this would be appropriate

tblStudents
-pkStudentID primary key, autonumber
-txtFName
-txtLName

tblStudentEssays
-pkStudentEssayID primary key, autonumber
-fkStudentID foreign key to tblStudents
-txtEssay

tblStudentEssayWords
-pkStuEssayWordsID primary key, autonumber
-fkStudentEssayID foreign key to tblStudentEssays
-txtWord (your field to hold the word)

Out of curiosity, how do you identify a word as an adjective, noun, verb etc.? Do you do that manually? If so you could add another field to the tblStudentEssayWords that you could use to flag the type of word.

tblStudentEssayWords
-pkStuEssayWordsID primary key, autonumber
-fkStudentEssayID foreign key to tblStudentEssays
-txtWord (your field to hold the word)
-fkPartsOfSpeechID foreign key to tblPartsOfSpeech

tblPartsOfSpeech (essentially a lookup table that identifies how the word is used: noun, verb etc.)
-pkParstOfSpeechID primary key, autonumber
-txtPartOfSpeeech

You would run an aggregate query and group by the part of speech field to do your counting

[jzwp11]

But if I have hundreds of words in hundreds of essays, then is the table just one long running list where as I switch to the next essay I am just changing the kfStudentID number?

You would use a main form (student) with a subform (essay) with a subsubform (words). To move from essay to essay for a particular student you would move from one record to the next in the subform. To move from student to student you would increment the main form. The words of a particular essay would be displayed in the subsubform.

I suppose I could make a Crosstab to show the essays in their own field.

I'm not following what you are saying here.


The number of tables allowed is finite, but the number of records is only governed by the maximum size of the database (2Gb for Access 2010 if I remember correctly)