Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Relationships (XP)

    <P ID="edit" class=small>(Edited by HansV on 16-Jun-03 09:24. Reduced huge screenshot in size - it caused horizontal scrolling)</P>I am attaching a screen shot of the tables in my database and their relationships. I am so bad at creating relationships. It looks right to me, but when I create a query and a form based on that query, I can't update the data. So, I thought I'd start with asking one of you to tell me if my relationships are right. The database is created to track public comment letters to a report we have submitted.One company may send one or more letters. Each letter will definitely have many comments. I need to track which letters come from which companies. What the comments are - we number them (CommentText), whether they are positive or negative (rating), which of our employees will address the issue (PUD contact), what department the comment is geared toward (Resource), and what our response is to the comment (Response).

    I am envisioning data entry on a form that has the company info at the top. Then a subform for the letter with another subform within the letter sub form to track the comments for each letter.

    I set it up that way, and it looks right, but I get an error message when It try to update comment fields. I would be thrilled if anyone wanted me to email the database to them to look at (I compacted it and it's 65kb.

    But, I would also be grateful if someone would tell me if I have set up the relationships correctly since if that's wrong, nothing else will work anyway.

    Thank you!!
    Attached Images Attached Images

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Relationships (XP)

    <P ID="edit" class=small>(Edited by HansV on 16-Jun-03 09:25. Reduced huge screenshot in size - it caused horizontal scrolling)</P>Ok. I'll try. drp tables contain the data to select from for a lookup. The result of what is selected is stored in the corresponding tbl. tbl_CommentText is where we will type the text of the comment we receive from companies on our report. One company may have multiple letters (hence the 1-many relationship between tbl_Company and tbl_letter). Each letter will definitely have many comments. Each comment may have more than one contact, more than one resource, more than one response, so I made the tbl_Comments the intermediate table. I thought that was the way to do it, but I am the first to admit that I could be way wrong on this.

    I'm attaching the form I made for data entry of the comments. Does this help you understand what I am trying to do?
    Attached Images Attached Images

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Relationships (XP)

    Hi Melanie,

    The relationships from tbl_Company to tbl_Letter to tbl_Comments are clear, and correspond to the structure of your form. But like Charlotte, I'm confused about the rest.

    For example, why do you need a separate table for CommentText? It would imply that one comment can have several comment texts, but that is not represented in your form.

    And if one comment can have several resources, you would need an intermediate table with CommentID (linked to tbl_Comments) and ResourceID (linked to tbl_Resources). There would be no ResourceID in tbl_Comments; the way it is set up now makes no sense to me. See attached screenshot. The same holds for contacts and ratings.

    One other comment. In your relationships picture, the contacts, resources, ratings and responses of a comment are completely independent of each other. It is quite possible that that is what you want, I just wanted to draw your attention to it.
    Attached Images Attached Images
    • File Type: bmp x.bmp (77.7 KB, 1 views)

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Relationships (XP)

    I'm sorry, but I don't understand why you have tbl_PUD_Contact *and* drp_PUD_Contact, tbl_Resources *and* drp_Resources, etc., especially when the field name you use in the tbl table suggests that the drp table is a lookup of some kind. If it is, then why do you need the intermediate tables. Nor is it clear what tbl_CommentText is for. If tbl_Comments is intended to contain comments about tbl_Letter and is the many side of a one-to-many relationship, then why do you need tbl_CommentText? tbl_Company, tbl_Letter and tbl_Comments seem to be in straightforward relationships, but the other tables are confusing. Perhaps if you explain the kind of data that is supposed to go into each, it will become clearer.
    Charlotte

Posting Permissions

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