presence/absense matrix

[E.Hobson]

I am a graduate student in biology, and am trying to figure out a way for
Access to make a matrix of association patterns for me. I am studying the
social interactions of a population of marked birds - each bird has a metal
band with a 3-digit code to identify it, which becomes BirdID in my database.

I made a cross-tab query that compiles my raw data so that Date, Time, and
NestID are Row Headings, and BirdID is the Column Heading and number of birds
are summed as the Value.

ex:
Date NestID Time 007 008 009
010 011
14-Jul-07 N08 14:37 1
1 1
14-Jul-07 N08 14:43 1 1
1

Etc. So at Time 14:37, birds 007, 009, and 011 were interacting, while at
14:43, 007, 008, and 009 were interacting. I am trying to figure out how to
get this data into a symmetric square matrix so that access will return a
count of the number of times that individual birds interact with each other.

For the matrix I'm trying to get it so that BirdID is along the top & left
side, with total interactions summed for each combination of birds. It
should look like this:

007 008 009 010
007 0 1 2 0
008 0 0 1 0
009 2 1 0 0
010 0 0 0 0

I tried the iif function for the first time today, but it wasn't returning
the data in quite the way that I wanted. Anyone know a way to get Access to
do a matrix like this for me? Thank you very much for the help!

 

[John Spencer]

What does your table look like?

Do you have one record that shows information in a format like the
following or is the data in some other format?
BirdNo
NestID
VisitDateTime



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[E.Hobson]

Hi John,

My raw data table has many columns for all the information I took, but does
include:

BirdID
NestID
Time of observation
Date of observation
Type of behavior observed

I have a separate record for every marked bird I observed. I observed
multiple nests at the same time, and made an observation approximately every
2 minutes of all marked individuals. I ran the cross tab so that I could see
a summary of which birds were interacting with each other by date, time, and
nest. I thought maybe going from a cross tab query where the interaction
data was summarized (i.e. a "1" for each date/time/nest when each particular
individual was recorded) might make it easier to translate the data into a
matrix of interactions, but let me know if there is something else that might
work.

 

[John Spencer]

SELECT Ta.[Date of Observation]
, Ta.[Time of Observation]
, Ta.BirdID
, Tb.BirdID as BirdID2
FROM YourTable as TA INNER JOIN YourTable as TB
ON TA.NestID = Tb.NestID
AND Ta.[Date of Observation] = Tb.[Date of Observation]
And Ta.[Time of Observation'= Tb.[Time of Observation
WHERE Ta.BirdId <> Tb.BirdID

That should give you a list of every pairing which you can use as a the
source for a crosstab. The crosstab should look something like the
following (UNTESTED) SQL statement

TRANSFORM Count([Date of Observation]) as TheCount
SELECT [Date of Observation]
, [Time of Observation]
, BirdID
FROM TheSavedQuery
GROUP BY [Date of Observation]
, [Time of Observation]
, BirdID
PIVOT BirdID2

If you need the zeroes then change the first line to


TRANSFORM Clng(NZ(Count([Date of Observation]))) as TheCount


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[E.Hobson]

John, thank you so much! You have saved me hours of tedious data entry and
potential data entry mistakes. I knew there was some way to get Access to do
this for me - thanks for the wonderful advice!