Making Non-Updateable Query Updatable?

[PeteCresswell]

I think I've painted myself into a corner on this one.

Got a screen that's based on work tables.

Screen't query, however, is joining to production tables to retrieve
certain fields - which I'm guessing is making the query non-updatable.

I *could* just denodrmalize the derived fields to the work table -
which would permit a Form.RecordSource query that points only to the
work table and would be slam-dunk updatable.

Problem is that would denormalize several fields and add greatly to
the management of the screen's contents if/when the user changes
various fields that the derived fields depend on.

What I'm trolling for here is some silver bullet that would magically
make this query into an updateable one.

I'm probably dreaming.... but I know so little about the nuts and
bolts of SQL and some of the people here know so much.... that
posting it here seems worth a try..
---------------------------------------------------------------------------------------------------------------------

Screen snap of query's design window:
http://tinyurl.com/yuks3r

Screen snap of the application screen:
http://tinyurl.com/yu8wv7

Problem query's SQL:
------------------------------------------------------------------------------------------------
SELECT DISTINCT
tblReferenceRate.IsApproved,
ttblSecurity_Security.FloatingSpread,
tblReferenceRate.ReferenceRate,
ttblSecurity_ResetSchedule.*

FROM (
ttblSecurity_ResetSchedule
INNER JOIN ttblSecurity_Security ON
ttblSecurity_ResetSchedule.SecurityID =
ttblSecurity_Security.SecurityID)
INNER JOIN tblReferenceRate
ON (ttblSecurity_Security.ReferenceRateTypeID =
tblReferenceRate.ReferenceRateTypeID) AND
(ttblSecurity_ResetSchedule.ResetDate =
tblReferenceRate.ReferenceRateDate)

WHERE (((ttblSecurity_ResetSchedule.SecurityID)=[forms]![frmSecurity]!
[txtSecurityID]))

ORDER BY ttblSecurity_ResetSchedule.ResetDate DESC;
------------------------------------------------------------------------------------------------

 

[Dirk Goldgar]

I think I've painted myself into a corner on this one.

Got a screen that's based on work tables.

Screen't query, however, is joining to production tables to retrieve
certain fields - which I'm guessing is making the query non-updatable.

I *could* just denodrmalize the derived fields to the work table -
which would permit a Form.RecordSource query that points only to the
work table and would be slam-dunk updatable.

Problem is that would denormalize several fields and add greatly to
the management of the screen's contents if/when the user changes
various fields that the derived fields depend on.

What I'm trolling for here is some silver bullet that would magically
make this query into an updateable one.

I'm probably dreaming.... but I know so little about the nuts and
bolts of SQL and some of the people here know so much.... that
posting it here seems worth a try..
---------------------------------------------------------------------------------------------------------------------

Screen snap of query's design window:
http://tinyurl.com/yuks3r

Screen snap of the application screen:
http://tinyurl.com/yu8wv7

Problem query's SQL:
------------------------------------------------------------------------------------------------
SELECT DISTINCT
tblReferenceRate.IsApproved,
ttblSecurity_Security.FloatingSpread,
tblReferenceRate.ReferenceRate,
ttblSecurity_ResetSchedule.*

FROM (
ttblSecurity_ResetSchedule
INNER JOIN ttblSecurity_Security ON
ttblSecurity_ResetSchedule.SecurityID =
ttblSecurity_Security.SecurityID)
INNER JOIN tblReferenceRate
ON (ttblSecurity_Security.ReferenceRateTypeID =
tblReferenceRate.ReferenceRateTypeID) AND
(ttblSecurity_ResetSchedule.ResetDate =
tblReferenceRate.ReferenceRateDate)

WHERE (((ttblSecurity_ResetSchedule.SecurityID)=[forms]![frmSecurity]!
[txtSecurityID]))

ORDER BY ttblSecurity_ResetSchedule.ResetDate DESC;
------------------------------------------------------------------------------------------------

Joining multiple tables doesn't necessarily make your query nonupdatable,
but using the DISTINCT predicate always does. Can you get by without that?

 

[PeteCresswell]

but using the DISTINCT predicate always does. Can you get by without that?

Yes. In fact, I had just added it as a stab in the dark trying to
make it editable. Just removed it. No improvement.

 

[Dirk Goldgar]

Yes. In fact, I had just added it as a stab in the dark trying to
make it editable. Just removed it. No improvement.

Does every table have a primary key defined? Are the relationships between
the tables defined?

 

[Douglas J. Steele]

Yes. In fact, I had just added it as a stab in the dark trying to
make it editable. Just removed it. No improvement.

In addition to what Dirk's suggested, see what Allen Browne has at
http://www.allenbrowne.com/ser-61.html

 

[(PeteCresswell)]

Per Dirk Goldgar:

Does every table have a primary key defined? Are the relationships between
the tables defined?

Every table had a PK.

But at the DB level, there are not relationships defined between
the tables bc two of them a transient "work" tables in an
equally-transient "work" DB in C:\Temp.

 

[Dirk Goldgar]

Per Dirk Goldgar:

Every table had a PK.

But at the DB level, there are not relationships defined between
the tables bc two of them a transient "work" tables in an
equally-transient "work" DB in C:\Temp.

Hmm. You won't be able to enforce inter-database relationships, and that
may be a factor. I don't know whether the fact that the tables are in
different databases renders the query non-updatable. I'll have to do a
little testing.