Design Record with an Array of numeric values

[ChrisGreaves]

GenWd001.png

My knowledge of Access is deplorable, but given the number of letter-strings I want to accumulate (about 200,000), a database table seems the only way.
I thought to build a record with a letter-string, followed by an indefinite set of numeric pairs.
Each pair of numeric values will represent (1) a Pointer to a record that holds the letter-string of the following word and (2) a Count of the number of times our record's letter-string has been followed by the following word.

GenWd002.png

I really want a type "NumericAarray" in Access2003, but I think I won't get it.

I believe that I can't be the first to want a variable-length array of values in a record.

Please and Thank you, how do Access experts build my type of record-structure?

My overall goal is a two-phase application to Harvest strings of letters and then Generate sentences using my harvested strings.
Thanks, Chris

[HansV]

You shouldn't stored similar information in repeated fields. Instead, create a new record for each combination.
Create two tables:
1) A table with all unique text strings, each with a unique ID, for example an AutoNumber field:

S0001.png

S0004.png

2) A table with a number field for the ID of the main string ("Each"), a number field for the ID of the string that follows it ("Record"), and a number field for the number of occurrences:

S0002.png

S0005.png

This way, you can create as many records for the same FirstStringID as needed.

Create relationships between the ID of the first table and each of the ID fields in the second table (add the first table twice to the Relationships window for this):

S0003.png

[ChrisGreaves]

You shouldn't stored similar information in repeated fields. Instead, create a new record for each combination.

Thank you Hans.
I have previously set up relational databases composed of tables such as Company; People; Projects, but thought I might be able to avoid what to me seems like overhead when "all i want is a little array".
I suspect that when I boil it all down to machine instructions, there is little difference in code size or speed of execution between identifying one element of a table and one element of an array.
Thanks
Chris

[HansV]

The way a relational database handles a dynamic array is by storing each array element in a record in a table.
There are efficient method for sorting, filtering and querying such a table.
Of course, it would be possible to store a dynamic array in a blob field, but (1) there are no built-in ways to process such an array, and (2) it is inefficient since a blob field is not stored in the table itself, but in a separate table, while the table only contains a pointer to that field.

[ChrisGreaves]

GenWd003.png

I think I've got it now.
I shall write some sample WordVBA code and use this table to see that I get what I want.

The stringId hookups rattled me at first: I had already created an "Id" auto-number field for the primary key, and hooked those together. At least now I know how to delete a relationship and start over!

The doubled relationship had me worried for a while. I was waiting for "CIRC" to pop up and pester me.

I have changed the identifiers to match my existing documentation. I am always interested in the following string, never the preceding string.
Thanks Again
Chris

[ChrisGreaves]

You shouldn't stored similar information in repeated fields. Instead, create a new record for each combination.

I haven't started coding yet, Hans. This morning I realised that the application centres on "word-pairs", a word followed by another word, hence my terminology <Letter String> and <Following String>. Harvested word-pairs are chosen according to a tally or count associated with each word-pair.

GenWd004.png

I am now leaning towards a simple one-dimensional, unrelated data table with three fields. The table can be accessed by <Letter String> (the first field) or by <Letter String> <Following String> (the first two fields) to obtain or update the tally.

I suspect that in the back of mind was the idea that I really needed a simple look-up array, but that a MSWord table was too slow, and that an internal array was too cumbersome to write and read as a BLOB file. So I thought "database".
I don't need an array within a record.
I need only have a simple table that IS an array.

Given a word <Letter String>, I can locate all relevant word-pairs within my table, and then choose from the set of word-pairs according to the criteria of tally (which is a weighting scheme)
Cheers, Chris