Query does not allow data changes

[Guest]

I am having difficulties creating a query that operates as needed. The query
returns the data as expected but I am unable to change any data though the
query. The SQL for the query (simplified version) is as follows:

SELECT [tLI-1].LoanID, [tLI-1].fkOptionsMatrixID, [tLI-1].ReqLoanAmount,
tLTVm.LtvLimit, tLTVm.LowerLimit, tLTVm.UpperLimit

FROM tblOptionsMatrix AS tOptM INNER JOIN (tblLtvMatrix AS tLTVm INNER JOIN
tblLoanInfo AS [tLI-1] ON tLTVm.fkOptionsMatrixId =
[tLI-1].fkOptionsMatrixID) ON tOptM.OptionsMatrixID =
[tLI-1].fkOptionsMatrixID

WHERE (((tLTVm.LowerLimit)<[ReqLoanAmount]) AND
((tLTVm.UpperLimit)>=[ReqLoanAmount]));

There are 2 tables involved:
1) tLI-1
2) tLTVm
These tables are related a 3 table, tOPTm, thought a field named
fkOptionsMatrixID.

tLTVMm also contains a field named RequestedLoanAmount (Curency). tLTVm
contains 4 fields, LtvMatrixID (Prime Key), fkOptionsMatrixId, LowerLimit
(Currency), UpperLimit (Currency), LtvLimit (Percenage).

Table tLTVm is basically a matrix that stores LTV values for loan amount
ranges for various “Options†(e.g.: Option 1, LowerLimit =0, UpperLimit =
500,000, LTV = .90%; Option 2, LowerLimit =500,000, UpperLimit = 650,000, LTV
= .85%; etc.

In the simplified querry, I need to be able to change the
tLI-1.RequestedLoanAmount and have the correct tLTV.LtvLimit to return. The
query from above does properly return the expected value for all existing
records but new records cannot be added and existing data
(RequestedLoanAmount) can not be changed.

Clearly, I am doing something wrong. Hopefully, someone can point me in the
right direction

 

[Van T. Dinh]

Your Query / SQL String is not updateable. Generally, the
3-Table Query will only be updateable if you link them PK
(Table1) to FK (Table2) and PK (Table2) to FK (Table3).

For more info. and few techniques of changing the Query
from non-updateable to updateable, check Access Help
topic "Updatable Queries".

HTH
Van T. Dinh
MVP (Access)

 

[Guest]

First, thank you very much for your reply. I have looked for information as
you recommend but have not found a way to accomplish my goal. I think I am
going about this the hard way. I think that I need to create a VB event at
the form level that will run the query and store then value in the proper
table. The value is used for calculation purposes only and data will never
be directly entered into the tables. Based on you response, I think I need
to expand on what I am trying to accomplish.

-------------------------------------------------------------------------------------------------------
Table [tLI-1] is basically a “Product Order†table. The table includes the
following fields:
[LoanID] (Prime-Key)
[fkOptionsMatrixID]
[LoanAmount].

[tLI-1].[fkOptionsMatrixID] has a many to 1 relation to table “[tOPTm].
[OptionsMatrixID]â€

-------------------------------------------------------------------------------------------------------
Table tLTVm is stores LTV amounts for, based on lower and upper loan amount
limits AND “option types. The table includes the following fields:

[LtvMatrixID] (Prime-Key)
[fkOptionsMatrixID]
LowerLimit
UpperLimit
LTV

[tLTVm].[fkOptionsMatrixID] has a many to 1 relation to table “[tOPTm].
[OptionsMatrixID]†Note that [tLTVm] is not directly related to [tLI-1]

-------------------------------------------------------------------------------------------------------

Sample Data in [tLTMm]:
1, 1,$0.00, $500,000, 0.90
2, 1, $500,000, $650,000, 0.85
3, 1, $650,000, $1,000,000, 0.70
4, 2,$0.00, $333,700, 0.90
5, 2, $333,700, $500,000, 0.85
6, 2, $500,000, $750,000, 0.80
7, 2, 750,000, $1,000,000, 0.65
Etc, Etc

I have created a “Master Query†as a record source that includes [tLI-1],
[tOPTm] and various other “look-up tablesâ€. The query does not include
[tLTVm] because when it is included, data in the master query, data in the
query can no longer be updated.

I need to add a calculated expression that will return the proper [LTV]
value as follows:
From [tLTVm].[fkOptionsMatrixID]
FROM [tLTM]
WHERE [tLI-1].[fkOptionsMatrixID] = [tLTVm].[fkOptionsMatrixID] AND
[tLI-1].[LoanAmount] > [tLTVm].[LowerLimit] AND
[tLI-1].[LoanAmount] <= [tLTVm].[UpperLimit]

I am able to create a “outside query that returns the correct LTV for each
tLI-1 record but the data not updateable. I was also able to create a
sub-query as an expression in the master query that works but also prevents
data updates once sub-query is added.

Maybe I am going about this the wrong way. Maybe an “On-Change eventâ€
linked to [LoanAmount] could work. I would need to pass the
[tLI-1].[LoanID], [tLI-1].[fkOptionsMatrixID],and [tLI-1].[LoanAmount] to a
query in VB. The event could then store/update the value in the underling
table. I am not exactly sure how to due this however. Any recommendations
will be greatly appreciated!

Your Query / SQL String is not updateable. Generally, the
3-Table Query will only be updateable if you link them PK
(Table1) to FK (Table2) and PK (Table2) to FK (Table3).

For more info. and few techniques of changing the Query
from non-updateable to updateable, check Access Help
topic "Updatable Queries".

HTH
Van T. Dinh
MVP (Access)

-----Original Message-----
I am having difficulties creating a query that operates as needed. The query
returns the data as expected but I am unable to change any data though the
query. The SQL for the query (simplified version) is as follows:

SELECT [tLI-1].LoanID, [tLI-1].fkOptionsMatrixID, [tLI- 1].ReqLoanAmount,
tLTVm.LtvLimit, tLTVm.LowerLimit, tLTVm.UpperLimit

FROM tblOptionsMatrix AS tOptM INNER JOIN (tblLtvMatrix AS tLTVm INNER JOIN
tblLoanInfo AS [tLI-1] ON tLTVm.fkOptionsMatrixId =
[tLI-1].fkOptionsMatrixID) ON tOptM.OptionsMatrixID =
[tLI-1].fkOptionsMatrixID

WHERE (((tLTVm.LowerLimit)<[ReqLoanAmount]) AND
((tLTVm.UpperLimit)>=[ReqLoanAmount]));

There are 2 tables involved:
1) tLI-1
2) tLTVm
These tables are related a 3 table, tOPTm, thought a field named
fkOptionsMatrixID.

tLTVMm also contains a field named RequestedLoanAmount (Curency). tLTVm
contains 4 fields, LtvMatrixID (Prime Key), fkOptionsMatrixId, LowerLimit
(Currency), UpperLimit (Currency), LtvLimit (Percenage).

Table tLTVm is basically a matrix that stores LTV values for loan amount
ranges for various â?oOptionsâ? (e.g.: Option 1, LowerLimit =0, UpperLimit =
500,000, LTV = .90%; Option 2, LowerLimit =500,000, UpperLimit = 650,000, LTV
= .85%; etc.

In the simplified querry, I need to be able to change the
tLI-1.RequestedLoanAmount and have the correct tLTV.LtvLimit to return. The
query from above does properly return the expected value for all existing
records but new records cannot be added and existing data
(RequestedLoanAmount) can not be changed.

Clearly, I am doing something wrong. Hopefully, someone can point me in the
right direction

.

 

[Van T. Dinh]

In your original SQL String, you joined [tLTVm] and [tLI-1] by something
different from PK - FK so there is no chance that the Query is updateable.

Sorry, I don't know enough of your set-up (since most of the Field names
sound like Dutch to me) to recommend a fix.

--
HTH
Van T. Dinh
MVP (Access)

First, thank you very much for your reply. I have looked for information as
you recommend but have not found a way to accomplish my goal. I think I am
going about this the hard way. I think that I need to create a VB event at
the form level that will run the query and store then value in the proper
table. The value is used for calculation purposes only and data will never
be directly entered into the tables. Based on you response, I think I need
to expand on what I am trying to accomplish.

-------------------------------------------------------------------------- -----------------------------
Table [tLI-1] is basically a "Product Order" table. The table includes the
following fields:
[LoanID] (Prime-Key)
[fkOptionsMatrixID]
[LoanAmount].

[tLI-1].[fkOptionsMatrixID] has a many to 1 relation to table "[tOPTm].
[OptionsMatrixID]"

-------------------------------------------------------------------------- -----------------------------
Table tLTVm is stores LTV amounts for, based on lower and upper loan amount
limits AND "option types. The table includes the following fields:

[LtvMatrixID] (Prime-Key)
[fkOptionsMatrixID]
LowerLimit
UpperLimit
LTV

[tLTVm].[fkOptionsMatrixID] has a many to 1 relation to table "[tOPTm].
[OptionsMatrixID]" Note that [tLTVm] is not directly related to [tLI-1]

-------------------------------------------------------------------------- -----------------------------

Sample Data in [tLTMm]:
1, 1,$0.00, $500,000, 0.90
2, 1, $500,000, $650,000, 0.85
3, 1, $650,000, $1,000,000, 0.70
4, 2,$0.00, $333,700, 0.90
5, 2, $333,700, $500,000, 0.85
6, 2, $500,000, $750,000, 0.80
7, 2, 750,000, $1,000,000, 0.65
Etc, Etc

I have created a "Master Query" as a record source that includes [tLI-1],
[tOPTm] and various other "look-up tables". The query does not include
[tLTVm] because when it is included, data in the master query, data in the
query can no longer be updated.

I need to add a calculated expression that will return the proper [LTV]
value as follows:
From [tLTVm].[fkOptionsMatrixID]
FROM [tLTM]
WHERE [tLI-1].[fkOptionsMatrixID] = [tLTVm].[fkOptionsMatrixID] AND
[tLI-1].[LoanAmount] > [tLTVm].[LowerLimit] AND
[tLI-1].[LoanAmount] <= [tLTVm].[UpperLimit]

I am able to create a "outside query that returns the correct LTV for each
tLI-1 record but the data not updateable. I was also able to create a
sub-query as an expression in the master query that works but also prevents
data updates once sub-query is added.

Maybe I am going about this the wrong way. Maybe an "On-Change event"
linked to [LoanAmount] could work. I would need to pass the
[tLI-1].[LoanID], [tLI-1].[fkOptionsMatrixID],and [tLI-1].[LoanAmount] to a
query in VB. The event could then store/update the value in the underling
table. I am not exactly sure how to due this however. Any recommendations
will be greatly appreciated!