Access - update query

M

Manfred

Good evening (at least here in South Africa) - could somebody please assist
with the structure for update queries. I am trying to accumulate two totals
in a 3rd file from data out of two other files.

File 1: tblLoanTrans

LoanTranID (ID)
TranAmount
LoanAccountID (relates to tblLoanAccount.LoanAccountID)
TranTypeID (relates to tblLoanTranType.LoanTranTypeID)

File 2: tblLoanTranType

LoanTranTypeID (ID)
TranType ('Charges / Capital')

File 3: tblLoanAccount

tblLoanAccountID (ID)
TotalCharges
TotalCapital

From tblLoanTrans update TotalCharges and TotalCapital on tblLoanAccount.
I've tried this with a series of 3 queries but keep on getting "Operation
must use an updateable query".

Any help would greatly appreciated...
 
S

Smartin

Manfred said:
Good evening (at least here in South Africa) - could somebody please assist
with the structure for update queries. I am trying to accumulate two totals
in a 3rd file from data out of two other files.

File 1: tblLoanTrans

LoanTranID (ID)
TranAmount
LoanAccountID (relates to tblLoanAccount.LoanAccountID)
TranTypeID (relates to tblLoanTranType.LoanTranTypeID)

File 2: tblLoanTranType

LoanTranTypeID (ID)
TranType ('Charges / Capital')

File 3: tblLoanAccount

tblLoanAccountID (ID)
TotalCharges
TotalCapital

From tblLoanTrans update TotalCharges and TotalCapital on tblLoanAccount.
I've tried this with a series of 3 queries but keep on getting "Operation
must use an updateable query".

Any help would greatly appreciated...
Click to expand...

Advice: What you are attempting to do--store calculated values in a
table--is a big no-no. Suppose you commit totals from a set of
transactions today. Tomorrow you add transactions. Now your totals are
wrong. Next week you want to update the totals. How do you know which
transactions have already been committed? Suppose an older transaction
is found to be in error. Now your totals are wrong.

Totals can easily be calculated on the fly using a totals query. They
will always be up to date with your data, and can easily be calculated
as of a point in time if desired.

If you really want to know how to do an update yours might look like
this (for Charges):

UPDATE tblLoanAccount.TotalCharges
SELECT SUM(tblLoanTrans.TranAmount)
FROM tblLoanTrans INNER JOIN tblLoanTranType
ON tblLoanTrans.LoanAccountID = tblLoanAccount.LoanAccountID
WHERE tblLoanTranType.TranType = 'Charges';

HOWEVER I would strongly recommend leaving out the entire first line of
that (UPDATE...) and simply store the rest as a SELECT query.
 
M

Manfred

Smartin said:
Advice: What you are attempting to do--store calculated values in a
table--is a big no-no. Suppose you commit totals from a set of
transactions today. Tomorrow you add transactions. Now your totals are
wrong. Next week you want to update the totals. How do you know which
transactions have already been committed? Suppose an older transaction
is found to be in error. Now your totals are wrong.

Totals can easily be calculated on the fly using a totals query. They
will always be up to date with your data, and can easily be calculated
as of a point in time if desired.

If you really want to know how to do an update yours might look like
this (for Charges):

UPDATE tblLoanAccount.TotalCharges
SELECT SUM(tblLoanTrans.TranAmount)
FROM tblLoanTrans INNER JOIN tblLoanTranType
ON tblLoanTrans.LoanAccountID = tblLoanAccount.LoanAccountID
WHERE tblLoanTranType.TranType = 'Charges';

HOWEVER I would strongly recommend leaving out the entire first line of
that (UPDATE...) and simply store the rest as a SELECT query.
Click to expand...
Thank you very much for the help - much appreciated. The reason I want to
store these totals is because I use them for further (rather complex)
calculations. I intend to "refresh" the totals before any forms or reports
based on a flag pending whether tarnsactions have been added/ edited.
 
J

John Spencer

Access won't let you update a field to a value that is calculated using sum.

You can do this using VBA functions DSum or you can populate (add records
to) an intermediate (temp) table with records that contain the data and then
update your final table with the results.

Assumption:
tblLoanAccount.tblLoanAccountID is a text field (If it is a number field
then Change """ to " and drop & """"")

UPDATE tblLoanAccount
Set TotalCharges = DSUM("TranAmount","tblLoanTrans","TranType =""Charges""
AND LoanAccountID=""" & tblLoanAccountID & """")
, TotalCapital = DSUM("TranAmount","tblLoanTrans","TranType =""Capital""
AND LoanAccountID=""" & tblLoanAccountID & """")

To build the temp table, you just need to do an aggregate query and use that
to populate the table

SELECT LoanAccountID, TranTypeID, Sum(TranAmount) as TotalAmount
FROM tblLoanTrans
GROUP BY LoanAccountID, TranTypeID

Once you have the values in the temp table, you would join that to
tblLoanAccount and populate the fields with two queries
UPDATE tblLoanAccount INNER JOIN tblTemp
On tblLoanAccount.tblLoanAccountId = [tblTemp].[LoanAccountID]
SET tblLoanAccount.TotalCharges = [tbltemp].[TotalAmount]
WHERE tblTemp.TranTypeID = "Charges"

Do a similar query for "Capital"
 
M

Manfred

Thanks John. The field is a text field, "unbound".

John Spencer said:
Access won't let you update a field to a value that is calculated using sum.

You can do this using VBA functions DSum or you can populate (add records
to) an intermediate (temp) table with records that contain the data and then
update your final table with the results.

Assumption:
tblLoanAccount.tblLoanAccountID is a text field (If it is a number field
then Change """ to " and drop & """"")

UPDATE tblLoanAccount
Set TotalCharges = DSUM("TranAmount","tblLoanTrans","TranType =""Charges""
AND LoanAccountID=""" & tblLoanAccountID & """")
, TotalCapital = DSUM("TranAmount","tblLoanTrans","TranType =""Capital""
AND LoanAccountID=""" & tblLoanAccountID & """")

To build the temp table, you just need to do an aggregate query and use that
to populate the table

SELECT LoanAccountID, TranTypeID, Sum(TranAmount) as TotalAmount
FROM tblLoanTrans
GROUP BY LoanAccountID, TranTypeID

Once you have the values in the temp table, you would join that to
tblLoanAccount and populate the fields with two queries
UPDATE tblLoanAccount INNER JOIN tblTemp
On tblLoanAccount.tblLoanAccountId = [tblTemp].[LoanAccountID]
SET tblLoanAccount.TotalCharges = [tbltemp].[TotalAmount]
WHERE tblTemp.TranTypeID = "Charges"

Do a similar query for "Capital"

Manfred said:
Good evening (at least here in South Africa) - could somebody please
assist
with the structure for update queries. I am trying to accumulate two
totals
in a 3rd file from data out of two other files.

File 1: tblLoanTrans

LoanTranID (ID)
TranAmount
LoanAccountID (relates to tblLoanAccount.LoanAccountID)
TranTypeID (relates to tblLoanTranType.LoanTranTypeID)

File 2: tblLoanTranType

LoanTranTypeID (ID)
TranType ('Charges / Capital')

File 3: tblLoanAccount

tblLoanAccountID (ID)
TotalCharges
TotalCapital

From tblLoanTrans update TotalCharges and TotalCapital on tblLoanAccount.
I've tried this with a series of 3 queries but keep on getting "Operation
must use an updateable query".

Any help would greatly appreciated...
Click to expand...
Click to expand...
 
J

John Spencer

Sorry, I don't understand your response. A field cannot be bound or
unbound. A control on a form can be bound to a field.

So, have you solved your problem? IF not, can you be more specific on what
you are trying to do. Are you trying to get a value to show in a control on
a form or are you trying to get a value to show in a control on a report?

Normally, you don't store values that you can calculate, so normally you
wouldn't be using an update query to generate the sums. You would use a
SELECT query or the VBA DSUM function to generate (temporarily) the sums and
display these calculated amounts.

Manfred said:
Thanks John. The field is a text field, "unbound".

John Spencer said:
Access won't let you update a field to a value that is calculated using
sum.

You can do this using VBA functions DSum or you can populate (add records
to) an intermediate (temp) table with records that contain the data and
then
update your final table with the results.

Assumption:
tblLoanAccount.tblLoanAccountID is a text field (If it is a number field
then Change """ to " and drop & """"")

UPDATE tblLoanAccount
Set TotalCharges = DSUM("TranAmount","tblLoanTrans","TranType
=""Charges""
AND LoanAccountID=""" & tblLoanAccountID & """")
, TotalCapital = DSUM("TranAmount","tblLoanTrans","TranType =""Capital""
AND LoanAccountID=""" & tblLoanAccountID & """")

To build the temp table, you just need to do an aggregate query and use
that
to populate the table

SELECT LoanAccountID, TranTypeID, Sum(TranAmount) as TotalAmount
FROM tblLoanTrans
GROUP BY LoanAccountID, TranTypeID

Once you have the values in the temp table, you would join that to
tblLoanAccount and populate the fields with two queries
UPDATE tblLoanAccount INNER JOIN tblTemp
On tblLoanAccount.tblLoanAccountId = [tblTemp].[LoanAccountID]
SET tblLoanAccount.TotalCharges = [tbltemp].[TotalAmount]
WHERE tblTemp.TranTypeID = "Charges"

Do a similar query for "Capital"

Manfred said:
Good evening (at least here in South Africa) - could somebody please
assist
with the structure for update queries. I am trying to accumulate two
totals
in a 3rd file from data out of two other files.

File 1: tblLoanTrans

LoanTranID (ID)
TranAmount
LoanAccountID (relates to tblLoanAccount.LoanAccountID)
TranTypeID (relates to tblLoanTranType.LoanTranTypeID)

File 2: tblLoanTranType

LoanTranTypeID (ID)
TranType ('Charges / Capital')

File 3: tblLoanAccount

tblLoanAccountID (ID)
TotalCharges
TotalCapital

From tblLoanTrans update TotalCharges and TotalCapital on
tblLoanAccount.
I've tried this with a series of 3 queries but keep on getting
"Operation
must use an updateable query".

Any help would greatly appreciated...
Click to expand...
Click to expand...
Click to expand...
 
M

Manfred

Hi John

Slip of the tongue I guess - I meant an empty field. What I am trying to
achieve is either to store the total in an empty data field or make that
field a calculated one. The Dsum function I'm trying to get right looks like
this:

DSum("TranAmount","tblLoanTrans","tblLoanTranType.TranType =""Charge"" AND
tblLoanTrans.LoanAccountID=" & tblLoanAccount.LoanAccountID)

At this stage I'm getting a "type conversion failure" message.

The data and criterea is derived from two seperate files, updating a third
file. The process looks like this (I've changed a field name in the interim):

File 1: tblLoanTrans

LoanTranID (ID)
TranAmount
LoanAccountID (relates to tblLoanAccount.LoanAccountID)
TranTypeID (relates to tblLoanTranType.LoanTranTypeID)

File 2: tblLoanTranType

LoanTranTypeID (ID)
TranDesc
TranType ('Charges / Capital')

File 3: tblLoanAccount

tblLoanAccountID (ID)
TotalCharge
TotalCapital

From tblLoanTrans use TranAmount (with tblLoanTranType.TranType = 'Charge')
to sum into TotalCharge on tblLoanAccount.

From tblLoanTrans use TranAmount (with tblLoanTranType.TranType = 'Capital')
to sum into TotalCapital on tblLoanAccount.

I'm short of pulling my hair out ...


John Spencer said:
Sorry, I don't understand your response. A field cannot be bound or
unbound. A control on a form can be bound to a field.

So, have you solved your problem? IF not, can you be more specific on what
you are trying to do. Are you trying to get a value to show in a control on
a form or are you trying to get a value to show in a control on a report?

Normally, you don't store values that you can calculate, so normally you
wouldn't be using an update query to generate the sums. You would use a
SELECT query or the VBA DSUM function to generate (temporarily) the sums and
display these calculated amounts.

Manfred said:
Thanks John. The field is a text field, "unbound".

John Spencer said:
Access won't let you update a field to a value that is calculated using
sum.

You can do this using VBA functions DSum or you can populate (add records
to) an intermediate (temp) table with records that contain the data and
then
update your final table with the results.

Assumption:
tblLoanAccount.tblLoanAccountID is a text field (If it is a number field
then Change """ to " and drop & """"")

UPDATE tblLoanAccount
Set TotalCharges = DSUM("TranAmount","tblLoanTrans","TranType
=""Charges""
AND LoanAccountID=""" & tblLoanAccountID & """")
, TotalCapital = DSUM("TranAmount","tblLoanTrans","TranType =""Capital""
AND LoanAccountID=""" & tblLoanAccountID & """")

To build the temp table, you just need to do an aggregate query and use
that
to populate the table

SELECT LoanAccountID, TranTypeID, Sum(TranAmount) as TotalAmount
FROM tblLoanTrans
GROUP BY LoanAccountID, TranTypeID

Once you have the values in the temp table, you would join that to
tblLoanAccount and populate the fields with two queries
UPDATE tblLoanAccount INNER JOIN tblTemp
On tblLoanAccount.tblLoanAccountId = [tblTemp].[LoanAccountID]
SET tblLoanAccount.TotalCharges = [tbltemp].[TotalAmount]
WHERE tblTemp.TranTypeID = "Charges"

Do a similar query for "Capital"

Good evening (at least here in South Africa) - could somebody please
assist
with the structure for update queries. I am trying to accumulate two
totals
in a 3rd file from data out of two other files.

File 1: tblLoanTrans

LoanTranID (ID)
TranAmount
LoanAccountID (relates to tblLoanAccount.LoanAccountID)
TranTypeID (relates to tblLoanTranType.LoanTranTypeID)

File 2: tblLoanTranType

LoanTranTypeID (ID)
TranType ('Charges / Capital')

File 3: tblLoanAccount

tblLoanAccountID (ID)
TotalCharges
TotalCapital

From tblLoanTrans update TotalCharges and TotalCapital on
tblLoanAccount.
I've tried this with a series of 3 queries but keep on getting
"Operation
must use an updateable query".

Any help would greatly appreciated...
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

When using DSUM the only fields that you can refer to must be in the domain
(the table or query that you refer to).

So, you can't refer to tblLoanTranType.TranType since that is not in
tblLoanTrans. For illustration purposes, I am going to assume that
TranTypeID in tblLoanTrans is a number where 1 = Charge and 2 = Capital.

So, with DSUM you should be able to do the following.

DSum("TranAmount","tblLoanTrans","TranTypeID = 1 AND LoanAccountID=" &
tblLoanAccount.LoanAccountID)

A query that might work for you follows (not updatable):

SELECT L.LoanTranID
, SUM(IIF(T.TranType="Charges",L.TranAmount,Null)) as ChargeTotal
, SUM(IIF(T.TranType="Capital",L.TranAmount,Null)) as CapitalTotal
FROM tblLoanTrans as L INNER JOIN tblLoanTranType as T
on L.TranTypeID = T.LoanTranTypeID
GROUP BY L.LoanTranID


Manfred said:
Hi John

Slip of the tongue I guess - I meant an empty field. What I am trying to
achieve is either to store the total in an empty data field or make that
field a calculated one. The Dsum function I'm trying to get right looks
like
this:

DSum("TranAmount","tblLoanTrans","tblLoanTranType.TranType =""Charge"" AND
tblLoanTrans.LoanAccountID=" & tblLoanAccount.LoanAccountID)

At this stage I'm getting a "type conversion failure" message.

The data and criterea is derived from two seperate files, updating a third
file. The process looks like this (I've changed a field name in the
interim):

File 1: tblLoanTrans

LoanTranID (ID)
TranAmount
LoanAccountID (relates to tblLoanAccount.LoanAccountID)
TranTypeID (relates to tblLoanTranType.LoanTranTypeID)

File 2: tblLoanTranType

LoanTranTypeID (ID)
TranDesc
TranType ('Charges / Capital')

File 3: tblLoanAccount

tblLoanAccountID (ID)
TotalCharge
TotalCapital

From tblLoanTrans use TranAmount (with tblLoanTranType.TranType =
'Charge')
to sum into TotalCharge on tblLoanAccount.

From tblLoanTrans use TranAmount (with tblLoanTranType.TranType =
'Capital')
to sum into TotalCapital on tblLoanAccount.

I'm short of pulling my hair out ...


John Spencer said:
Sorry, I don't understand your response. A field cannot be bound or
unbound. A control on a form can be bound to a field.

So, have you solved your problem? IF not, can you be more specific on
what
you are trying to do. Are you trying to get a value to show in a control
on
a form or are you trying to get a value to show in a control on a report?

Normally, you don't store values that you can calculate, so normally you
wouldn't be using an update query to generate the sums. You would use a
SELECT query or the VBA DSUM function to generate (temporarily) the sums
and
display these calculated amounts.

Manfred said:
Thanks John. The field is a text field, "unbound".

:

Access won't let you update a field to a value that is calculated
using
sum.

You can do this using VBA functions DSum or you can populate (add
records
to) an intermediate (temp) table with records that contain the data
and
then
update your final table with the results.

Assumption:
tblLoanAccount.tblLoanAccountID is a text field (If it is a number
field
then Change """ to " and drop & """"")

UPDATE tblLoanAccount
Set TotalCharges = DSUM("TranAmount","tblLoanTrans","TranType
=""Charges""
AND LoanAccountID=""" & tblLoanAccountID & """")
, TotalCapital = DSUM("TranAmount","tblLoanTrans","TranType
=""Capital""
AND LoanAccountID=""" & tblLoanAccountID & """")

To build the temp table, you just need to do an aggregate query and
use
that
to populate the table

SELECT LoanAccountID, TranTypeID, Sum(TranAmount) as TotalAmount
FROM tblLoanTrans
GROUP BY LoanAccountID, TranTypeID

Once you have the values in the temp table, you would join that to
tblLoanAccount and populate the fields with two queries
UPDATE tblLoanAccount INNER JOIN tblTemp
On tblLoanAccount.tblLoanAccountId = [tblTemp].[LoanAccountID]
SET tblLoanAccount.TotalCharges = [tbltemp].[TotalAmount]
WHERE tblTemp.TranTypeID = "Charges"

Do a similar query for "Capital"

Good evening (at least here in South Africa) - could somebody please
assist
with the structure for update queries. I am trying to accumulate two
totals
in a 3rd file from data out of two other files.

File 1: tblLoanTrans

LoanTranID (ID)
TranAmount
LoanAccountID (relates to tblLoanAccount.LoanAccountID)
TranTypeID (relates to tblLoanTranType.LoanTranTypeID)

File 2: tblLoanTranType

LoanTranTypeID (ID)
TranType ('Charges / Capital')

File 3: tblLoanAccount

tblLoanAccountID (ID)
TotalCharges
TotalCapital

From tblLoanTrans update TotalCharges and TotalCapital on
tblLoanAccount.
I've tried this with a series of 3 queries but keep on getting
"Operation
must use an updateable query".

Any help would greatly appreciated...
Click to expand...
Click to expand...
Click to expand...
 
M

Manfred

Thanks John - would be a good idea to enhance ACCESS / SQL to be able to do
that. I come from a PICK / D3 environment where we would call these
references "correlatives". But thanks anyway.

John Spencer said:
When using DSUM the only fields that you can refer to must be in the domain
(the table or query that you refer to).

So, you can't refer to tblLoanTranType.TranType since that is not in
tblLoanTrans. For illustration purposes, I am going to assume that
TranTypeID in tblLoanTrans is a number where 1 = Charge and 2 = Capital.

So, with DSUM you should be able to do the following.

DSum("TranAmount","tblLoanTrans","TranTypeID = 1 AND LoanAccountID=" &
tblLoanAccount.LoanAccountID)

A query that might work for you follows (not updatable):

SELECT L.LoanTranID
, SUM(IIF(T.TranType="Charges",L.TranAmount,Null)) as ChargeTotal
, SUM(IIF(T.TranType="Capital",L.TranAmount,Null)) as CapitalTotal
FROM tblLoanTrans as L INNER JOIN tblLoanTranType as T
on L.TranTypeID = T.LoanTranTypeID
GROUP BY L.LoanTranID


Manfred said:
Hi John

Slip of the tongue I guess - I meant an empty field. What I am trying to
achieve is either to store the total in an empty data field or make that
field a calculated one. The Dsum function I'm trying to get right looks
like
this:

DSum("TranAmount","tblLoanTrans","tblLoanTranType.TranType =""Charge"" AND
tblLoanTrans.LoanAccountID=" & tblLoanAccount.LoanAccountID)

At this stage I'm getting a "type conversion failure" message.

The data and criterea is derived from two seperate files, updating a third
file. The process looks like this (I've changed a field name in the
interim):

File 1: tblLoanTrans

LoanTranID (ID)
TranAmount
LoanAccountID (relates to tblLoanAccount.LoanAccountID)
TranTypeID (relates to tblLoanTranType.LoanTranTypeID)

File 2: tblLoanTranType

LoanTranTypeID (ID)
TranDesc
TranType ('Charges / Capital')

File 3: tblLoanAccount

tblLoanAccountID (ID)
TotalCharge
TotalCapital

From tblLoanTrans use TranAmount (with tblLoanTranType.TranType =
'Charge')
to sum into TotalCharge on tblLoanAccount.

From tblLoanTrans use TranAmount (with tblLoanTranType.TranType =
'Capital')
to sum into TotalCapital on tblLoanAccount.

I'm short of pulling my hair out ...


John Spencer said:
Sorry, I don't understand your response. A field cannot be bound or
unbound. A control on a form can be bound to a field.

So, have you solved your problem? IF not, can you be more specific on
what
you are trying to do. Are you trying to get a value to show in a control
on
a form or are you trying to get a value to show in a control on a report?

Normally, you don't store values that you can calculate, so normally you
wouldn't be using an update query to generate the sums. You would use a
SELECT query or the VBA DSUM function to generate (temporarily) the sums
and
display these calculated amounts.

Thanks John. The field is a text field, "unbound".

:

Access won't let you update a field to a value that is calculated
using
sum.

You can do this using VBA functions DSum or you can populate (add
records
to) an intermediate (temp) table with records that contain the data
and
then
update your final table with the results.

Assumption:
tblLoanAccount.tblLoanAccountID is a text field (If it is a number
field
then Change """ to " and drop & """"")

UPDATE tblLoanAccount
Set TotalCharges = DSUM("TranAmount","tblLoanTrans","TranType
=""Charges""
AND LoanAccountID=""" & tblLoanAccountID & """")
, TotalCapital = DSUM("TranAmount","tblLoanTrans","TranType
=""Capital""
AND LoanAccountID=""" & tblLoanAccountID & """")

To build the temp table, you just need to do an aggregate query and
use
that
to populate the table

SELECT LoanAccountID, TranTypeID, Sum(TranAmount) as TotalAmount
FROM tblLoanTrans
GROUP BY LoanAccountID, TranTypeID

Once you have the values in the temp table, you would join that to
tblLoanAccount and populate the fields with two queries
UPDATE tblLoanAccount INNER JOIN tblTemp
On tblLoanAccount.tblLoanAccountId = [tblTemp].[LoanAccountID]
SET tblLoanAccount.TotalCharges = [tbltemp].[TotalAmount]
WHERE tblTemp.TranTypeID = "Charges"

Do a similar query for "Capital"

Good evening (at least here in South Africa) - could somebody please
assist
with the structure for update queries. I am trying to accumulate two
totals
in a 3rd file from data out of two other files.

File 1: tblLoanTrans

LoanTranID (ID)
TranAmount
LoanAccountID (relates to tblLoanAccount.LoanAccountID)
TranTypeID (relates to tblLoanTranType.LoanTranTypeID)

File 2: tblLoanTranType

LoanTranTypeID (ID)
TranType ('Charges / Capital')

File 3: tblLoanAccount

tblLoanAccountID (ID)
TotalCharges
TotalCapital

From tblLoanTrans update TotalCharges and TotalCapital on
tblLoanAccount.
I've tried this with a series of 3 queries but keep on getting
"Operation
must use an updateable query".

Any help would greatly appreciated...
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

There is nothing stopping you from creating and savng query containing
tblLoanTrans and tblLoanTranType and then using that as the domain for the
DSum.

Manfred said:
Thanks John - would be a good idea to enhance ACCESS / SQL to be able to
do
that. I come from a PICK / D3 environment where we would call these
references "correlatives". But thanks anyway.

John Spencer said:
When using DSUM the only fields that you can refer to must be in the
domain
(the table or query that you refer to).

So, you can't refer to tblLoanTranType.TranType since that is not in
tblLoanTrans. For illustration purposes, I am going to assume that
TranTypeID in tblLoanTrans is a number where 1 = Charge and 2 = Capital.

So, with DSUM you should be able to do the following.

DSum("TranAmount","tblLoanTrans","TranTypeID = 1 AND LoanAccountID=" &
tblLoanAccount.LoanAccountID)

A query that might work for you follows (not updatable):

SELECT L.LoanTranID
, SUM(IIF(T.TranType="Charges",L.TranAmount,Null)) as ChargeTotal
, SUM(IIF(T.TranType="Capital",L.TranAmount,Null)) as CapitalTotal
FROM tblLoanTrans as L INNER JOIN tblLoanTranType as T
on L.TranTypeID = T.LoanTranTypeID
GROUP BY L.LoanTranID


Manfred said:
Hi John

Slip of the tongue I guess - I meant an empty field. What I am trying
to
achieve is either to store the total in an empty data field or make
that
field a calculated one. The Dsum function I'm trying to get right looks
like
this:

DSum("TranAmount","tblLoanTrans","tblLoanTranType.TranType =""Charge""
AND
tblLoanTrans.LoanAccountID=" & tblLoanAccount.LoanAccountID)

At this stage I'm getting a "type conversion failure" message.

The data and criterea is derived from two seperate files, updating a
third
file. The process looks like this (I've changed a field name in the
interim):

File 1: tblLoanTrans

LoanTranID (ID)
TranAmount
LoanAccountID (relates to tblLoanAccount.LoanAccountID)
TranTypeID (relates to tblLoanTranType.LoanTranTypeID)

File 2: tblLoanTranType

LoanTranTypeID (ID)
TranDesc
TranType ('Charges / Capital')

File 3: tblLoanAccount

tblLoanAccountID (ID)
TotalCharge
TotalCapital

From tblLoanTrans use TranAmount (with tblLoanTranType.TranType =
'Charge')
to sum into TotalCharge on tblLoanAccount.

From tblLoanTrans use TranAmount (with tblLoanTranType.TranType =
'Capital')
to sum into TotalCapital on tblLoanAccount.

I'm short of pulling my hair out ...


:

Sorry, I don't understand your response. A field cannot be bound or
unbound. A control on a form can be bound to a field.

So, have you solved your problem? IF not, can you be more specific on
what
you are trying to do. Are you trying to get a value to show in a
control
on
a form or are you trying to get a value to show in a control on a
report?

Normally, you don't store values that you can calculate, so normally
you
wouldn't be using an update query to generate the sums. You would use
a
SELECT query or the VBA DSUM function to generate (temporarily) the
sums
and
display these calculated amounts.

Thanks John. The field is a text field, "unbound".

:

Access won't let you update a field to a value that is calculated
using
sum.

You can do this using VBA functions DSum or you can populate (add
records
to) an intermediate (temp) table with records that contain the data
and
then
update your final table with the results.

Assumption:
tblLoanAccount.tblLoanAccountID is a text field (If it is a number
field
then Change """ to " and drop & """"")

UPDATE tblLoanAccount
Set TotalCharges = DSUM("TranAmount","tblLoanTrans","TranType
=""Charges""
AND LoanAccountID=""" & tblLoanAccountID & """")
, TotalCapital = DSUM("TranAmount","tblLoanTrans","TranType
=""Capital""
AND LoanAccountID=""" & tblLoanAccountID & """")

To build the temp table, you just need to do an aggregate query and
use
that
to populate the table

SELECT LoanAccountID, TranTypeID, Sum(TranAmount) as TotalAmount
FROM tblLoanTrans
GROUP BY LoanAccountID, TranTypeID

Once you have the values in the temp table, you would join that to
tblLoanAccount and populate the fields with two queries
UPDATE tblLoanAccount INNER JOIN tblTemp
On tblLoanAccount.tblLoanAccountId = [tblTemp].[LoanAccountID]
SET tblLoanAccount.TotalCharges = [tbltemp].[TotalAmount]
WHERE tblTemp.TranTypeID = "Charges"

Do a similar query for "Capital"

Good evening (at least here in South Africa) - could somebody
please
assist
with the structure for update queries. I am trying to accumulate
two
totals
in a 3rd file from data out of two other files.

File 1: tblLoanTrans

LoanTranID (ID)
TranAmount
LoanAccountID (relates to tblLoanAccount.LoanAccountID)
TranTypeID (relates to tblLoanTranType.LoanTranTypeID)

File 2: tblLoanTranType

LoanTranTypeID (ID)
TranType ('Charges / Capital')

File 3: tblLoanAccount

tblLoanAccountID (ID)
TotalCharges
TotalCapital

From tblLoanTrans update TotalCharges and TotalCapital on
tblLoanAccount.
I've tried this with a series of 3 queries but keep on getting
"Operation
must use an updateable query".

Any help would greatly appreciated...
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Update query problem 5
access update query ignore nulls 3
Access 2007 Update query not behaving 1
Update query super slow... 3
Non-updateable query issue 24
operation must use updatable query (?!) 3
query results in Access 3
Problem with Update/Access query 1

Top