Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Relationships in Query (2000)

    Hi,

    I am understand the one to one and one to many relationships. However, I really not clear in what situation that I should use one to one or one to many relationships in query. Please see example below:

    Current I have two tables, one is tblUser and another one is tblEntryData.

    tblUser: UserID, Name, Address, City, State, ZipCode
    tblEntryData: EntryID, UserID, ReviewDate, ReviewType, Review

    And the relationships of the database is one to many relationships, which is One user can have many entry data.

    Now I am create a data entry form and the data source is a query that with both tables. I am confuse now is should I use one to one or one to many relationships for this query.

    Please advice.

    Thanks

    Regards

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

    Re: Relationships in Query (2000)

    I wouldn't do it that way. I would create a main form based on tblUser, and a subform based on tblEntryData. The main form and subform would be linked on UserID through the Link Master Fields and Link Child Fields properties of the subform as a control on the main form.
    This way you don't need a query.

  3. #3
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Relationships in Query (2000)

    Thanks, Hans.

    However, I would like to know when should I use one to one or one to many releationships in query?

    Thanks

    Regards

  4. #4
    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: Relationships in Query (2000)

    You can't create one-to-one or one-to-many relationships "in a query". The power of a relationship is in the table design.

    Hans has shown you a way to relate the primary and foreign keys in the tables using the form using UserID
    Jerry

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

    Re: Relationships in Query (2000)

    I'm wondering whether you really mean one-to-one and one-to-many relationships, or inner and outer joins in a query.

    If you only want to view data for those users who have entered at least some data, you'd use an inner join between the tables on UserID. This is the default line between the two tables.

    If you want to view data for all users, including those who haven't entered any data, you'd use an outer join: double click the line joining the tables, and select the option to include ALL records from tblUser and only related records from tblEntryData. The line between the tables will have an arrow on the side of tblEntryData.

  6. #6
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Relationships in Query (2000)

    Thank you so much, Hans.

Posting Permissions

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