Require help with "data collection" process

[Tom]

I'd like to run a problem w/ you and hope to get some ideas as to what may
be the most efficient way of capturing matrix data (and transferring an
Excel data collection process into an Access database)

Let me begin by giving you some sample data. Currently, data is collected
in a very fundamental spreadsheet (I'm only focusing on those columns that
pertain to my question).

Let's say the spreadsheet contains only 3 columns (A:C):
- column A: "Topic"
- column B: "Category"
- column C: "Comment"

BTW, there will be 1:M relationship between Topic and Comment.

Now, both columns A and C are self-explantory and wouldn't give me an issue
in collecting the data in a meaningful format. However, I need to think
about good ways of storing the "Category" value. Here's why...

"Categories" can be created on 6 different "levels". For instance, on the
1st level I may have records whose values take on e.g. "1, 2, 3, 4, etc.".
On my 2nd level, I have their "children" e.g. "1.1, 1.2, 1.3, 2.1, 2.2,
etc.). The same for the 3rd level containing "1.1.1, 1.1.2.... and so on).
Those "levels" could go down to the 6th sublevel (e.g. 1.1.1.1.1.1).

Now, in the current process, members use the spreadsheet in meetings and
have to quickly capture feedback from discussion points. Very often, time
is of essense and they simply have to enough time to enter all required info
into the spreadsheet. Further, they sometimes just wouldn't know to which
"Category" a topic/comment(s) belongs.

As a result, they sometimes leavel the cell of "Category" empty and may
complete it later on. And, if they do know to what category a
topic/comment(s) belongs, they're mixing data from different levels in one
and the same column. That in itself become a problem as it doesn't lend
itself to good querying procedures.

Problem at hand:
===========
a. Giving the fact that I still need to capture the information quickly AND
I sometimes don't necessarily know at which level a topic falls under (e.g.
"Is it 1.3 vs. 1.4.1?"), I couldn't built a table structure that requires
drilling down through several levels and eventually enter my comments.

b. I may need to create a form that allows me to enter data for "Topic" and
"Comment" only. Then, whenever analysts have some time (after the meeting)
they need to go back and "link" the records to the appropriate level. If
they decide that a topic (record) belongs to e.g. "1.3, 1.3.1, and 1.5.4.2),
I don't necessarily want to drill via 1 to 1.3 to 1.3.1 and via 1 to 1.5 to
1.5.4 to 1.5.4.2). My preferences would be to somehow "link" the record
to "1.3, 1.3.1, and 1.5.4.2 directly... and automatically inherent those
"parent relationships".

I'm not sure what the most efficient way of doing this in Access? So,
before I begin design some table structures that may work but are tedious to
use (drill down to 6th level), I was wondering if someone has a good idea(s)
how to create a process that preserves data integrity and is user-friendly.
Any ideas?

EEH

 

[John Nurick]

Hi Tom,

I think the key will be a Categories table, with at least the following
fields:

Category* (Text, about 11 characters)
SortOrder (Long)

The Category field will contain a list of all the allowable categories,
e.g.
1
1.1
1.1.1
...
1.1.1.1.1.1
1.1.1.1.1.2
...
1.3.5.7.9.1
...
and SortOrder will contain values that when sorted will get the
categories to display in the order you want.

Probably you'll need six more fields to store the six levels of category
(IMO this is one of the cases where convenience outweighs
normalisation).

Then in your main table (I'll call it Comments), the fields would be

Topic (almost certainly this would be related M:1 to a Topics table)
Category (related M:1 to Categories.Category)
Comment

On the form, use a combobox for the Category field, with Autocomplete
and LimitToList turned on and its rowsource set to a query on the
Categories table. That way, entering a category will be as quick as it
is in Excel and the users won't be able to enter a category that doesn't
exist.

When you subsequently need to relate records to others higher or lower
in the same part of the "category tree", you can easily do it with a
query that (a) joins Comments to Categories on the shared Category
field, (b) does the same thing again, and (c) joins the two joins on two
of the single-category fields in Categories.

Your scheme seems to allow up to a million categories. If you actually
have more than a few thousand, the combobox may have trouble: but there
are ways round that.