Subfields

[Claren]

I am working with data that seem to require subfields. I am using Access 07.
I am analyzing features of instances of phrases I search in the Internet:

Phrase Category 1 Cat 2 Cat 3
A 5 / 50% 5 / 50% 0
X2, Y3 Z5
B

The table shows that phrase A has 10 instances, 5 in Cat 1 and 5 in Cat 2.
These instances have features marked with XYZ. Table shows that for A the
five instances of Cat 1 are broken down to 2 of X and 3 of Y, and so forth. I
would like to sort this data like: 'for A list the features that are in the
Categories that score freqencies below 10%' and 'list the Categories with
only one feature (Cat 2 in the table)'.

Can access handle this data? I think multi tables is not helpful because
they only add further info for values, not fields. I need to add further info
for fields (Cat) by setting up subfields of frequency and feature. Also, I
don't think multivalue option will help because it works only as a lookup
(can't add random numbers).

I tried using two primary keys (Phrase and Type) to correspond to each
subfield as follows:

Phrase Type Cat 1 Cat 2 Cat 3
A Freq. 5 5
A Perc. 50 50
A Feature X2, Y3 Z5
B
B
B

This may be a solution, but is quite cumbersom. Also, I am not sure if I
will be able to do the queries I mentioned above. Thank you very much for
your help.

 

[PieterLinden via AccessMonster.com]

Basic design is a table with (Phrase, Category) as the compound primary key
(unique and required). What are you really working on? I spent seven months
fixing databases where added columns until they were blue in the face. One
word of advice. Information belongs IN the table (in a column, as a value),
not ON it (as a column name).

Trust me, adding columns for each category name will cause you no end of
problems. I know, I met them every day. Simple things like does a cell with
no data in it mean a *missing* value, or that the condition does not exist?
There is a substantial difference.

If you want to create something like below, you can create a crosstab query...