[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index][Subject Index][Author Index]

Re: Rank taxonomy (was Re: Cladism (akin to masochism))



If you have no interest in databasesand/or MS Access, just ignore this thread...


>
>   Looks like I'll adopt this way of doing things, although I'm probably not
>going to use node numbers (IF I can get MS-Access to set up a cascading
>referential integrity constraint between two fields in the same table).
>Thanks.
>


You can do this by creating an alias of the main table (I call mine
Dinosaurs) in the relationships window. To do this, just add the table to
the relationships window twice. The second one will be called Dinosaurs_1.
Then just drag the field you want to relate from Dinosaurs to Dinosaurs_1
and set referental integrity to "One to Many". In my example you would
relate "Node No" to "Parent No". For this to work you have to put NULL in
the root node - not zero.

I Presume the reason to do this is so you can relate a text field. You don't
necesserily need to do this. If you create a form, you can use a combo-box
to enter the parent node as text. There is a wizard which makes this very
easy to set up. Select Name and Node as the fields to appear in the combo
(make sure Name is first, as this is the one you will type). Pick Node as
the field to relate and Parent as the field to be relate to. The nice part
is that as you type the combo box will select the closest match, so that you
often don't have to more than a few letters (although many clade names have
the annoying habit ob being the same up to the last few letters).

This gives you the advantage of having neat (and compact) relationships
without the hassle of having to match numbers.


>
>   The problem now becomes pulling all the clades for a single animal from
>top to bottom.  Recursive joins of unknown number can't be done in SQL.  May
>have to write a C program to do it (ugh).
>


If you're talking of using Access, this is dead easy to do in Access Basic
(and most other database engines offer some form of macro language or stored
procedures). Here's something I knocked up:

Function ListClades (nNode As Long) As String

   Dim dbDino As Database
   Dim rsClade As Recordset

   Set dbDino = DBEngine.Workspaces(0).Databases(0)
   Set rsClade = dbDino.OpenRecordset("Select * From Dinosaurs Where 
[Node Ref]=" & nNode)
   If IsNull(rsClade("Parent Ref")) Then
      ListClades = rsClade("Name")
   Else
      ListClades = ListClades((rsClade("Parent Ref"))) & " " & rsClade("Name")
   End If
   rsClade.Close

End Function

(watch the split line in the middle). Note I've changed "No" to "Ref" in my
field names.

Note that this is a bit slow. To speed it up you could keep a global
recordset object open and search that, rather than executing a query at
every level.

The nice bit is that this function can be called in a query. Here's an
example of a query which lists and classifys all species in the database.
Note that I've added a boolean field "Species" to mark leaf nodes.

SELECT DISTINCTROW Dinosaurs.Name, ListClades([Node Ref]) AS Dinosaur
FROM Dinosaurs
WHERE ((Dinosaurs.Species=Yes));


What I really want to do is write a program (probably in VB) to draw
Cladiograms from the database. Right now I don't have time, but I am working
on something not dissimilar in another project. When I get that going I may
try to adapt.

If anyone wants my tinkerings (in the for of an Access Database) in
preparing this message, you're welcome to them. I could FTP it somewhere. If
anyone has some spare time and a book full of cladistics, I'm sure a few
other people on this list would be interested in a copy of the resulting
database...

Sorry if this message has gone on a bit. Hope it's of interest to someone.

James Shields  -  jshields@iol.ie  -  http://www.iol.ie/~jshields
-------------------------------------------------------------------------------
And when the ark was finished Noah said unto Elvis, "What do you reckin?"
And Elvis checked out his own cabin and shook his head saying "poky".
And so did they knock several walls through and install a jaccuzzi.
And when it was all done Noah scratched his beard and said, "We don't have
room for all the animals now."
And Elvis perused the livestock list and in his wisdom said, "Lose the
dinosaurs."
        -Robert Rankin, The Suburban Book of the Dead