Your problem MIGHT be worsened by the fact that it isn't the table size that is the limit. It is the size of the recordset that cannot exceed 1 GB. So a query is also bound in terms of its size. NORMALLY I would say JOIN the tables on the PID and SID, then you could have everything together. But if I recall your problem as it has developed here, you are dealing with more and more data that you simply CANNOT handle all at once with Access.
Given the nature of those tables, you are in essence denormalizing that table that has the QTY1, QTY2, QTY3... structure. But you are also making a table with the combined values that will be bigger than the original tables from which the data originated. Be sure that it will fit.
And unfortunately, I don't see a way to do this in a single query that doesn't risk making a recordset for the update that would be bigger than the 1 GB limit. You MIGHT consider that the only way to do this is to do it sequentially with three update queries, each one JOINing the target table to ONE of the source tables at a time. That is assuming that we are still talking about your tables with 800,000 records per month? Or has that shrunk a bit?
The problem of course is that the recordset is perhaps a bit smaller than 800,000 records because your dataset IS sparse, but the WHERE clause that takes advantage of that sparseness still requires the syntax that potentially includes the whole table so that the WHERE clause can take effect.
OK, here is what I believe MIGHT be the syntax for what you wanted to do IF IT WOULD FIT, using table names TAB1, TAB2, TAB3 and GRPTAB as you showed us.
UPDATE ( ( ( GRPTAB
LEFT JOIN TAB1 ON GRPTAB.SID = TAB1.SID AND GRPTAB.PID = TAB1.PID )
LEFT JOIN TAB2 ON GRPTAB.SID = TAB2.SID AND GRPTAB.PID = TAB2.PID )
LEFT JOIN TAB3 ON GRPTAB.SID = TAB3.SID AND GRPTAB.PID = TAB3.PID )
SET GRPTAB.QTY1 = NZ( TAB1.QTY, 0 ), GRPTAB.QTY2 = NZ( TAB2.QTY, 0 ), GRPTAB.QTY3 = NZ( TAB3.QTY, 0 );
I DO NOT guarantee that will work but what you want might resemble that. You might have to play with it a bit. The problem is that your dataset is sparse, so INNER JOIN is not the answer. You won't have some records. And I absolutely do not guarantee that a nested JOIN for an update will go that deep in nesting. But IF it works, that will be more or less what it would look like. I DO NOT want to get your hopes up that the recordset resulting from that SQL will work on the tables you previously described. If it blows out the size limits, you are stuck with doing it one update per source table.
This issue is another side effect of your choice to stay with Access. It was your choice and I respect that you felt it was necessary. But somewhere along the line with the amount of data you are trying to manage, you might run into something that won't fit no matter what you do to it. Good luck with that.