Need query to group sums where two fields match (1 Viewer)

sumdumgai · Mar 19, 2019

Here's my problem and I need some help, please. Two tables, T1 and T2. Each has three fields, SID, PID and QTY. See my example below with various field values in SID and PID.

T1:
SID, PID, QTY
s1,ps1,0
s1,p2,0
s2,p1,0
s2,p2,0

T2:
SID, PID, QTY
s1,p1,3
s1,p1,5
s1,p2,3
s1,p2,6
s2,p1,1
s2,p2,3

I need a query that will consolidate quantities in T2 where SID and PID pairings are the same; i.e., s1,p1 are found twice so sum the QTY and update the QTY field where the T2 F1/F2 pairing matches the T1 F1/F2 pairing.

The resultant T1 table would look like this:
SID, PID, QTY

s1,p1,8
s1,p2,9
s2,p1,1
s2,p2,3

Thanks as always for the great help this forum provides.

sumdumgai · Mar 19, 2019

I've attached small test database to further explain what I have.

The_Doc_Man · Mar 19, 2019

Something similar to:

Code:

UPDATE T1 INNER JOIN T2 ON ( T1.PID = T2.PID ) AND ( T1.SID = T2.SID )
SET T1.QTY = T1.QTY + T2.QTY ;

This assumes you have already created T1 with the combinations you need AND that the T1.QTY fields start off as 0. Note that this works by adding each matching record from T2 into the T1 QTY field. If no records exist in T2 for a given PID & SID, then T1.QTY will stay 0. If you had 20 records for a given PID & SID, they would be added in, one matching record at a time. No need to over-complicate it.

June7 · Mar 19, 2019

I question the need for this. Why do you want to calculate and save aggregate data?
Should save raw data and calculate aggregate data when needed, not save it.

sumdumgai · Mar 20, 2019

June7, there's too much data for Access to handle on an ad hoc basis. I'm trying to solve a capacity limitation by consolidating key data from multiple databases into more manageable tables that can be queried.

sumdumgai · Mar 20, 2019

The Doc Man, is there a way to calculate the sum of matching pair quantities and then setting the T1 quantity for the associated pair to that summed quantity? I'm worried that if the update query is executed more than once, the aggregate quantities will be wrong. Thanks.

June7 · Mar 20, 2019

Yes, there are always exceptions to a 'rule'. See if this helps http://allenbrowne.com/AppInventory.html

The_Doc_Man · Mar 20, 2019

Speed-wise, what I posted should be fast. If you are worried, write a macro to execute TWO action queries... one to erase the QTY field for all records in your T1, the other to do the summation as I suggested. Then do it via the macro. That way, no double-dipping can occur 'cause you clean up beforehand.

sumdumgai · Mar 20, 2019

Reply erased. Still checking.

Need query to group sums where two fields match (1 Viewer)

sumdumgai

Registered User.

sumdumgai

Registered User.

Attachments

The_Doc_Man

Immoderate Moderator

June7

AWF VIP

sumdumgai

Registered User.

sumdumgai

Registered User.

June7

AWF VIP

The_Doc_Man

Immoderate Moderator

sumdumgai

Registered User.

Similar threads

Users who are viewing this thread