Calculated fields for subgroups

[shysong]

Hi everyone,

I hope someone can help me with this data transformation problem,
because I'm working with a big enough dataset that I really don't want
to do this by hand (I have neither the time nor the finances). I am
using Access 2003. I have an Access table that looks like this
(n=231):

subjid jobtitle site dur1 to dur243 freqwk1 to freqwk243
1 A A 30... 15 5...10
2 B A
3 C A
4 A B
5 B B
6 C B

In other words, this is a dataset holding the survey responses of 231
participants, in which they completed information about the duration
and frequency with which they performed 243 different tasks; each task
duration and each task frequency is listed as a separate field.

For theoretically driven reasons too long to go into here, rather than
use the actual reported values, I want to use the modal values reported
by each job title at each site as the value for frequency and duration
for each task. In other words, if the mode for field dur1 for
subjects in jobtitle A, site A is say 20, I want to replace their
existing values for field dur 1 with 20. Ditto for all other
combinations (i.e., jobtitle B site A; job title A site B; etc.) I
have 7 job titles and 7 sites. So I want to create 483 new fields that
would hold the mode values as described above.

How would I go about doing this? If Excel is a better choice for doing
this, and someone knows how to do this in Excel, that's fine too.
Either way will work for me.


Thanks in advance,

Sylvia J. Hysong

 

[John Vinson]

Hi everyone,

I hope someone can help me with this data transformation problem,
because I'm working with a big enough dataset that I really don't want
to do this by hand (I have neither the time nor the finances). I am
using Access 2003. I have an Access table that looks like this
(n=231):

subjid jobtitle site dur1 to dur243 freqwk1 to freqwk243
1 A A 30... 15 5...10
2 B A
3 C A
4 A B
5 B B
6 C B

In other words, this is a dataset holding the survey responses of 231
participants, in which they completed information about the duration
and frequency with which they performed 243 different tasks; each task
duration and each task frequency is listed as a separate field.

Well... you CANNOT store this dataset in an Access/JET table, which is
limited to 255 fields (an absurdly wide table IMO).

A MUCH better design would have two tables:

Tasks
TaskID ' 1 to 243
<descriptive info about the task>

Participants
SubjID ' 1 to 231
<bio information about the participant>

Results
SubjID ' link to Participants
TaskID ' what task is being evaluated
Duration ' duration for this task by this subject
Frequency ' frequency of this task by this subject

"Fields are expensive - records are cheap"! Build your table tall and
thin, then you'll have just ONE field (in the Results table) to which
you can apply whatever data transformations you wish.

John W. Vinson[MVP]

 

[shysong]

Thanks for the reply -- I do have the database set up in that way as
well ( tall and thin -- doing different analyses that require different
data setups) -- but I still don't know how to actually do the
transformation I need -- which is to calculate a conditional mode
(i.e., the mode of a subgroup of records, and then have that value be
the same for all records in that subgroup only. -- then repeat for all
of my subgroups). Any thoughts?

 

[Joseph Meehan]

Thanks for the reply -- I do have the database set up in that way as
well ( tall and thin -- doing different analyses that require
different data setups) -- but I still don't know how to actually do
the transformation I need -- which is to calculate a conditional mode
(i.e., the mode of a subgroup of records, and then have that value be
the same for all records in that subgroup only. -- then repeat for all
of my subgroups). Any thoughts?

For what you are doing Excel may be the better tool.

With out trying to go back to my statistical courses to remember if I am
right, you may be able to use queries, reports or forms to do what you want.
However I suspect you may be better off with Excel.