Page 3 of 4 FirstFirst 1234 LastLast
Results 31 to 45 of 57
  1. #31
    bbilotta is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Oct 2013
    Posts
    60
    yes, all very true. I know for me, two years ago i didn't really have a clue as to the full scope of what to consider. i kind of knew the final product to be, though to be honest, i really didn't. after working with it (AGAIN) and now having a much better understanding of how SQL and Access work (albeit still rudimentary), i know better what questions to ask and answer. Still have to do quite abit of hunt-and-peck, but getting there.

  2. #32
    bbilotta is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Oct 2013
    Posts
    60
    Here's a design question that's been bugging me for a while now. How do you handle spouses?
    I sometimes have a John and Jane Doe listed as owners of a dog.
    In our catalog we list the competing dogs along with their relative information such as DOB, Gender, AKC Registration #, Sire, Dam, Owner(s), Breeder(s), and the Handler. I would like to list John & Jane Doe as the owners and as one unit. However, often one of those two will be the Handler and needs to be listed alone.

    We also list "Owners of Competing Spaniels" in our catalog so i need both names. I would prefer to list them again as one unit - "John & Jane Doe" followed by their address as opposed to two separate listings of John Doe, Address and Jane Doe, address.

    I suspect i'm getting into an area where i'll need VBA, and that it may be a wish more than a need. I thought i might include a Spouse check box. if checked you need to link them to a spouse. But I haven't even begun to figure out the reports in access and i'm not sure how to tell the report to handle it. i'm guessing i could build the criteria in the query using an iif statement.

    This is not a major design issue at this point, just something that's been stuck in my head and i thought i'd throw it out there for an opinion.

  3. #33
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Your term handler triggered an old model I helped someone with. Now it was related to Dogs, Handlers and Agencies and I found the jpg. It isn't part of your model, but it may be useful. I see it was from Aug 2015. It shows a Dog may have many Handlers, and a Handler may "handle" Many Dogs.

    All this to say I don't have any immediate insight into how it should be done. I do think it would/could take some vba and would probably mock it up as a separate analysis/test thing. This may be one of those trial and error (black box) things.

    Update: Just searched and found the original post
    https://www.accessforums.net/showthr...handler+agency
    Click image for larger version. 

Name:	DogsHandlersAgencies.jpg 
Views:	80 
Size:	81.9 KB 
ID:	27904

  4. #34
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    How are things going? Haven't seen any updates.
    As for Dog, Sire and Dam, I just tried a small test of this general structure for hierarchy.

    Click image for larger version. 

Name:	DogSireDamTbl.jpg 
Views:	74 
Size:	38.5 KB 
ID:	27943

    With some sample records
    DogId Gender Sire Dam DogName
    2 m 0 -9 BillsGem
    3 f -9 -9 SassyLady
    4 m -9 -9 JackSprat
    5 m 2 8 Bullit
    6 f -9 -9 QueenAnne
    7 f 10 3 Princess
    8 f -9 0 AuntSadie
    9 m -9 -9 OlYeller
    10 m 4 3 Rufus
    11 m 4 8 Jedidiah
    12 f -9 -9 SarahQ

    And a query
    Code:
    SELECT DogTest.DogId, DogTest.Gender, DogTest.Sire, DogTest.Dam, DogTest.DogName
    , M.dogname AS Mother, F.DogName AS Father
    FROM (DogTest INNER JOIN dogtest AS M ON DogTest.dam = M.dogid) 
    INNER JOIN DogTest AS F ON DogTest.Sire = F.DogId
    WHERE (((DogTest.Sire) Not In (0,-9)) AND
     ((DogTest.Dam) Not In (0,-9)))
    To give result

    Code:
    DogId Gender Sire Dam DogName Mother Father
    5 m 2 8 Bullit AuntSadie BillsGem
    7 f 10 3 Princess SassyLady Rufus
    10 m 4 3 Rufus SassyLady JackSprat
    11 m 4 8 Jedidiah AuntSadie JackSprat
    The concept is that this hierarchy is all in one table.
    For each Dog you record the DogID of the Sire in field Sire and the DogId of Dam in Dam.
    At the top of the hierarchy--the top dog the sire will be 0, the dam will be 0. Value is arbitrary, but I wanted to show the top dog (furthest back ancestor) and used a -9 to indicate not known or to be processed.

    This is similar to 2 self joins on the table

    Dog,(Dog is Sire), (Dog is Dam).

  5. #35
    bbilotta is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Oct 2013
    Posts
    60
    Orange, We had our spring field trial this weekend. i was off line for a few days there getting ready and running it. I finished up the paperwork on it yesterday and will be back on it today or tomorrow.

  6. #36
    bbilotta is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Oct 2013
    Posts
    60
    So i'm looking at your work. i currently have two self joins in the dog table for Sire and Dam. but if i'm reading this correctly, what you've set up here is a pedigree, or the structure that allows one to print a pedigree.

    see pic. TillyComet pups.pdf

    Correct me if i'm wrong, but your SQL would order many generations of dogs (assuming you had many generations of dogs). Does it work this way no matter what the numerical value of the Sire or Dam is? They are not in any sequential order.

  7. #37
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Yes it should. The concept is you have a "dog" table that uniquely identifies a dog. You also record that dog's Sire and Dam in that dog record.. You use the DogID of the Sire in the Sire field of the "dog". And similarly with the Dam of the "dog" you put the Dam's "DogId" in the Dam field of the "dog". Since there is one Sire and one Dam per "dog", you have a hierarchy/pedigree. The use of some special character (or 0) for the Sire and Dam at the top of the hierarchy. With some formatting, you could identify and display a hierarchy.

    I haven't done any testing, and I don't know your business facts, but the same sort of approach may work for other entities. If you consider DogPeople, these are People and some could be spouse/partner of another. So to keep your "pairs together" you might consider/test

    PersonId
    PersonFirstName
    PersonLastName
    SpousePartner <---and this would be the PersonId of the "spouse/partner"
    OtherPersonAttributes....

    I just did a search for "printing a hierarchy from a self join table" and I got this article by Allen Browne.

    Note in general:
    If you have a multilevel hierarchy/pedigree, then use LEFT JOIN rather than INNER JOIN shown in post 34 to show the full hierarchical structure.
    Last edited by orange; 03-21-2017 at 08:02 PM. Reason: additional info

  8. #38
    bbilotta is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Oct 2013
    Posts
    60
    Interesting. i tried something very similar to the allen browne solve you posted, but i ran into problems. i believe the problems are due to the ConcatRelated titles. When I tried to enforce referential integrity between the SireID and "Sire" table when using the ConcatRelated titles I got errors.

  9. #39
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    As I mentioned before you might want to leave ConcatRelated out of the picture until you're ready to display data. Make sure your tables and relationships support your facts. ConcatRelated is based on a function that can called/used when needed.

  10. #40
    bbilotta is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Oct 2013
    Posts
    60
    I'll try to do that. I found a work around by simply not setting up the entire query and only calling the ConcatRelated on the form when i needed to present the Dog, Sire, and Dam, which is probably what you're alluding to.

  11. #41
    bbilotta is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Oct 2013
    Posts
    60
    Just an update:

    I've begun building forms. so far so good. a few minor alterations, but working well so far.
    I did make one structural change. I have a junction table called ClubMemberRoleTbl and in it i had the FKs ClubID, PeopleID, and ClubRoleID. I'm already assigning certain people a ClubMemberID, and to do that i'm using a foreign key for PeopleID in the ClubMemberTbl. I want to limit the list in ClubMemberRoleTbl so i removed the FK PeopleID from ClubMemberRoleTbl and replaced it with ClubMemberID. Additionally i want to know who the President was for 2015 (for instance), so I've added the FK DuesID from the ClubDuesTbl. I did this because this gives me a single row that contains the ClubID and Year and then associates the Club Member with a Club, a Year, and a Role. Now i can run a report for a given year, club and member and know what role that member played. Or i can run a report on a club for a year and list the members and their roles. This also allows me to remove the FK ClubID from the ClubMemberRoleTbl as i'm already capturing the ClubID from the ClubDuesTbl.DuesID

    I'm not sure all that makes sense to you as you read it, but all in all i have no new issues today, LOL - A good day.

  12. #42
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You're working on a test database, right? It's easier for me to look at your tables and relationships when looking at your question. Do you have a current version of these you can post?

  13. #43
    bbilotta is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Oct 2013
    Posts
    60
    DogRebuild3.zipDogRebuild3.zipIt's a perpetual "test" db, LOL.

  14. #44
    bbilotta is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Oct 2013
    Posts
    60
    i still have to clean up some formatting, but this is what i ended up with so far on the Club Member Form
    Click image for larger version. 

Name:	ClubMemberForm.jpg 
Views:	70 
Size:	83.1 KB 
ID:	27968
    I did a little more work after sending you that previous file. it won't contain this form shown above. this one does DogRebuild3 (2).zip

    I would like to figure out how to get the Member name in the subform to auto populate instead of having to select it, but that's a VERY minor item and can probably be done by adding an event action to the Member name cbo on the main form.

    if you open the form and look at it in design you'll see i've included the DuesID on that subform and hidden it from view.

  15. #45
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You really want to have a spot where you can test/try some things without messing up your work to date.
    I agree that EffectiveDate is a required field in DCClubMember since the Role, Membership have an effective /active period. Again these are facts that come out during your what-if scenarios and will/could affect your underlying data model.
    Identify and test your scenarios, when you get it the way you want/need--save a copy and label it. I would not get too deeply involved in finalizing forms or reports. Better to have a mock up of what the form should do and look like without a lot of detailed code behind the form (at this point). You may want to identify test data and a test plan so you can test/check what you have.
    You seem to be doing well--good luck.

    NOTE: I received your latest post after submitting my response. I don't use macros so can not comment on the event code etc behind form(s)/Control(s). Just keep your "tested work" from time to time so you don't lose what you have done. I would not use 500 member records for test.
    You might consider a Salutation field in your member info --Dr. is not part of FirstName in my view, but you know your audience better than readers.

    Are you working with macros for a specific reason?

Page 3 of 4 FirstFirst 1234 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 11-04-2015, 07:25 AM
  2. How to filter bound subform from unbound main form?
    By ittechguy in forum Programming
    Replies: 3
    Last Post: 10-25-2015, 09:12 PM
  3. Replies: 1
    Last Post: 01-16-2015, 09:28 AM
  4. Replies: 6
    Last Post: 11-30-2013, 02:41 PM
  5. Replies: 2
    Last Post: 08-01-2011, 11:35 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums