Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 03-17-2019, 06:55 AM   #1
sumdumgai
Newly Registered User
 
Join Date: Jul 2007
Posts: 384
Thanks: 132
Thanked 1 Time in 1 Post
sumdumgai is on a distinguished road
Update multiple fields in one table from multiple tables

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.

sumdumgai is offline   Reply With Quote
Old 03-17-2019, 07:07 AM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 1,153
Thanks: 12
Thanked 265 Times in 258 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Update multiple fields in one table from multiple tables

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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
sumdumgai (03-17-2019)
Old 03-17-2019, 07:29 AM   #3
sumdumgai
Newly Registered User
 
Join Date: Jul 2007
Posts: 384
Thanks: 132
Thanked 1 Time in 1 Post
sumdumgai is on a distinguished road
Re: Update multiple fields in one table from multiple tables

Monthly imports to separate linked tables. Importing to same table would exceed Access max size.



Thanks.

sumdumgai is offline   Reply With Quote
Old 03-17-2019, 07:43 AM   #4
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 1,153
Thanks: 12
Thanked 265 Times in 258 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Update multiple fields in one table from multiple tables

So, tab1 and tab2 are linked tables and grptab is a local table, correct?
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
sumdumgai (03-17-2019)
Old 03-17-2019, 07:55 AM   #5
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 13,136
Thanks: 70
Thanked 1,377 Times in 1,269 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Update multiple fields in one table from multiple tables

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.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
The Following User Says Thank You to The_Doc_Man For This Useful Post:
sumdumgai (03-17-2019)
Old 03-17-2019, 10:27 AM   #6
sumdumgai
Newly Registered User
 
Join Date: Jul 2007
Posts: 384
Thanks: 132
Thanked 1 Time in 1 Post
sumdumgai is on a distinguished road
Re: Update multiple fields in one table from multiple tables

theDBguy, yes, tab1 & tab2 are linked and grpTab is local.
sumdumgai is offline   Reply With Quote
Old 03-17-2019, 10:48 AM   #7
sumdumgai
Newly Registered User
 
Join Date: Jul 2007
Posts: 384
Thanks: 132
Thanked 1 Time in 1 Post
sumdumgai is on a distinguished road
Re: Update multiple fields in one table from multiple tables

The_Doc_Man, works great. Thanks. I'll see how it performs on more data.

sumdumgai is offline   Reply With Quote
Old 03-17-2019, 02:01 PM   #8
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 1,153
Thanks: 12
Thanked 265 Times in 258 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Update multiple fields in one table from multiple tables

See? Told you it was possible. Good luck with your project.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 03-17-2019, 03:40 PM   #9
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,685
Thanks: 40
Thanked 3,465 Times in 3,354 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: Update multiple fields in one table from multiple tables

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.

__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
sumdumgai (03-18-2019)
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL issue - Select multiple fields from multiple unrelated tables Lestatos Modules & VBA 7 10-29-2013 09:36 AM
Question What is the best way to display multiple fields from multiple tables? SimonSezz General 25 01-28-2009 07:47 PM
iMPORTING MULTIPLE FIELDS FROM MULTIPLE DATA SETS INTO A TABLE s-taylor Tables 2 11-20-2006 08:26 AM
[SOLVED] Update fields in multiple tables Sanies Tables 6 07-21-2005 01:37 PM
VB Code to update Multiple Table Fields jfgambit Modules & VBA 2 12-01-2004 02:59 PM




All times are GMT -8. The time now is 12:37 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World