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.
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.