New data structure, incorrect query sums

[zeroaccess]

This is probably something simple, but:

Table Data:

Sum of Samples for PR 5.5.12 = 630

Query Output:

Where is the extra 156 coming from? Here:

Whenever there is more than one subrecord, the number of "Samples" is counted again, in this case, twice. 630+156=786

How should I adjust my query to fix this? I've tried all 3 join types, I've tried it without the Errors field below. The output doesn't sum correctly unless I remove the second table from the window, so it's definitely the relationship.

 

[theDBguy]

Hi. I might say create a Totals query to sum the errors first (that way, there are unique sample ids in it for the join) and then connect it to you sample table to sum the samples.

 

[arnelgp]

you need to Link them by PR number, since this is the common value to group.

 

[zeroaccess]

Hi. I might say create a Totals query to sum the errors first (that way, there are unique sample ids in it for the join) and then connect it to you sample table to sum the samples.

This ended up working for this query. However - if I bring in other fields to group by, the results are not as expected. It ends up showing the same total for every instance of that field, rather than the expected break down.

This was a walk in the park with the old one-to-many structure...but I'm having a really hard time with the queries after changing to a 3-table structure.

 

[arnelgp]

if you use dbguys, suggest
whatever you add field to your query, add same field on the linking query.
join them both on those fields.

 

[zeroaccess]

if you use dbguys, suggest
whatever you add field to your query, add same field on the linking query.
join them both on those fields.

Good idea...I will try this.

 

[zeroaccess]

So this is getting complicated.

Using subqueries helped for 2 of my queries. However when grouping by more fields, I can not avoid the query repeating numbers and the subqueries aren't helping.

I am starting to doubt this 3-table structure. Even though it is "technically" the way it should be done and I'm liking the interface I came up with to use it, getting the proper query results has been a nightmare that I have invested too many hours on. If anyone is bored at home and wants to take a shot at this, I will provide a copy via email.

 

[theDBguy]

So this is getting complicated.

Using subqueries helped for 2 of my queries. However when grouping by more fields, I can not avoid the query repeating numbers and the subqueries aren't helping.

I am starting to doubt this 3-table structure. Even though it is "technically" the way it should be done and I'm liking the interface I came up with to use it, getting the proper query results has been a nightmare that I have invested too many hours on. If anyone is bored at home and wants to take a shot at this, I will provide a copy via email.

Shoot.

 

[zeroaccess]

Table 1 (Main Form) One
Table 2 (Subform1) Many
Table 3 (Subform 2) Many

I just had a thought - so because my structure is one-to-many, with the many having another one-to-many (one-many-many?), the only way table 3 can get to table 1 is through table 2. Would this be greatly simplified by recording the PK of the main form/table into Table 3 (Subform 2) when adding records? So it would have:

FK from Subform1
FK from Main Form
PK of its own records

Maybe I do a Before or After Update of Subform 2, set field value = Main Form PK

That way, I should be able to directly sum how many Table 3 records exist for each Table 1 record, right?

 

[zeroaccess]

I attempted to link Table 1 and 3 directly by adding Table 1's PK as a FK in Table 3.

This fixed some problems and helped get me closer to my goal, but I can't seem to bring it home. The highlighted total should be 2633. I've tried all kinds of combinations of joins and I can't get a correct result. This is without any subqueries at this point. I've tried those for this particular query design and they just cause more problems.