Help with update statement across tables

[Hollywood22]

Hi there I have 3 tables that house financial data. Table 1 is the master T1ID primary key and Amount the field I want updated, Table 2 is one level drilled down t2ID primary key and t1id foreign key and Amount the field I want updated, and table 3 is 2 levels drilled down t3id primary key t2id foreign key and Amount the source of the updates.

Basically I would like an update statement that would

update table2
set amount= sum(table3.amount)
where table2.t2Id = table3.t2id

and similarly

update table1
set amount = sum(table2.amount)
where table1.t1id = table2.t1ID

Apologies as my SQL knowledge is not access based. I've tried query builder and just straight SQL writing and cant seem to get the right values to work out.

Thanks so much.

 

[Joe4]

Welcome to the Board!

It is not really clear as to what your tables hold, and why you want to do this, but a general rule of thumb in databases is that you seldom ever want to (or should) store anything that can be calculated as a hard-coded value (i.e. you seldom ever want to store a "sum" since it can be easily calculated). Storing calculated values can actually violate the rules of normalization undermine the dynamic nature of a database. The only time I have really seen a use for it is to capture historical records at a particular point in time (and you don't want those values to change if the underlying values can change).

So, you would store the calculations in a query. Queries can be used for the Data Source for all the same objects that tables can (i.e. other queries, forms, reports, exports), which is why there is seldom a need to write calculations back to a table.

Can you explain your tables a bit more, and why you think the calculations need to be stored at the table level?

 

[Hollywood22]

Welcome to the Board!

It is not really clear as to what your tables hold, and why you want to do this, but a general rule of thumb in databases is that you seldom ever want to (or should) store anything that can be calculated as a hard-coded value (i.e. you seldom ever want to store a "sum" since it can be easily calculated). Storing calculated values can actually violate the rules of normalization undermine the dynamic nature of a database. The only time I have really seen a use for it is to capture historical records at a particular point in time (and you don't want those values to change if the underlying values can change).

So, you would store the calculations in a query. Queries can be used for the Data Source for all the same objects that tables can (i.e. other queries, forms, reports, exports), which is why there is seldom a need to write calculations back to a table.

Can you explain your tables a bit more, and why you think the calculations need to be stored at the table level?

I will do my best to explain though it is probably a little specific to my need. Essentially the three tables hold 3 aggregate levels of an investment. Table 1 holds the total investment amount. Table 2 holds the separate payments of that total, sometimes 2-5 subtotals with differing release dates. Table 3 holds the information of each of those sub totals by investor and date. Given that these dates are always estimated and the constantly shifting exchange rates, I cant simply populate them in advance at the third level so we use the total of levels 1 or 2 respectively depending on the need. Its certainly true that had the original designer build the dataset differently this need could have been avoided storing only the third level details but I am kinda stuck without redesigning the whole thing and if the fix is simply creating 2 or 3 updates queries to run upon openning the database, its a good, easy quick fix.

Does this help? I completely follow the logic of never wanting to store a value that can be calculated, but as I say unless I redesign the whole system and the canned reports, I am a little hogtied.

 

[Joe4]

What I mean is you can store calculated values/fields in a query, and then you can use the queries for whatever needs you may have. So I don't know if actually writing the value back to a table is necessary.
Because I do not know your whole project and what you are trying to do accomplish with it, I may not understand your business needs. Can you tell me why it needs to be a hard-coded value in a table instead of a calculated field in a query?

By the way, you would use Aggregate (Totals) Queries in order to combine certain records and sum them up.

 

[Hollywood22]

What I mean is you can store calculated values/fields in a query, and then you can use the queries for whatever needs you may have. So I don't know if actually writing the value back to a table is necessary.
Because I do not know your whole project and what you are trying to do accomplish with it, I may not understand your business needs. Can you tell me why it needs to be a hard-coded value in a table instead of a calculated field in a query?

By the way, you would use Aggregate (Totals) Queries in order to combine certain records and sum them up.

I understand your concern and confusion looking at this issue from your lens. The issue is that I have inherited this database, I am not creating it and so I am not looking to reinvent the wheel here, simply apply a band-aid fix to an issue. I understand and appreciate the "proper" way this should have been constructed, but it hasn't been done so and I am simply looking for any help I can get in the query language as described in the first post.

Thanks again for all the help, I do appreciate it.

 

[Joe4]

Can you post a small sample/example of each of your three data tables, and your expected results?
I think it will help to have an image of what the data looks like you are working with.

Thanks

 

[Hollywood22]

Can you post a small sample/example of each of your three data tables, and your expected results?
I think it will help to have an image of what the data looks like you are working with.

Thanks

Absolutely, as the data is sensitive and there are many unnecessary data points pertinent to this query I've synthesized a simpler version and attached below (hope the paste works).

To help Follow the progression, Tables 1,2,3 are all populated at the onset with estimates of amounts and dates. As the sub-payments of Table 3 occur, I include the true amounts (often changed as a result of exchange rates). So as you can see from table 3, the total for "T2ID 2" is no longer 250, but rather 253. and similarly for Table 1, 503 from 500.

As such, rather than to go back manually and update tables 2/1, I would rather simply run an update query to do this for me. Or if possible put a rule in the table itself (though I don't know if that's possible).

simply a query such as

update table2
set amount = [sum(table3.amount) where table2.t2Id = table3.t2id]

and then I can simply change the table names to adjust table 1 similarly.

Table 1				Table 2					Table 3
T1ID	Date	Amount		T2ID	T1ID	Date	Amount		T3ID	T2ID	Date	Amount
1	01/01/2014	500		1	1	01/01/2014	250		1	1	01/01/2014	250
				2	1	01/29/2014	250		2	2	01/29/2014	125
									3	2	01/29/2014	100
									4	2	01/29/2014	28

<colgroup><col><col><col span="4"><col><col span="4"><col><col></colgroup><tbody>
</tbody>

 

[Joe4]

Let me show you what I was talking about, in how you can get what you are looking for simply through SELECT queries, without having to use UPDATE queries. Let's see if that works for you. I was a little confused as to when you include the Date in your Join, and when you don't want to, so hopefully I got this right.


Query1:

SELECT Table3.T2ID, Table3.Date, Sum(Table3.Amount) AS SumOfAmount
FROM Table3
GROUP BY Table3.T2ID, Table3.Date;

Query2:

SELECT Table2.T2ID, Table2.T1ID, Table2.Date, Query1.SumOfAmount
FROM Table2 
INNER JOIN Query1 
ON (Table2.T2ID=Query1.T2ID) 
AND (Table2.Date=Query1.Date);

Query3:

SELECT Table1.T1ID, Table1.Date, Sum(Query2.SumOfAmount) AS SumOfSumOfAmount
FROM Table1 
INNER JOIN Query2 
ON Table1.T1ID = Query2.T1ID
GROUP BY Table1.T1ID, Table1.Date;

Then, you only need to run Query3 to get your results, as since it calls the other 2 queries via nesting, it is not necessary to physically run them once they are set up.

If you really want to physically update the table fields with an Update Query, you will probably need to write your Queries to a temporary table, as Access requires Update Queries to only include Updateable Queries, and Aggregate Queries are not updateable (even though the value you are trying to update is in the table, not in the query, Access requires ALL objects involved to be updateable - this is a complaint I have with Access!). So you would probably have to convert the Queries I posted to MakeTable (or Append Queries), write the data to Temporary tables, and then write your Update Queries using those.

 

[Joe4]

Basically I would like an update statement that would

update table2
set amount= sum(table3.amount)
where table2.t2Id = table3.t2id

and similarly

update table1
set amount = sum(table2.amount)
where table1.t1id = table2.t1ID

Actually, I think I figured out a way to do the Update Queries without the need for Temporary tables - via the use of the DSUM function (see: MS Access: DSum Function).

So your first query would look like:

UPDATE Table2 
SET Table2.Amount = DSum("Amount","Table3","T2ID=[Table3]![T2ID]");

and the second one would follow the same logic.

 

[Hollywood22]

Actually, I think I figured out a way to do the Update Queries without the need for Temporary tables - via the use of the DSUM function (see: MS Access: DSum Function).

So your first query would look like:

UPDATE Table2 
SET Table2.Amount = DSum("Amount","Table3","T2ID=[Table3]![T2ID]");

and the second one would follow the same logic.

Thanks for the effort once again! Unfortunately this seems to be having the same issue I had previously in that it is seemingly not accepting the criteria that T2ID = T2ID across both tables and rather the sum being updated is the total sum of all entries.

The temporary table method may be the only was to get this to work. Is the code for that very difficult?