Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Relationship (Access03)

    I have a form that shows up to five levels of task information. I then have a subform that has which units should have that on their credentialling listing. My problem is that it is working as a one-to-one relationship. I am not sure how to deal with this. I thought to create a relationship in the relationship window based on the underlying queries but that didn't work. What I don't want to have to do is assign units individually to all five levels. Guidance would be appreciated. I have attached the database. Thank you. Fay

  2. #2
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Relationship (Access03)

    Fay,
    Without looking at your database yet, are your fields that are linked primary keys? If so, it will enforce 1 to 1 relationships.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  3. #3
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Relationship (Access03)

    Yes. So how do I deal with this? Fay

  4. #4
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Relationship (Access03)

    Let me try again. The qryListing is based on the individual level tables and useds the primary key. The qryAttaching is based of the tblAttaching that useds foreign keys. Fay

  5. #5
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Relationship (Access03)

    Fay, I *think* I understand the problem. I'll try to word this as best as possible (I know what I want to say, but not exactly how to convey it in words), I'll tell you how I'd do this.

    Use your tables with static data as reference tables (for lookups). Create one table to hold all tasks (using the Lookup Wizard to select sections, subsections, etc...if it stops at a certain level, just omit the section), and then use queries to filter your forms, reports, etc...

    If you've been told by the WMVPs, moderators, etc that this is a bad idea, or if they suggest later that the design is bad, by all means listen to them - this is just how I would do it. I'll work on it with this setup, and post it back later. Hope this helps!
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  6. #6
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Relationship (Access03)

    I think I may understand what you are driving at. But that means the user haa to make many more selections that way.

  7. #7
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Relationship (Access03)

    Fay

    Further to what Jeremy has said. I have looked at your frmListing and fsubattaching. It appears you have multiple Child/Master linking between the pair. Open frmListing in Design view and get the properties of the fsubattaching and delete ;SubTaskID;SubTaskID in the Child/Master on the Data Tab leaving just TaskID as the link.

    In line with Jeremy's answer it is probably best to create 1 field lookup tables for department and Job Title as this is currently confusing the database to think you are adding records to these tables. The best way to do this is to create a make table query from your existing tables.

    Doing this I have been able to get the records to move with the current structure you have submitted.
    Jerry

  8. #8
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Relationship (Access03)

    I will work on it thank you. But does this mean I can only assign units to only the task level and not all the way down to subsubtask level? Fay

  9. #9
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Relationship (Access03)

    Fay,
    I'm still working on your database, I'm trying to refrain from changing your table structure/relationships. It's working out OK so far, as soon as I figure out how to make a subform requery another subform we'll be in business....
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  10. #10
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Relationship (Access03)

    Thanks I am working on the information the two of you have shared with me. I have been trying to get that requery to work also. Thanks. Fay

  11. #11
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Relationship (Access03)

    Fay,
    Check out the attached database. It filters each subform based on the active record above it (basically following the heirarchy)...the only problem I'm having right now (read as cry for help to the WMVPs, and more knowledgable than me) is carrying the same ID down (Where applicable) to the new task.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  12. #12
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Relationship (Access03)

    The attachment didn't come through. Did you zip it? If you can't zip it send to the email address on my profile. Thanks. Fay

  13. #13
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Relationship (Access03)

    Lets try again....
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  14. #14
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Relationship (Access03)

    Fay

    Whilst you wait for Jeremy's solution I have attached my take on your database. I am not a great lover of subforms within subforms but this is how I have looked at your work.

    I have created a subform called sfrmSubTask and attached another subform (sfrmsubsubform) making SubTaskID the Master/Child. This allows the user to view the subtasks of the tasks.

    Having created this I have attached it to frmListing making TaskID the Master/Child link.

    It is not the prettiest of formatting but I will leave you to clean up...Is this what you were looking for?
    Jerry

  15. #15
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Relationship (Access03)

    Also - I'm going to request to be smacked upside the head by *all* for suggesting you combine the tables....that goes against database normalization. Your setup is good, please disregard my ill-informed suggestion. Although reducing database size is probably negligible for your requirements, it's always just incorporated with good design <img src=/S/yep.gif border=0 alt=yep width=15 height=15>
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

Page 1 of 2 12 LastLast

Posting Permissions

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