Update multiple fields in one table from multiple tables (1 Viewer)

sumdumgai

Registered User.
Local time
Today, 02:18
Joined
Jul 19, 2007
Messages
453
I have multiple table with same design:
e.g., tab1:
PID, SID, QTY


tab2:
PID, SID, QTY



I have another table with design:
grpTab:
PID, SID, QTY1, QTY2, QTY3


How can I update:

grpTab.QTY1 FROM
tab1.QTY where tab1.PID = PID and tab1.SID = SID



grpTab.QTY2 FROM

tab2.QTY where tab2.PID = PID and tab2.SID = SID


and grpTab.QTY3 FROM
tab3.QTY where tab3.PID = PID and tab3.SID = SID


I've tried various queries but can only get one field update from one table to work:


UPDATE [grpTab]
INNER JOIN tab1 ON ([grpTab].PID = tab1.PID) AND ([grpTab].[SID] = tab1.[SID])
SET [grpTab].QTY1 =tab1.Qty;


Thanks as always for any help.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:18
Joined
Oct 29, 2018
Messages
21,358
Hi. This should be possible but why do you have multiple tables with similar structures? It doesn’t sound like your table structure is properly normalized.
 

sumdumgai

Registered User.
Local time
Today, 02:18
Joined
Jul 19, 2007
Messages
453
Monthly imports to separate linked tables. Importing to same table would exceed Access max size.



Thanks.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:18
Joined
Oct 29, 2018
Messages
21,358
So, tab1 and tab2 are linked tables and grptab is a local table, correct?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 01:18
Joined
Feb 28, 2001
Messages
27,001
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.

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

sumdumgai

Registered User.
Local time
Today, 02:18
Joined
Jul 19, 2007
Messages
453
theDBguy, yes, tab1 & tab2 are linked and grpTab is local.
 

sumdumgai

Registered User.
Local time
Today, 02:18
Joined
Jul 19, 2007
Messages
453
The_Doc_Man, works great. Thanks. I'll see how it performs on more data.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:18
Joined
Oct 29, 2018
Messages
21,358
See? Told you it was possible. Good luck with your project.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 06:18
Joined
Feb 19, 2013
Messages
16,553
This comment doesn't really fit your requirement as described but one other thought - is the data you receive normalised? If not you may find you get better capacity by moving the data to normalised tables.

I once had client experiencing similar issues to yours - the data was a detailed summary of invoices raised which included among other things the customer name and address, product descriptions and the values.

Normalising the data by splitting this into 4 tables (customers, addresses, products, invoice values) reduced the volume of data to around 20% of the original non-normalised volume and resulted in a significant improvement in performance. Further, because customer and product data did not change that often, subsequent loads achieved even better reductions.
 

Users who are viewing this thread

Top Bottom