Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2011
    Posts
    2

    Unanswered: Help w/Simple Query Problem

    I have a query that consists of 4 tables, I'll refer to as tables A through D. There is a many-to-many relationship between A and B, and between A and C. There is also a many-to-many relationship between C and D. The query includes fields from each of the 4 tables and for the most part when I run the query I see 42 records returned which is what I expect. However, there two additional fields from table D that I want in the query but whether I add one or both of them, the number of records returned increases. I am not changing the criteria, and when the additional fields are removed the query again returns the correct number of records. Been playing with this for 3 hours and now have a migraine headache!

    I attached a screenshot of the query design window. Adding the fields DebitAmount and CreditAmount from the last table on the right is what triggers the problem.
    Attached Thumbnails Attached Thumbnails AccessQuery1.jpg  

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Schyler View Post
    Adding the fields DebitAmount and CreditAmount from the last table on the right is what triggers the problem.
    well, no wonder -- you have GROUP BY on everything

    take that off, and watch what happens

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2011
    Posts
    2

    Good Call, But...

    ok that was in error. Dandy, now the query returns 66 vs. 42 records with table D in the picture and if I remove table D I get other results. Headache just got worse.

    I'll simplify. With tables A & B (and the criteria shown), 21 records are returned which is what I expect. But there is a field in table C and fields in table D that I want included. How does one do that? It would seem to me that dropping in table C and relating it to table A is all I need to do. However, if I just add table C w/o relating it, 206,976 records are returned. When I relate C to A, 52 records are returned...

    Obviously I'm not understanding something about the relationships but this can't be that difficult.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Schyler View Post
    Obviously I'm not understanding something about the relationships ...
    imagine how we feel, we know your tables even less than you do

    Quote Originally Posted by Schyler View Post
    ... but this can't be that difficult.
    unfortunately, it is

    any time you involve multiple many-to-many relationships in the same query, you've got to do something to manage the multiplicities that will result

    and gosh, do i ever hate working in a vacuum like "table A and table B"

    here, let me give you a simpler example

    person hobbies
    tom stampcollecting
    tom fishing
    díck drinking
    díck smoking
    harry television
    harry eating

    person pets
    tom goldfish
    tom parakeet
    díck dog
    díck ferret
    harry cat
    harry hamster

    there is just no way you're going to return data from those two tables simultaneously but without multiplicities, not without doing a lot of very complicated query gymnastics

    see what i mean?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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