Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2009
    Location
    RI
    Posts
    31

    Question Unanswered: Problem with Relationships

    I'm working on a light CRM database for work. I've set up the relationships and imported his data from an old DB we were using before and I have an issue. I've read it's bad DB design to have 3 tables all relate to each other, but i don't know any way around it. I'm hoping for some input here because the second I try to write a select query that uses data from any 2 of those tables, the query instantly stops returning results, and I can only imagine that it's because of this funky relationship.

    My form setup is that I have an account details form which has a tab control with 3 tabs - General, Contacts and Activities. General is from the tblAccount, Contacts and Activities are subforms. Contacts lists the various contact persons and their information for each account. The Activities tab is a list of all activities (phone calls, site visits, etc...) performed on the Account. To make it easier to track, I have a Contact drop down on the Activities subform which allows the salesman to pick which contact he met with for the specific activity.

    If anyone has a better suggestion of how I can set this up, I'd greatly appreciate it. I've attached the relationship report to this post. Thank you for your input!
    Attached Files Attached Files

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by rendrag View Post
    I've read it's bad DB design to have 3 tables all relate to each other...
    could you please dig up where you read that?

    either you misinterpreted what it was trying to say, or else it's complete crap

    your relationships look okay, by the way
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2009
    Location
    RI
    Posts
    31
    Thank you very much for the clarification. Unfortunately I read that a long time ago, so I probably misinterpreted what was being said. My main problem is that when I write a Select Query on those tables I get no results. Perhaps it's a problem with my query then. The following query returns nothing and I don't know why.

    Code:
    SELECT DISTINCTROW tblAccount.AccountName, [tblContact.FirstName] & " " & [tblContact.LastName] AS Name, tblActivity.ActivityDate, tblActivity.ActivityTime, tblActivityType.ActivityType, tblActivity.Description, tblContact.Phone
    FROM tblActivityType INNER JOIN ((tblAccount INNER JOIN tblActivity ON tblAccount.AccountID = tblActivity.ContactID) INNER JOIN tblContact ON (tblAccount.AccountID = tblContact.AccountID) AND (tblActivity.ContactID = tblContact.ContactID)) ON tblActivityType.ActivityTypeID = tblActivity.ActivityTypeID;
    That query is for a report I want to run that lists out all the activities performed (on the report itself I group on ActivityDate, and in the detail section I show the ActivityTime, AccountName, Name (of the Contact met), Phone, ActivityType and a description of what was done.


    Any thoughts or ideas?

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Not sure if you are making this mistake, but relationships are not the same as joins.

    Anyway, this looks suspicious:
    Code:
    ON  tblAccount.AccountID = tblActivity.ContactID
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Apr 2009
    Location
    RI
    Posts
    31
    Thanks for catching that. I used the query designer to build the query, so Access made that join. I did change tblActivity.ContactID to tblActivity.AccountID but saw no change.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Based on your upload, none of your joins should be between columns whose names do not match.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Apr 2009
    Location
    RI
    Posts
    31
    I looked over the SQL query again, and all of the join fields match (updated query below)
    Code:
    SELECT DISTINCTROW tblAccount.AccountName, [tblContact.FirstName] & " " & [tblContact.LastName] AS Name, tblActivity.ActivityDate, tblActivity.ActivityTime, tblActivityType.ActivityType, tblActivity.Description, tblContact.Phone
    FROM tblActivityType INNER JOIN ((tblAccount INNER JOIN tblActivity ON tblAccount.AccountID = tblActivity.AccountID) INNER JOIN tblContact ON (tblContact.ContactID = tblActivity.ContactID) AND (tblAccount.AccountID = tblContact.AccountID)) ON tblActivityType.ActivityTypeID = tblActivity.ActivityTypeID;
    Problem is I have no experience with complex joins like these, so i'm a little bit like a fish out of water here. I'm unsure of what a query like this should look like. I appreciate everyone's input here, it's been very beneficial.

  8. #8
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201
    If this query is still not working, I wonder whether it is because you are in effect working from the bottom up by putting the ActivityType at the head of the query. Looking at the fields you have selected, I would have thought that the logical nesting would have been to start with the Account, then the Contact, then the Activity, then the ActivityType. This has nothing much to do with RDBMS theory, but more to do with the way the Jet engine looks at the query.

  9. #9
    Join Date
    Apr 2009
    Location
    RI
    Posts
    31
    I built the query through the Query Designer, so the actual Select script is all Access' doing. I'm green when it comes to SQL script, but i'll attempt to write it starting at the Account Level.

    Thank you for the suggestion!

  10. #10
    Join Date
    Apr 2009
    Location
    RI
    Posts
    31
    doh... i was trying to re-build the query piece by piece and I found the problem - in the Activity table, the ContactID field is blank for all records. Looks like i have match the contact to the activity peformed for 1800+ records. Fantastic.

    Sorry for wasting people's time.

  11. #11
    Join Date
    Apr 2009
    Location
    RI
    Posts
    31
    Well, that got me nowhere... I added a couple contactID's to the field in tblActivity, however the report still shows no data. I tried re-writing my sql statement as suggested by Jim Wright, however I'm getting a syntax error of a missing operator and I can't seem to spot it... suggestions?
    Code:
    SELECT DISTINCTROW tblAccount.AccountName, [tblContact.FirstName] & " " & [tblContact.LastName] AS Name, tblActivity.ActivityDate, tblActivity.ActivityTime, tblActivityType.ActivityType, tblActivity.Description, tblContact.Phone
    FROM tblContact INNER JOIN tblAccount ON tblContact.AccountID = tblAccount.AccountID
    INNER JOIN tblAccount ON tblActivity.AccountID = tblAccount.AccountID
    INNER JOIN tblActivity ON tblContact.ContactID = tblActivity.ContactID
    INNER JOIN tblActivityType ON tblActivityTypeID = tblAcitivity.ActivityTypeID;

  12. #12
    Join Date
    Apr 2009
    Location
    RI
    Posts
    31

    Thumbs up

    so I finally figured out what the problem was. Once I removed the join between contacts and activities and re-did the query, everything started working again. Thank you 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
  •