Converting row data to a matrix -- possible?

  • Thread starter Thread starter Geoffrey
  • Start date Start date
G

Geoffrey

I am putting together a database of annual information
about a sample of companies (key figures, shareholders
and subsidiaries, directors and their other
directorships, etc.). If possible, I would like to
recompile some of this data and ouput it as an array or
matrix (in text file or Excel format) in order to use it
in a network analysis package.

For example:
For any given year, I have data on the ownership ties
between the firms in my sample. I want to produce a
square matrix (with the names of all the firms at the top
and down the side) and code the existence of a tie with
a "1" and the absence of a tie with a "0".

Another example:
Many directors are members of more than one board. I have
a database of directors and firms. I want to count the
number of pairs of directors who sit on the same boards
and report those counts, again, in a square matrix.

(Note: the first example will produce an asymmetric
matrix while the second will produce a symmetric one).

Complex explanation to arrive at a simple question: is
this possible and, if so, how?

Thanks!
Geoffrey
 
Yes, both are possible! You use a crosstab query for both.

In the first example, create a query and pull down the firm field twice and tie
field once. Then build a crosstab query based on the first query. You beed firm
field in the first query twice to get the firms both across the top and down the
side.

The second example is more straight forward. Your crosstab query just need to be
based on the table. Use the crosstab query wizard and follow the directions.
 
Sounds as if you (hopefully) have a normalized Access database that you want
to use to create a denormalized "matrix", which you can do with a crosstab
query.
 
Complex explanation to arrive at a simple question: is
this possible and, if so, how?
Click to expand...

A Crosstab query would get your first example easily enough. The
square matrix would be rather more difficult (especially if you have
over 255 directors) but a crosstab based on a Self Join query might be
possible.
 
Back
Top