New data structure, incorrect query sums (1 Viewer)

zeroaccess

Active member
Local time
Today, 07:57
Joined
Jan 30, 2020
Messages
671
This is probably something simple, but:

Table Data:
Samples.png


Sum of Samples for PR 5.5.12 = 630

Query Output:

Samples 2.png


Where is the extra 156 coming from? Here:

Samples 3.png


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.

Samples 4.png
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:57
Joined
Oct 29, 2018
Messages
21,358
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

..forever waiting... waiting for jellybean!
Local time
Today, 20:57
Joined
May 7, 2009
Messages
19,175
you need to Link them by PR number, since this is the common value to group.
 

zeroaccess

Active member
Local time
Today, 07:57
Joined
Jan 30, 2020
Messages
671
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

..forever waiting... waiting for jellybean!
Local time
Today, 20:57
Joined
May 7, 2009
Messages
19,175
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

Active member
Local time
Today, 07:57
Joined
Jan 30, 2020
Messages
671
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

I’m here to help
Staff member
Local time
Today, 05:57
Joined
Oct 29, 2018
Messages
21,358
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

Active member
Local time
Today, 07:57
Joined
Jan 30, 2020
Messages
671
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

Active member
Local time
Today, 07:57
Joined
Jan 30, 2020
Messages
671
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.
 

Attachments

  • Sums.png
    Sums.png
    7.1 KB · Views: 75
  • Query 3.png
    Query 3.png
    27.4 KB · Views: 70

Users who are viewing this thread

Top Bottom