CODEWITHSUNDEEP
sqlms-access
SadMrFrown
SadMrFrown

Reputation: 157

Update query just showing zero values when there exists non-zero values. (ACCESS)

I have been struggling with this for hours. I am trying to update all values that have the same 'SHORT#'. If the 'SHORT#' is in 017_PolWpart2 I want this to be the value that updates the corresponding 'SHORT#' in 017_WithdrawalsYTD_changelater. This update query is just displaying zeroes, but these values are in fact non-zero.

So say 017_WithdrawalsYTD_changelater looks like this:

SHORT# WithdrawalsYTD
1          0
2          0
3          0
4          0
5          0

and 017_PolWpart2 looks like this:

SHORT# Sum_MTD_AGG
3          50
5          12

I want this:

SHORT# WithdrawalsYTD
1          0
2          0
3          50
4          0
5          12

But I get this:

SHORT# WithdrawalsYTD
1          0
2          0
3          0
4          0
5          0

I have attached the SQL for the Query below.

Thanks! 

UPDATE 017_WithdrawalsYTD_changelater 
INNER JOIN 017b_PolWpart2 ON [017_WithdrawalsYTD_changelater].[SHORT#] = 
                 [017b_PolWpart2].[SHORT#] 
SET [017_WithdrawalsYTD_changelater].WithdrawalsYTD = [017b_PolWpart2].[Sum_MTD_AGG];

EDIT: As I must aggregate on the fly, I have tried to do so. Still getting all kinds off errors. Note the table 17a_PolicyWithdrawalMatch is of the form:

SHORT# MTG_AGG     WithdrawalPeriod       PolDurY
1          3                 1            1
1          5                 1            0
2          2                 1            1
2          22                1             1

So I aggregate:

SHORT# MTG_AGG     
1          3                 
2          24

And put these aggregated values in 017_WithdrawalsYTD_changelater.

I tried to this like so:

SELECT [017a_PolicyWithdrawalMatch].[SHORT#], Sum([017a_PolicyWithdrawalMatch].MTD_AGG) AS Sum_MTD_AGG
WHERE ((([017a_PolicyWithdrawalMatch].WithdrawalPeriod)=[017a_PolicyWithdrawalMatch].[PolDurY]))
GROUP BY [017a_PolicyWithdrawalMatch].[SHORT#]
UPDATE 017_WithdrawalsYTD_changelater INNER JOIN 017a_PolicyWithdrawalMatch ON [017_WithdrawalsYTD_changelater].[SHORT#] = [017a_PolicyWithdrawalMatch].[SHORT#] SET 017_WithdrawalsYTD_changelater.WithdrawalsYTD =Sum_MTD_AGG;

I am getting no luck... I get told SELECT statement is using a reserved word... :(

Upvotes: 2

Views: 91

Answers (1)

Parfait
Parfait

Reputation: 107687

Consider heeding @June7's comments to avoid the use of saving aggregate data in a table as it redundantly uses storage resources since such data can be easily queried in real time. Plus, such aggregate values immediately become historical figures since it is saved inside a static table.

In MS Access, update queries must be sourced from updateable objects of which aggregate queries are not, being read-only types. Hence, they cannot be used in UPDATE statements.

However, if you really, really, really need to store aggregate data, consider using domain functions such as DSUM inside the UPDATE. Below assumes SHORT# is a string column. 

UPDATE [017_WithdrawalsYTD_changelater] c
SET c.WithdrawalsYTD = DSUM("MTD_AGG", "[017a_PolicyWithdrawalMatch]", 
                            "[SHORT#] = '" & c.[SHORT#] & "' AND WithdrawalPeriod = [PolDurY]")

Nonetheless, the aggregate value can be queried and refreshed to current values as needed. Also, notice the use of table aliases to reduce length of long table names: 

SELECT m.[SHORT#], SUM(m.MTD_AGG) AS Sum_MTD_AGG
FROM [017a_PolicyWithdrawalMatch] m
WHERE m.WithdrawalPeriod = m.[PolDurY]
GROUP BY m.[SHORT#]

Upvotes: 1

Related Questions