Results 1 to 12 of 12
  1. #1
    Join Date
    Aug 2002
    Posts
    78

    Unanswered: I have two issues . . . .

    Now that I have "Normalized" my data. somethings worked better and now somethings don't work.

    I have a table that has
    DogID DogName SireID DamID (Fatehr Mother Respectively)

    1) I have related the table to itself (making it look like a family tree however it stops at the forth generation as Access says the qry is too complex. Each Generation the number of dogs double (As there are obviouisly two parents per dog) so it gets quite large. Can anyone cop me a clue as to a better way of doing this?

    2) I based a form on this query and it updates the table etc with no problem however if I go back to an empty field on a record to fill in missing information I get the error
    "Can’t enter value into blank field on ‘one’ side of outer join."

    Any clue as to what I am doing wrong????


    I hope I explained myself properly.

    thank you in advance for any help.

    t
    Attached Thumbnails Attached Thumbnails relate.jpg  

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "Any clue as to what I am doing wrong????"

    unless i misunderstand what that diagram is showing me, you have too many tables

    you only need one

    rudy
    http://rudy.ca/

  3. #3
    Join Date
    Aug 2002
    Location
    Singapore
    Posts
    91
    1. Querying all path will be complex. The query result also will not be very nice to represent the tree. Many blank fields will appear. The sire, dam, sire of sire, dam of sire, sire of dam, dam of dam, sire of sire of sire, ... and so much more... all appear in one page... of course this is very complex. If you use it for form, how big is your form if there is no limitation? I think you just need to query specific data. For example, just query the sire and dam for each dog. Or query the n-th generation. If access can't run the query because of complexity, I think you can split the query into several sub query.

    2. This query is not editable. You must edit on the table, rather on the query result. If you want to have the form based on the non updatable query, you should use insert statement (using DoCmd.RunSQL, or Open Recordset object, or Conn.execute) manually to insert to specific table.

    My idea, use query for sire and dam only, so in 1 form, we can see the dog, sire and dam. Sire and dam info should not be edited on the form (only the ID can be edited), and you just link (or have a button, or anything) so that we can go directly to the sire data (on the same form), or dam data, to enter the sire of sire... and the next level of data.
    Last edited by Djoko; 09-24-02 at 23:34.

  4. #4
    Join Date
    Aug 2002
    Posts
    78
    Rudy:

    It is the same table, just copies of it. . . is there a way to requery the same table over and over again without using so many copies?????

    thank you for your help
    Last edited by t_galownia; 09-24-02 at 23:53.

  5. #5
    Join Date
    Aug 2002
    Posts
    78

    1. Querying all path will be complex. The query result also will not be very nice to represent the tree. Many blank fields will appear. The sire, dam, sire of sire, dam of sire, sire of dam, dam of dam, sire of sire of sire, ... and so much more... all appear in one page... of course this is very complex. If you use it for form, how big is your form if there is no limitation? I think you just need to query specific data. For example, just query the sire and dam for each dog. Or query the n-th generation. If access can't run the query because of complexity, I think you can split the query into several sub query.

    (The object is for the end users to beable to insert a 5 generation pedigree all at once (As they are my "customers" I am trying to meet their needs. )

    2. This query is not editable. You must edit on the table, rather on the query result. If you want to have the form based on the non updatable query, you should use insert statement (using DoCmd.RunSQL, or Open Recordset object, or Conn.execute) manually to insert to specific table.

    (Editing the table is really not too much an option as the users need something beyond foolproof. That is why I need to use the same form and be able to add the missing information. Currently when filling out the initial record it creates 63 records in the Dogs table as it should) However when going back to the form there are wholes in all the pedigrees except the first one (Which is the way it should be also) So I am needing to allow them to fill in the missing blanks when they go to the other records created when filling in and updating the missing information on the form for the other records. Did that make sense????

    My idea, use query for sire and dam only, so in 1 form, we can see the dog, sire and dam. Sire and dam info should not be edited on the form (only the ID can be edited), and you just link (or have a button, or anything) so that we can go directly to the sire data (on the same form), or dam data, to enter the sire of sire... and the next level of data.

    (This part kind of confused me. In understand about separating the sire and dam forms but editing the missing information will still be a result as the sire and dams never stop. What am I missing please.

    On top of that I will ultimately need a 5 Gen query that works as many of them want that. Some even go as far back as 7 and 10 (Which would be nice but that is down the road. Thank you for your help and input.

    As an FYI I am going to take classes as soon as next quarter starts. . . I know enough to be dangerous but not enough to correct my issues. I can usually (Mind you usually) understand code looking at it as it is somewhat logical but at this time I am unable to come up with it on my own. So I really appreciate All of everyone's help in the interim. . . I am learning alot.
    Last edited by t_galownia; 09-24-02 at 23:55.

  6. #6
    Join Date
    Aug 2002
    Posts
    78
    Originally posted by t_galownia
    Rudy:

    It is the same table, just copies of it. . . is there a way to requery the same table over and over again without all the copies?

    thank you for your help

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    > It is the same table, just copies of it.

    yikes

    > is there a way to requery the same table over and over again
    > without all the copies?

    certainly

    Code:
    select Dog.DogID
         , Dog.DogName
         , Dog.SireID
         , Sire.DogName as SireName
         , Dog.DamID
         , Dam.DogName as DamName
      from Dogs Dog 
    left outer 
      join Dogs Sire
        on Dog.SireID = Sire.DogID
    left outer 
      join Dogs Dam
        on Dog.DamID = Dam.DogID
    this query joins the table to itself twice, once for the dog's sire and once for the dog's dam

    notice it uses left outer joins, in case the sire id or dam id is null

    i would recommend you do not try to extend beyond one level -- always just the dog and its parents

    i do not know access forms all that well, nor how to tie them to update or insert statements, so i'm not really sure how you're going to populate the data into the table, but i will veture a guess that if you restrict yourself to just the dog and its parents, you'll have a much easier time

    the procedure is to add the parents first, so that their ids are in the table, then add the dog and set its sireid and damid to the appropriate parent dogs

    helps?

    rudy

  8. #8
    Join Date
    Aug 2002
    Posts
    78
    I really appreciate your help and time. I owe you

    Helps? A little.

    Actually, what I need is backwards, As the form looks alot like the query structure above (Actual family tree layout) that is how I need the data entered. So it actually starts with the dog and not the parents.

    When the form is submitted it puts the records in the table and relates thier Sire/dam ID. (How I did it I will never know LOL) If a sire is a sire more than once currently it gets added more than once but that is another issue all together I will address later as I am sure that is more simple coding than getting the form to work. The way I need it. Like I said the query stops at the forth generation but five is needed and optimally (Infinate but will be happy with five as that is what the end users need.) If you look in Design view on the query and look at the SQL (View, SQL View) view you will see obviously how I messed it up. (Well . . it works kind of.

    I will upload what I have (as it is rather small and you can see what I mean. I have Access 2000. I hope it clears up my dilema.

    I need to be able to fill in the holes in data when they occur as with any family tree not all information is available all at once.

    As I let the other person who responded know,

    "As an FYI I am going to take classes as soon as next quarter starts. . . I know enough to be dangerous but not enough to correct my issues. ::::sung to the tune of the hallelujah chorus:::: (UUUUUUUUNNNNNNNNNNNDERSTATEMENT)
    I can usually (Mind you usually) understand code looking at it as it is somewhat logical but at this time I am unable to come up with it on my own." I really do appreciate the time and effort. I am learning alot.
    Attached Files Attached Files
    Last edited by t_galownia; 09-25-02 at 02:01.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry, i can only read access 97

    if you add the dog first and then its parents, there have to be several statements tied to the form -- insert dog leaving sireid and damid null, insert parents one at a time, and finally update dog setting sireid and damid to the values issued to the parent rows, which you have to retrieve somehow

    frankly, i don't see how a form can do that, but like i said, i don't do access forms, maybe there's all kinds of neat stuff like a series of procedures it can run

    also, you said "If a sire is a sire more than once currently it gets added more than once" and that would be a serious problem unless you use dropdown lists for sires and dams

    anyhow, i can understand your comment about "what the end users need" but i urge you to consider the difference between what they want and what they need, and they probably don't want a slow, cumbersome system with the possibility of data integrity errors

    i'm afraid i won't be able to assist you any further down the road you have chosen

    good luck

    rudy

  10. #10
    Join Date
    Jan 2002
    Location
    UK
    Posts
    67
    Hi t_galownia,

    The way your query diagram looks, you have made ti quite complex by using so many copies of the same table time and agin, you should not be needing to use them in such complexitiy.

    I had seen your earlier query as well where you went in for the data normalization.

    In fact I am unable to under stand why the users want to start with the children not with the parents, If it is the other way round then you can probably use the drop down menus for your forms.

    As far as the things are concerned, it will save your life.

  11. #11
    Join Date
    Aug 2002
    Posts
    78
    Originally posted by r937
    sorry, i can only read access 97

    if you add the dog first and then its parents, there have to be several statements tied to the form -- insert dog leaving sireid and damid null, insert parents one at a time, and finally update dog setting sireid and damid to the values issued to the parent rows, which you have to retrieve somehow

    frankly, i don't see how a form can do that, but like i said, i don't do access forms, maybe there's all kinds of neat stuff like a series of procedures it can run

    also, you said "If a sire is a sire more than once currently it gets added more than once" and that would be a serious problem unless you use dropdown lists for sires and dams

    anyhow, i can understand your comment about "what the end users need" but i urge you to consider the difference between what they want and what they need, and they probably don't want a slow, cumbersome system with the possibility of data integrity errors

    i'm afraid i won't be able to assist you any further down the road you have chosen

    good luck

    rudy
    Once Again I thank you for your time and effort. It is muchly appreciated. I have included the Db in Zip Access 97 form should you care to see it. with the way I have this query set up in the form it actually adds them to the dog table correctly. ???? (Again how I dont' know as I am planning on taking class next time it starts (inclusive of design, sql etc. When I grow up I want to be just like y'all LOL) It actually works adding the dogs as is.

    Anyway should you care to look at it and if it sets some ideas off they would be greatly appreciated. If not again I really appreciate your help and time. You have been a big help to me so possibly back to drawing board again I go LOL

    t
    Attached Files Attached Files

  12. #12
    Join Date
    Aug 2002
    Posts
    78
    Originally posted by xzone
    Hi t_galownia,

    The way your query diagram looks, you have made ti quite complex by using so many copies of the same table time and agin, you should not be needing to use them in such complexitiy.

    I had seen your earlier query as well where you went in for the data normalization.

    In fact I am unable to under stand why the users want to start with the children not with the parents, If it is the other way round then you can probably use the drop down menus for your forms.

    As far as the things are concerned, it will save your life.
    With coding (in my fourth revamp before I found out about normalizing data) another person was able to help me use dropdown boxes so it works starting with the children first. As for the reasons end users want to start with children? As you can see in my "no so good working qry set up". . .it is set just like a family tree. . . That is the way the breeders have been doing it for as long as anyone can remember . . so unfortunately that is what I have to work with. I could opt to do just one set of parents for data input (as was an option given to me before) and query for output with five (or more) generations however it is the way it has been done for goodness knows how long. So you can see the dilema I am in. thank you for your time and all. . .I will take all in consideration. I zipped the file (which is only approx 144k unzipped) for r937 in Access 97 format in the post just above this so should you care to look at it it might make a lil more sense. if not that is cool.

    You help, advice etc is muchly appreciated. thank you for taking your time to offer your advice etc.

    t

Posting Permissions

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