Grouping/relating fields

[jbzy324]

Hi all; please bear with me, this is my first access project and this may be a silly question - I suspect there's a fundamental gap in my knowledge and appreciate any help.



My question is regarding data organization; For each Record I have (3) fields that have something in common and should, for the sake of analysis, be grouped together. As an example, if my PK is "Book", the three fields are "Page", "Line", and "Word" - each of the 3 pieces of data are worthless on their own, and need to analyzed as a group, yet they are distinctly different pieces of information and can't be lumped into a single field. Moreover, each Book could have many different page/line/word entries.

The best way I can think to visualize what I want to do is with a three dimensional table, or with "Stacked" fields. If I'm looking at a data table, there's a single entry, but that single entry has my three pieces of information stacked together.

Organizationally, I'm having a tough time coming up with the best way to store this info. I'm sure this isn't a new issue, but I'm a laymen when it comes to this stuff - perhaps I'm not thinking about it in the proper terms to be able to come up with a useful search.

Thanks in advance to anyone who can point me in the right direction!

[ranman256]

That sounds like you can find anything with that structure. THO, you cant PK [book] fld , else you'll only get 1 "LITTLE WOMEN" with 1 page.
I would INDEX the fields, but dont key it.

[rpeare]

in terms of a data structure you'd have something like

tblBooks
BookID  BookName
1       The Name of the Wind
2       A Wise Man's Fear

tblPages
PageID  BookID  PageNum
1       1       1
2       1       2
3       1       3
4       2       1
5       2       2 
6       2       3

tblWords
WordID  Word
1       The
2       name
3       Kvothe
4       Denna

tblLines
Line_ID  PageID  LineNumber
1      1       1
2      1       2
3      1       3

tblLineWords
LW_ID  Line_ID  Word_ID
1      1        1
1      2        4

I'm assuming you're just giving an example of your problem where you have multiple layers of data

The concept is how to capture the data so that it's the easiest to capture (non duplication of data entry, in this case it could be handled easily with forms/subforms, or on an unbound form) and store in a method that's easy to reference the data. You could theoretically recompile the book from start to finish with this type of model. Again I'm assuming this is an example that is not related to your actual needs but you get the idea.

[jbzy324]

rpeare - correct, the book was merely an example, but that data structure is similar to the structure I thought I'd have to explore (that is, each segment of data is stored in separate relational tables.)

In this case, tblLineWords would be a Junction Table?

I think that's enough to get me started, or at least to be able to approach the forum with a less nebulous question next time. Thanks so much for the help!

[rpeare]

yes tblLineWords would be a junction table