Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Table relationships (2002)

    I have some questions in regards to setting up table relationships and querying the data once the relationships are defined.

    I have two tables with the following columns and data

    tblProperty

    ID Adrs ExtrRating_ID IntrRating_ID GnrlRating_ID
    1 123 Main 1 2 3
    2 456 Main 3 4 4

    tblRating

    ID Dscrptn
    1 Excellent
    2 Good
    3 Fair
    4 Undetermined


    The Rating_ID columns in tblProperty are foreign keys to the tblRating table (ID column), which serves as a lookup table. I

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

    Re: Table relationships (2002)

    You would have to link the lookup table 3 times (3 instances of the same table added to the query grid), once to each on your rating ID fields with outer joins from tblProperty to the instances of tblRating, but why do this in a query?
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table relationships (2002)

    I'll want to display data in the forms and reports without the ID's.

    Adrs Extr_Dscrptn Intr_Dscrptn
    123 Main Excellent Good
    456 Main Fair Undetermined

    I may want to use different descriptions down the road, which is the reason that I went with this design. You asked "why do this in a query?". Is there a better solution? I'd be open to any suggestions you might have.

    Another question with the current data model, would I have to add multiple instances of the table to the relationships in order to enforce referential integrity?

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table relationships (2002)

    Another method would be to use the choose funtion.
    Put the three fields in the detail section of the report.
    Make Extr_Dscrptn and Intr_Dscrptn invisible.
    Create two unbounded textboxes.
    In the control source use for the first
    = Choose([Extr_Dscrptn], "Excellent", "Good", "Fair", "Undetermined")
    and for the second
    = Choose([Intr_Dscrptn] , "Excellent", "Good", "Fair", "Undetermined")
    This have for consequence that if you change your description in the table tblRating, you will have to change the code.

    You could also use the DLookUp function.
    For the first unbound textbox use for the control source
    =DLookUp("dscrptn";"tblRating";"[ID] =" & [Extr_Dscrptn])
    and for the second :
    =DLookUp("dscrptn";"tblRating";"[ID] =" & [Intr_Dscrptn])
    This has the disadvantage that the DLookup function is know as a slow function but for a report and a small table (tblRating) it would be of no meaning.
    Francois

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

    Re: Table relationships (2002)

    That was my point. If you want to display fields in forms or reports, that doesn't mean you have to exclude the fields you don't want to see from the query. Instead, you do that at the form or report level.

    I don't understand the question about referential integrity. I don't believe that Access will let you create multiple relationships between the same two tables and enforce referential integrity in all of them. If I had designed this, I wouldn't have multiple fields to hold the ratings. Instead, I would have the rating entries in a separate table, one per rating type for the appropriate ID.
    Charlotte

  6. #6
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table relationships (2002)

    I understand what you're saying about the forms and reports. In my original post I was looking for the join criteria needed to produce a query similiar to my example. Sorry for the confusion, I wasn't very clear.

    I like your suggestion, I think it's better to change my design. It makes life much easier with the ratings in a separate table, I just wish I would have posted this question earlier.

    Thanks for the help.

Posting Permissions

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