Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2009
    Posts
    223

    Unanswered: Crying over my Relationships

    Hi,

    I am trying to make sure my Database has the correct Relationships design before I proceed further?
    It would be great if any of you would please review the attached screenshot of my proposed Relationships?

    I am still a novice with these Relationships, so please don’t hesitate to mention if anything basic is missing, if necessary.

    So, based on the attached Relationships layout, please could any of you advise if it would it be EASY and POSSIBLE to produce the Forms and Reports as mentioned below (I will be creating many more different reports than these). Would I need to add any more Foreign Keys to any of the Tables?

    Thank you all in advance.


    1. Contract Form (to include the following fields)

    Contract ID (Autonumber)
    Property ID (ComboBox choice)
    Property Address (automatically populated)
    Renter ID (ComboBox choice)
    Owner ID (automatically populated)
    Owner Name (automatically populated)


    2. Rental Income Form (to include the following fields)

    Rental Income ID (Autonumber)
    Amount Received (user input)
    Date Received (user input)
    Contract ID (ComboBox choice)
    Renter ID (automatically populated)
    Renter Name (automatically populated)
    Property ID (automatically populated)
    Property Address (automatically populated)
    Owner ID (automatically populated)
    Owner Name (automatically populated)


    3. Monthly Rental Income Report – by Owners (to include the following)
    Reported on a line-by-line basis

    Owner ID and Owner Name
    Property ID and Property Address
    Renter ID and Renter Name
    Contract ID
    Amount Received
    Date Received
    Total Amount Received

    4. Tricky one??
    Rental Income Received per Owner ID.
    (even though there is no Owner ID in the Rental Income table)?
    Attached Thumbnails Attached Thumbnails Relationships 1.jpg  
    What would you attempt to do if you knew you would not fail?

  2. #2
    Join Date
    Dec 2001
    Posts
    79
    I've reviewed the relationships and tables and I don't see a problem. With regard to #4 above, create a join query that brings together your Owner, Property, Contract and Rental Income tables. Then the OwnerID and Rental Income will be together in the same query.
    Peter De Baets
    Peter's Software - Microsoft Access Tools for Developers
    http://www.peterssoftware.com

  3. #3
    Join Date
    Nov 2009
    Posts
    223
    I THINK I GOT IT? I THINK I GOT IT? I THINK I GOT IT? YIPPEEE??

    Is the code below good format?

    With the way my Reationships have been linked, I think I can get AND Field from ANY Table by usinbg a Query??? The code below is to show how much a Renter has paid Rental Income.

    I am so happy if this is correct?

    Code:
    SELECT [Rental Income Table].[Amount Received], [Renter Table].[Renter ID]
    FROM [Renter Table] INNER JOIN ([Contract Table] INNER JOIN [Rental Income Table] ON [Contract Table].[Contract ID]=[Rental Income Table].[Contract ID]) ON [Renter Table].[Renter ID]=[Contract Table].[Renter ID];
    What would you attempt to do if you knew you would not fail?

  4. #4
    Join Date
    Nov 2009
    Posts
    223
    Peter,

    Thanks for your comments above. I think maybe it works by using a Query as described in my last post?

    Cheers.
    What would you attempt to do if you knew you would not fail?

  5. #5
    Join Date
    Nov 2009
    Posts
    223
    Guys,

    I know this may be a simple question, but is it possible a couple of you could take a quick look at my relationshiop structure to see if it is OK?

    I guess the advantage of such a structure is that we can include related Fields on Forms which have been calculated using a Query?

    And that we can create Reports using any related Fields from any Table?

    Thanks in advance.

    Paul
    What would you attempt to do if you knew you would not fail?

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    best way of validating your model is to slam in some test data and make certain the nodel meets yourrequirements
    ie you can extract the data you need in the manner you need.

    so rather than ask others to validate your model I'd suggest you attempt to see if the design word as is
    if there are other elements that you need to cater for
    if the models hangs together
    ie it meets the rules of normalisation
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Nov 2009
    Posts
    223
    Yup, I tried with test data and it worked OK.

    I tried to get the "Amount Recd" and "Renter ID" (look at the screen dump to verify in a Query.

    However, it seems the folowing code is very long, and probably inefficient and I thought there may be a better way??

    Code:
    SELECT [Rental Income Table].[Amount Received], [Renter Table].[Renter ID]
    FROM [Renter Table] INNER JOIN ([Contract Table] INNER JOIN [Rental Income Table] ON [Contract Table].[Contract ID]=[Rental Income Table].[Contract ID]) ON [Renter Table].[Renter ID]=[Contract Table].[Renter ID];
    The 5 tables seem to be all linked together somehow? I presume that means I can mix and match any Field from any Table because of the Relationships?

    I think I was asking for confirmation from others because if the above is true then this is fantastic and think I have FINISHED my design.
    What would you attempt to do if you knew you would not fail?

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Code:
    SELECT [Rental Income Table].[Amount Received], [Renter Table].[Renter ID]
    FROM [Renter Table]
    INNER JOIN ([Contract Table]
    INNER JOIN [Rental Income Table]
    ON [Contract Table].[Contract ID]=[Rental Income Table].[Contract ID])
    ON [Renter Table].[Renter ID]=[Contract Table].[Renter ID];
    ....However, it seems the folowing code is very long, and probably inefficient and I thought there may be a better way??
    what makes you think its inefficient?
    does it take significant amounts of time to execute
    whether the SQL is long or short doesn't matter. what does matter is that its suitable for the job in hand.

    I doubt you will be able to judge if the query is 'efficient' or 'inefficient' based on small samples of test data.
    what doe smatter is can you get the pieces of data glued to gether usign SQL as and when required

    I think I was asking for confirmation from others because if the above is true then this is fantastic and think I have FINISHED my design.
    ultimately its your design
    only you know whether this design meets your requirements
    only you know if you have finished your design

    if someone else skims over your design and says its ok, that doesn't mean very much unless that person knows the full requirements of the project. the risk is that you accept their word for it, even assuming they are up to speed with the requirement and you don't take full ownership of the design, its limits and so on then you dont fully understand what is going on.
    Last edited by healdem; 09-20-11 at 06:56.
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Nov 2009
    Posts
    223
    yup, i agree that i am hesitant to take full responsibility for my own design....maybe that is coz i am still relatively new to building a database that will be extremely important for my business?

    from my knowledge and experience, i think the table designs and relationships are 99% finalised. i plan to start on my Reports next.

    thanx for your input
    What would you attempt to do if you knew you would not fail?

Posting Permissions

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