Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2008

    Unanswered: help with basic database

    I have a table that is coming from Excel that looks like this (in tab delimited form):

    source target weight
    Warthog Galápagos Tortoise 0
    Ring Tailed Lemur White-tufted-ear Marmoset 0
    Komodo Dragon Meerkat 0
    Spotted Hyena Komodo Dragon 0
    Sun Bear Golden Lion Tamarin 0
    Tasmanian Devil Gray Wolf 0
    Golden Lion Tamarin Sumatran Orangutan 0
    Ring Tailed Lemur Southern Two-Toed Sloth 0
    Ring Tailed Lemur Siamang 0.......

    it goes on and on and in fact each row's relationship is duplicated - somewhere in the file there will be a line like
    Sugar Glider Golden Lion Tamarin 0
    and somewhere else a line like
    Golden Lion Tamarin Sugar Glider 0

    I only need one of the two - it decscribes ONE relationship not two. I figure this is something Access can help me do - using one table to reference both "source" and "target" and making only one entry for every relationship. Can someone guide me through how this would be done...?


  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    what I suspect is that you need to look at normalisation (sounds grnd but its a way of lloking at your data and designing the database) have a look at

    the basic aim of normalisation is to reduce duplication and enfoce data ntegrity

    so you don't store multiple row contianing (say) lemur. most databases do not recognise Lemur, lemur, LEMUR as te same, so its better to abstract such thing

    Id suggest yoiu have a table with animals (or organisms if you prefer)

    I don't understand why you would have your duplciated relationship..... it makes no sense, so you probably need to enforce some constraint (rule) which checks to make sure that you don't double define the realtionship

    the linkage doesn't make sense to me in any event.. that maybe the way you have described it.

    10 Red Tailed Lemur
    20 Ferret
    1 Three toed Sloth

    FirstOrganism 'fk to organisms Fk: FOREIGN key
    SecondOrganism 'fk to organisms
    1 10 23.5
    20 20 0

    the primary Key could be either inserting an ID column, or havng a composite key of firstorganism and second organism
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2008
    i appreciate your response but I tihnk I should carify a couple of things. First off - I am not writing the able out line by line, if I were I wouldn't duplicate entries. I know the basics of normalization and that's what I am looking to deal with. I am simply searching for a technique that would remove one of each pair of duplicates.

    Just to be certain, the weight column does not refer to one of the animals body weight, but to a number generated by differencing attributes about each one - essentially descibing proximity.

    The table was generated by an SQL which compared all entities with every other one in the list subtracting one from the other. Consequently, this leaves me with two lines for each relationship, each one with a different order of

  4. #4
    Join Date
    Jun 2008
    I only need one of the two - it decscribes ONE relationship not two.
    Sorry, I'm kinda new at this so I don't know if you want a form or query or a new table layout. Anyway...

    Is weight entry a calculated value pre-determined per source? if it is, would it help to have a table with two fields, one for the name of the animal the second column for its "attribute difference number" value.

    Then make a form (based on the table) that has a source box (text input or combo) and then a subform with a whole bunch of boxes (two columns) that has the target name (which is all your source names listed out) on the first box then an automatically calculated value for the second box. You'll prolly need to set the "if sourcename = targetname" to not show itself.

    I'm just guessing with the subform mind you, it might be okay to just put it all in a single form. Also, this would only show how one animal relates to the rest.

    the form below doesn't refresh data when you choose a new source animal, I just put it in to give an idea of what I was saying (and since I don't know how weight is calculated, I put in bogus values for each animal and just multiplied it).
    Attached Files Attached Files
    Last edited by coffeecat; 08-11-08 at 04:55.

  5. #5
    Join Date
    Apr 2008
    hi coffeecat

    i don't really understand what you are proposing but I appreciate it nevertheless...I am attaching the database that might help you understand what I have and where I want to go. There is a table in there called differenceValues which was generated by a query, one comparing every animal and its number to every other animal. The only problem I have with this table is that I have two entries for each relationship, as you will see, the source and target just swapped...
    Attached Files Attached Files

  6. #6
    Join Date
    Jun 2008
    Sorry Oompa, my version of access (2003) is unable to open your file. I suggested a form because it seemed easier to me to do it that way.

    From what you just replied, you need to re-create the query in such a way that duplicate records don't appear.

    I'm not good at making code, but the basic idea of what you want to achieve is this:

    let's give a letter for every animal, a to j (there's 10 for this example). Each animal has a value right? so it's like: (a, 1), (b, 2), (c, 3) and so forth.

    You want your query (or vba code, the access lords of these boards can enlighten us on that) to compare animal A to animals B-J, then B to animals C-J, animal D to E-J, until you get to animal I which you compare to J (at this point, you've already compared all animals to J, so J does not need to be compared). That should avoid having an A&J and J&A duplicate entry.

    I can't help with how to put that in an applicable code, sadly. Good luck!

    when you do figure out the code for it, it would be nice to see it! =D

  7. #7
    Join Date
    Apr 2008
    hi coffeecat

    sounds like you understand the problem now - here's the database downsaved just in case you are interested. I am sort of new to databases and access andit looks like in being downsaved, the querys got shouldnt matter i suppose
    Attached Files Attached Files

  8. #8
    Join Date
    Jun 2008
    Again, I don't know how to "sql"-ize this, but I see that each animal has an ID (autonumber) in the animal table. There has to be a code of some sort that you can use to look up an animal's ID and compare it only to those animals with an ID that has a value that is higher than its own.

    Oh, and that's one interesting database you have. =D

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts