What makes a query non-updateable?

[Nick Mirro]

The add record button in a query (datasheet view) periodically dims,
rejecting any new data. The associate forms then no longer work.

I have to then change the relationships of the queries two tables to 1 to 1,
or 1 to many depending on which it was last at. The logic would always
imply 1:1 (patient visits to billing incidents), but I cannot get this to
hold.

What could make the query suddenly become non-updateable?

Nick

 

[Lynn Trapp]

A 1 to 1 query is ALWAYS updatable. When you say you "change the
relationships of the queries two tables to 1 to 1", how are you doing this?

 

[Nick Mirro]

Thanks for the reply. I add an autonumber primary key and then redefine the
relationship. On the last error, it was non-updateable as a 1:1 with its
immediate parent.

http://home.comcast.net/~nickmirro/images/Temporary/Relationships.pdf

The error happens between tblVisits and both tblPayments and tblBillingInfo

Nick

p.s. there's a more detailed post in formscoding.

 

[Duane Hookom]

Are the VisitID fields primary keys in each table? BTW: I would change the
table structures to make them one to many so the structure is more
normalized.

 

[Nick Mirro]

Thanks for the reply. I can use help with this. VisitID is the primary key
in tblVisits, and in any linked table with a 1:1 relationship. When the
secondary table becomes many, its autonumber field becomes primary key.

The current normalization seems to be the most ergonomic from the form
perspective. db requires lots of controls per page, and many (such as cpt
codes per visit) need to be viewable all at once, intermingled with
calculating expressions. It is beyond my ability to go completely
normalized and display this ergonomically.

I understand that in medical billing, 1:1 is frequently used.

Nick

 

[Duane Hookom]

I have seen your relationships but not your query's sql. This might help.

 

[Nick Mirro]

Hi Duane. This sql follows my having switched tblPayments back to 1:1 with
its primary, tblVisits. Thanks again.

Nick


SELECT [Patient information].PatientID, [Patient
information].Patient_first_name, [Patient information].Patient_last_name,
tblVisits.DateOfVisit, tblVisits.VisitID, [Insurance information].[Carrier
name], tblPayments.DateOfPayment1, tblPayments.DateOfPayment2,
tblPayments.DateOfPayment3, tblPayments.[Check#1], tblPayments.[Check#2],
tblPayments.[Check#3], tblPayments.TotalPayed, tblPayments.CPT1PaymentTotal,
tblPayments.CPT1UnitsPaid1, tblPayments.CPT1UnitsPaid2,
tblPayments.CPT1UnitsPaid3, tblPayments.CPT1UnitPayment1,
tblPayments.CPT1UnitPayment2, tblPayments.CPT1UnitPayment3,
tblPayments.CPT2PaymentTotal, tblPayments.CPT2UnitsPaid1,
tblPayments.CPT2UnitsPaid2, tblPayments.CPT2UnitsPaid3,
tblPayments.CPT2UnitPayment1, tblPayments.CPT2UnitPayment2,
tblPayments.CPT2UnitPayment3, tblPayments.CPT3PaymentTotal,
tblPayments.CPT3UnitsPaid1, tblPayments.CPT3UnitsPaid2,
tblPayments.CPT3UnitsPaid3, tblPayments.CPT3UnitPayment1,
tblPayments.CPT3UnitPayment2, tblPayments.CPT3UnitPayment3,
tblPayments.CPT4PaymentTotal, tblPayments.CPT4UnitsPaid1,
tblPayments.CPT4UnitsPaid2, tblPayments.CPT4UnitsPaid3,
tblPayments.CPT4UnitPayment1, tblPayments.CPT4UnitPayment2,
tblPayments.CPT4UnitPayment3, tblPayments.CPT5PaymentTotal,
tblPayments.CPT5UnitsPaid1, tblPayments.CPT5UnitsPaid2,
tblPayments.CPT5UnitsPaid3, tblPayments.CPT5UnitPayment1,
tblPayments.CPT5UnitPayment2, tblPayments.CPT5UnitPayment3,
tblPayments.CPT6PaymentTotal, tblPayments.CPT6UnitsPaid1,
tblPayments.CPT6UnitsPaid2, tblPayments.CPT6UnitsPaid3,
tblPayments.CPT6UnitPayment1, tblPayments.CPT6UnitPayment2,
tblPayments.CPT6UnitPayment3, tblBillingInfo.CPT1, tblBillingInfo.CPT1Units,
tblBillingInfo.CPT1UnitCharge, tblBillingInfo.CPT2,
tblBillingInfo.CPT2Units, tblBillingInfo.CPT2UnitCharge,
tblBillingInfo.CPT3, tblBillingInfo.CPT3Units,
tblBillingInfo.CPT3UnitCharge, tblBillingInfo.CPT4,
tblBillingInfo.CPT4Units, tblBillingInfo.CPT4UnitCharge,
tblBillingInfo.CPT5, tblBillingInfo.CPT5Units,
tblBillingInfo.CPT5UnitCharge, tblBillingInfo.CPT6,
tblBillingInfo.CPT6Units, tblBillingInfo.CPT6UnitCharge
FROM (((([Patient information] LEFT JOIN [Injury information] ON [Patient
information].PatientID = [Injury information].PatientID) LEFT JOIN tblVisits
ON [Injury information].InjuryID = tblVisits.InjuryID) LEFT JOIN [Insurance
information] ON [Injury information].InjuryID = [Insurance
information].InjuryID) LEFT JOIN tblBillingInfo ON tblVisits.VisitID =
tblBillingInfo.VisitID) LEFT JOIN tblPayments ON tblVisits.VisitID =
tblPayments.VisitID
ORDER BY tblVisits.DateOfVisit DESC;

 

[Duane Hookom]

I thought this all involved only two tables at a time in the query. You have
at least 6 tables with all using LEFT JOINS. I have never used this many
tables in a query that I expect to edit. Is there reason why you have so
many tables in the query?

--
Duane Hookom
MS Access MVP

Hi Duane. This sql follows my having switched tblPayments back to 1:1 with
its primary, tblVisits. Thanks again.

Nick


SELECT [Patient information].PatientID, [Patient
information].Patient_first_name, [Patient information].Patient_last_name,
tblVisits.DateOfVisit, tblVisits.VisitID, [Insurance information].[Carrier
name], tblPayments.DateOfPayment1, tblPayments.DateOfPayment2,
tblPayments.DateOfPayment3, tblPayments.[Check#1], tblPayments.[Check#2],
tblPayments.[Check#3], tblPayments.TotalPayed, tblPayments.CPT1PaymentTotal,
tblPayments.CPT1UnitsPaid1, tblPayments.CPT1UnitsPaid2,
tblPayments.CPT1UnitsPaid3, tblPayments.CPT1UnitPayment1,
tblPayments.CPT1UnitPayment2, tblPayments.CPT1UnitPayment3,
tblPayments.CPT2PaymentTotal, tblPayments.CPT2UnitsPaid1,
tblPayments.CPT2UnitsPaid2, tblPayments.CPT2UnitsPaid3,
tblPayments.CPT2UnitPayment1, tblPayments.CPT2UnitPayment2,
tblPayments.CPT2UnitPayment3, tblPayments.CPT3PaymentTotal,
tblPayments.CPT3UnitsPaid1, tblPayments.CPT3UnitsPaid2,
tblPayments.CPT3UnitsPaid3, tblPayments.CPT3UnitPayment1,
tblPayments.CPT3UnitPayment2, tblPayments.CPT3UnitPayment3,
tblPayments.CPT4PaymentTotal, tblPayments.CPT4UnitsPaid1,
tblPayments.CPT4UnitsPaid2, tblPayments.CPT4UnitsPaid3,
tblPayments.CPT4UnitPayment1, tblPayments.CPT4UnitPayment2,
tblPayments.CPT4UnitPayment3, tblPayments.CPT5PaymentTotal,
tblPayments.CPT5UnitsPaid1, tblPayments.CPT5UnitsPaid2,
tblPayments.CPT5UnitsPaid3, tblPayments.CPT5UnitPayment1,
tblPayments.CPT5UnitPayment2, tblPayments.CPT5UnitPayment3,
tblPayments.CPT6PaymentTotal, tblPayments.CPT6UnitsPaid1,
tblPayments.CPT6UnitsPaid2, tblPayments.CPT6UnitsPaid3,
tblPayments.CPT6UnitPayment1, tblPayments.CPT6UnitPayment2,
tblPayments.CPT6UnitPayment3, tblBillingInfo.CPT1, tblBillingInfo.CPT1Units,
tblBillingInfo.CPT1UnitCharge, tblBillingInfo.CPT2,
tblBillingInfo.CPT2Units, tblBillingInfo.CPT2UnitCharge,
tblBillingInfo.CPT3, tblBillingInfo.CPT3Units,
tblBillingInfo.CPT3UnitCharge, tblBillingInfo.CPT4,
tblBillingInfo.CPT4Units, tblBillingInfo.CPT4UnitCharge,
tblBillingInfo.CPT5, tblBillingInfo.CPT5Units,
tblBillingInfo.CPT5UnitCharge, tblBillingInfo.CPT6,
tblBillingInfo.CPT6Units, tblBillingInfo.CPT6UnitCharge
FROM (((([Patient information] LEFT JOIN [Injury information] ON [Patient
information].PatientID = [Injury information].PatientID) LEFT JOIN tblVisits
ON [Injury information].InjuryID = tblVisits.InjuryID) LEFT JOIN [Insurance
information] ON [Injury information].InjuryID = [Insurance
information].InjuryID) LEFT JOIN tblBillingInfo ON tblVisits.VisitID =
tblBillingInfo.VisitID) LEFT JOIN tblPayments ON tblVisits.VisitID =
tblPayments.VisitID
ORDER BY tblVisits.DateOfVisit DESC;

I have seen your relationships but not your query's sql. This might help.

--
Duane Hookom
MS Access MVP


primary
key change
the tables

 

[david epsom dot com dot au]

FWIW, a 1-many-1 query is updatable in Jet 3.51:
a many-1-many query is not updatable.
And queries are only updatable if they are joined
on fields with indexes. (Matching indexes for
multi-field indexes).


(david)

 

[Nick Mirro]

Well its after the fact but this form is complex (cramped), and is next in
line to be subformed. That change would only remove 2 tables. For medical
billing, all this information needs to be visible at the same time (to make
collections easier).

http://home.comcast.net/~nickmirro/images/Temporary/frmPayments.gif

Nick

I thought this all involved only two tables at a time in the query. You have
at least 6 tables with all using LEFT JOINS. I have never used this many
tables in a query that I expect to edit. Is there reason why you have so
many tables in the query?

--
Duane Hookom
MS Access MVP

Hi Duane. This sql follows my having switched tblPayments back to 1:1 with
its primary, tblVisits. Thanks again.

Nick


SELECT [Patient information].PatientID, [Patient
information].Patient_first_name, [Patient information].Patient_last_name,
tblVisits.DateOfVisit, tblVisits.VisitID, [Insurance information].[Carrier
name], tblPayments.DateOfPayment1, tblPayments.DateOfPayment2,
tblPayments.DateOfPayment3, tblPayments.[Check#1], tblPayments.[Check#2],
tblPayments.[Check#3], tblPayments.TotalPayed, tblPayments.CPT1PaymentTotal,
tblPayments.CPT1UnitsPaid1, tblPayments.CPT1UnitsPaid2,
tblPayments.CPT1UnitsPaid3, tblPayments.CPT1UnitPayment1,
tblPayments.CPT1UnitPayment2, tblPayments.CPT1UnitPayment3,
tblPayments.CPT2PaymentTotal, tblPayments.CPT2UnitsPaid1,
tblPayments.CPT2UnitsPaid2, tblPayments.CPT2UnitsPaid3,
tblPayments.CPT2UnitPayment1, tblPayments.CPT2UnitPayment2,
tblPayments.CPT2UnitPayment3, tblPayments.CPT3PaymentTotal,
tblPayments.CPT3UnitsPaid1, tblPayments.CPT3UnitsPaid2,
tblPayments.CPT3UnitsPaid3, tblPayments.CPT3UnitPayment1,
tblPayments.CPT3UnitPayment2, tblPayments.CPT3UnitPayment3,
tblPayments.CPT4PaymentTotal, tblPayments.CPT4UnitsPaid1,
tblPayments.CPT4UnitsPaid2, tblPayments.CPT4UnitsPaid3,
tblPayments.CPT4UnitPayment1, tblPayments.CPT4UnitPayment2,
tblPayments.CPT4UnitPayment3, tblPayments.CPT5PaymentTotal,
tblPayments.CPT5UnitsPaid1, tblPayments.CPT5UnitsPaid2,
tblPayments.CPT5UnitsPaid3, tblPayments.CPT5UnitPayment1,
tblPayments.CPT5UnitPayment2, tblPayments.CPT5UnitPayment3,
tblPayments.CPT6PaymentTotal, tblPayments.CPT6UnitsPaid1,
tblPayments.CPT6UnitsPaid2, tblPayments.CPT6UnitsPaid3,
tblPayments.CPT6UnitPayment1, tblPayments.CPT6UnitPayment2,
tblPayments.CPT6UnitPayment3, tblBillingInfo.CPT1, tblBillingInfo.CPT1Units,
tblBillingInfo.CPT1UnitCharge, tblBillingInfo.CPT2,
tblBillingInfo.CPT2Units, tblBillingInfo.CPT2UnitCharge,
tblBillingInfo.CPT3, tblBillingInfo.CPT3Units,
tblBillingInfo.CPT3UnitCharge, tblBillingInfo.CPT4,
tblBillingInfo.CPT4Units, tblBillingInfo.CPT4UnitCharge,
tblBillingInfo.CPT5, tblBillingInfo.CPT5Units,
tblBillingInfo.CPT5UnitCharge, tblBillingInfo.CPT6,
tblBillingInfo.CPT6Units, tblBillingInfo.CPT6UnitCharge
FROM (((([Patient information] LEFT JOIN [Injury information] ON [Patient
information].PatientID = [Injury information].PatientID) LEFT JOIN tblVisits
ON [Injury information].InjuryID = tblVisits.InjuryID) LEFT JOIN [Insurance
information] ON [Injury information].InjuryID = [Insurance
information].InjuryID) LEFT JOIN tblBillingInfo ON tblVisits.VisitID =
tblBillingInfo.VisitID) LEFT JOIN tblPayments ON tblVisits.VisitID =
tblPayments.VisitID
ORDER BY tblVisits.DateOfVisit DESC;

I have seen your relationships but not your query's sql. This might help.

--
Duane Hookom
MS Access MVP


Thanks for the reply. I can use help with this. VisitID is the primary
key
in tblVisits, and in any linked table with a 1:1 relationship. When the
secondary table becomes many, its autonumber field becomes primary key.

The current normalization seems to be the most ergonomic from the form
perspective. db requires lots of controls per page, and many (such

as
cpt

codes per visit) need to be viewable all at once, intermingled with
calculating expressions. It is beyond my ability to go completely
normalized and display this ergonomically.

I understand that in medical billing, 1:1 is frequently used.

Nick


Are the VisitID fields primary keys in each table? BTW: I would change
the
table structures to make them one to many so the structure is more
normalized.

--
Duane Hookom
MS Access MVP


Thanks for the reply. I add an autonumber primary key and then
redefine
the
relationship. On the last error, it was non-updateable as a 1:1 with
its
immediate parent.

http://home.comcast.net/~nickmirro/images/Temporary/Relationships.pdf

The error happens between tblVisits and both tblPayments and
tblBillingInfo

Nick

p.s. there's a more detailed post in formscoding.


message
A 1 to 1 query is ALWAYS updatable. When you say you "change the
relationships of the queries two tables to 1 to 1", how are you
doing
this?

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm



The add record button in a query (datasheet view) periodically
dims,
rejecting any new data. The associate forms then no longer work.

I have to then change the relationships of the queries two tables
to
1
to
1,
or 1 to many depending on which it was last at. The logic would
always
imply 1:1 (patient visits to billing incidents), but I

cannot
get

this
to
hold.

What could make the query suddenly become non-updateable?

Nick

 

[Nick Mirro]

David,

Well this is updated Access 2002 editing a 2k db in XP. I assume I'm
running 4.something. Does this have any bearing?

 

[david epsom dot com dot au]

Jet 4 <probably> has the same rules for update as Jet 3.5.

I haven't followed what exactly your queries do.

I think, if I understand you correctly, that you were thinking
that perhaps some of your queries were changing 'by themselves'
between updatable and non-updatable. Another possibility is
that you are
making a query that was non-updatable into updatable,
simultaneously making an updatable query non-updatable.
for example, if you have
many-many
many-1
1-many
and you change index design to make the first query updatable
many-1
1-many
many-many
the third query becomes non-updatable.

I only mention this to add to your knowledge about what kinds
of queries are non-updatable....

(david)

 

[Duane Hookom]

I would recommend using subforms and normalizing your data. Also, some of
your tables might not be needed in the query if you use combo boxes.

--
Duane Hookom
MS Access MVP

Well its after the fact but this form is complex (cramped), and is next in
line to be subformed. That change would only remove 2 tables. For medical
billing, all this information needs to be visible at the same time (to make
collections easier).

http://home.comcast.net/~nickmirro/images/Temporary/frmPayments.gif

Nick

I thought this all involved only two tables at a time in the query. You have
at least 6 tables with all using LEFT JOINS. I have never used this many
tables in a query that I expect to edit. Is there reason why you have so
many tables in the query?

--
Duane Hookom
MS Access MVP

Hi Duane. This sql follows my having switched tblPayments back to 1:1 with
its primary, tblVisits. Thanks again.

Nick


SELECT [Patient information].PatientID, [Patient
information].Patient_first_name, [Patient information].Patient_last_name,
tblVisits.DateOfVisit, tblVisits.VisitID, [Insurance information].[Carrier
name], tblPayments.DateOfPayment1, tblPayments.DateOfPayment2,
tblPayments.DateOfPayment3, tblPayments.[Check#1], tblPayments.[Check#2],
tblPayments.[Check#3], tblPayments.TotalPayed, tblPayments.CPT1PaymentTotal,
tblPayments.CPT1UnitsPaid1, tblPayments.CPT1UnitsPaid2,
tblPayments.CPT1UnitsPaid3, tblPayments.CPT1UnitPayment1,
tblPayments.CPT1UnitPayment2, tblPayments.CPT1UnitPayment3,
tblPayments.CPT2PaymentTotal, tblPayments.CPT2UnitsPaid1,
tblPayments.CPT2UnitsPaid2, tblPayments.CPT2UnitsPaid3,
tblPayments.CPT2UnitPayment1, tblPayments.CPT2UnitPayment2,
tblPayments.CPT2UnitPayment3, tblPayments.CPT3PaymentTotal,
tblPayments.CPT3UnitsPaid1, tblPayments.CPT3UnitsPaid2,
tblPayments.CPT3UnitsPaid3, tblPayments.CPT3UnitPayment1,
tblPayments.CPT3UnitPayment2, tblPayments.CPT3UnitPayment3,
tblPayments.CPT4PaymentTotal, tblPayments.CPT4UnitsPaid1,
tblPayments.CPT4UnitsPaid2, tblPayments.CPT4UnitsPaid3,
tblPayments.CPT4UnitPayment1, tblPayments.CPT4UnitPayment2,
tblPayments.CPT4UnitPayment3, tblPayments.CPT5PaymentTotal,
tblPayments.CPT5UnitsPaid1, tblPayments.CPT5UnitsPaid2,
tblPayments.CPT5UnitsPaid3, tblPayments.CPT5UnitPayment1,
tblPayments.CPT5UnitPayment2, tblPayments.CPT5UnitPayment3,
tblPayments.CPT6PaymentTotal, tblPayments.CPT6UnitsPaid1,
tblPayments.CPT6UnitsPaid2, tblPayments.CPT6UnitsPaid3,
tblPayments.CPT6UnitPayment1, tblPayments.CPT6UnitPayment2,
tblPayments.CPT6UnitPayment3, tblBillingInfo.CPT1, tblBillingInfo.CPT1Units,
tblBillingInfo.CPT1UnitCharge, tblBillingInfo.CPT2,
tblBillingInfo.CPT2Units, tblBillingInfo.CPT2UnitCharge,
tblBillingInfo.CPT3, tblBillingInfo.CPT3Units,
tblBillingInfo.CPT3UnitCharge, tblBillingInfo.CPT4,
tblBillingInfo.CPT4Units, tblBillingInfo.CPT4UnitCharge,
tblBillingInfo.CPT5, tblBillingInfo.CPT5Units,
tblBillingInfo.CPT5UnitCharge, tblBillingInfo.CPT6,
tblBillingInfo.CPT6Units, tblBillingInfo.CPT6UnitCharge
FROM (((([Patient information] LEFT JOIN [Injury information] ON [Patient
information].PatientID = [Injury information].PatientID) LEFT JOIN tblVisits
ON [Injury information].InjuryID = tblVisits.InjuryID) LEFT JOIN [Insurance
information] ON [Injury information].InjuryID = [Insurance
information].InjuryID) LEFT JOIN tblBillingInfo ON tblVisits.VisitID =
tblBillingInfo.VisitID) LEFT JOIN tblPayments ON tblVisits.VisitID =
tblPayments.VisitID
ORDER BY tblVisits.DateOfVisit DESC;



I have seen your relationships but not your query's sql. This might help.

--
Duane Hookom
MS Access MVP


Thanks for the reply. I can use help with this. VisitID is the primary
key
in tblVisits, and in any linked table with a 1:1 relationship.

When
the

secondary table becomes many, its autonumber field becomes primary key.

The current normalization seems to be the most ergonomic from the form
perspective. db requires lots of controls per page, and many

(such

as

 

[Nick Mirro]

What causes table fields to become unindexed? Seems like this might have
been the cause.

Nick

 

[Nick Mirro]

Thank you for your time Duane. I find single control subforms are very
difficulty to work with, and I'd need quite a few. Referencing up and down
parent/children to get all calculations to work would also be very complex,
and I am not much of a vb coder as it is.

Removing the first two tables from this form's query is a 4+ hour job I just
can't do at this point. I just completed it for another form, and that took
over a week.

I was hoping that there was a logical error in my relationships.


What do you think of David's suggestion:

"I think, if I understand you correctly, that you were thinking
that perhaps some of your queries were changing 'by themselves'
between updatable and non-updatable. Another possibility is
that you are
making a query that was non-updatable into updatable,
simultaneously making an updatable query non-updatable.
for example, if you have
many-many
many-1
1-many
and you change index design to make the first query updatable
many-1
1-many
many-many
the third query becomes non-updatable."

http://home.comcast.net/~nickmirro/images/Temporary/Relationships.pdf

tblBillingInfo and tblPayments are again 1:1 with tblVisits

Nick

I would recommend using subforms and normalizing your data. Also, some of
your tables might not be needed in the query if you use combo boxes.

--
Duane Hookom
MS Access MVP

Well its after the fact but this form is complex (cramped), and is next in
line to be subformed. That change would only remove 2 tables. For medical
billing, all this information needs to be visible at the same time (to make
collections easier).

http://home.comcast.net/~nickmirro/images/Temporary/frmPayments.gif

Nick

I thought this all involved only two tables at a time in the query.

You
have

at least 6 tables with all using LEFT JOINS. I have never used this many
tables in a query that I expect to edit. Is there reason why you have so
many tables in the query?

--
Duane Hookom
MS Access MVP


Hi Duane. This sql follows my having switched tblPayments back to 1:1
with
its primary, tblVisits. Thanks again.

Nick


SELECT [Patient information].PatientID, [Patient
information].Patient_first_name, [Patient information].Patient_last_name,
tblVisits.DateOfVisit, tblVisits.VisitID, [Insurance information].[Carrier
name], tblPayments.DateOfPayment1, tblPayments.DateOfPayment2,
tblPayments.DateOfPayment3, tblPayments.[Check#1], tblPayments.[Check#2],
tblPayments.[Check#3], tblPayments.TotalPayed,
tblPayments.CPT1PaymentTotal,
tblPayments.CPT1UnitsPaid1, tblPayments.CPT1UnitsPaid2,
tblPayments.CPT1UnitsPaid3, tblPayments.CPT1UnitPayment1,
tblPayments.CPT1UnitPayment2, tblPayments.CPT1UnitPayment3,
tblPayments.CPT2PaymentTotal, tblPayments.CPT2UnitsPaid1,
tblPayments.CPT2UnitsPaid2, tblPayments.CPT2UnitsPaid3,
tblPayments.CPT2UnitPayment1, tblPayments.CPT2UnitPayment2,
tblPayments.CPT2UnitPayment3, tblPayments.CPT3PaymentTotal,
tblPayments.CPT3UnitsPaid1, tblPayments.CPT3UnitsPaid2,
tblPayments.CPT3UnitsPaid3, tblPayments.CPT3UnitPayment1,
tblPayments.CPT3UnitPayment2, tblPayments.CPT3UnitPayment3,
tblPayments.CPT4PaymentTotal, tblPayments.CPT4UnitsPaid1,
tblPayments.CPT4UnitsPaid2, tblPayments.CPT4UnitsPaid3,
tblPayments.CPT4UnitPayment1, tblPayments.CPT4UnitPayment2,
tblPayments.CPT4UnitPayment3, tblPayments.CPT5PaymentTotal,
tblPayments.CPT5UnitsPaid1, tblPayments.CPT5UnitsPaid2,
tblPayments.CPT5UnitsPaid3, tblPayments.CPT5UnitPayment1,
tblPayments.CPT5UnitPayment2, tblPayments.CPT5UnitPayment3,
tblPayments.CPT6PaymentTotal, tblPayments.CPT6UnitsPaid1,
tblPayments.CPT6UnitsPaid2, tblPayments.CPT6UnitsPaid3,
tblPayments.CPT6UnitPayment1, tblPayments.CPT6UnitPayment2,
tblPayments.CPT6UnitPayment3, tblBillingInfo.CPT1,
tblBillingInfo.CPT1Units,
tblBillingInfo.CPT1UnitCharge, tblBillingInfo.CPT2,
tblBillingInfo.CPT2Units, tblBillingInfo.CPT2UnitCharge,
tblBillingInfo.CPT3, tblBillingInfo.CPT3Units,
tblBillingInfo.CPT3UnitCharge, tblBillingInfo.CPT4,
tblBillingInfo.CPT4Units, tblBillingInfo.CPT4UnitCharge,
tblBillingInfo.CPT5, tblBillingInfo.CPT5Units,
tblBillingInfo.CPT5UnitCharge, tblBillingInfo.CPT6,
tblBillingInfo.CPT6Units, tblBillingInfo.CPT6UnitCharge
FROM (((([Patient information] LEFT JOIN [Injury information] ON [Patient
information].PatientID = [Injury information].PatientID) LEFT JOIN
tblVisits
ON [Injury information].InjuryID = tblVisits.InjuryID) LEFT JOIN
[Insurance
information] ON [Injury information].InjuryID = [Insurance
information].InjuryID) LEFT JOIN tblBillingInfo ON tblVisits.VisitID =
tblBillingInfo.VisitID) LEFT JOIN tblPayments ON tblVisits.VisitID =
tblPayments.VisitID
ORDER BY tblVisits.DateOfVisit DESC;



I have seen your relationships but not your query's sql. This might
help.

--
Duane Hookom
MS Access MVP


Thanks for the reply. I can use help with this. VisitID is the
primary
key
in tblVisits, and in any linked table with a 1:1 relationship. When
the
secondary table becomes many, its autonumber field becomes primary
key.

The current normalization seems to be the most ergonomic from

the
form

perspective. db requires lots of controls per page, and many

(such

 

[david epsom dot com dot au]

Only database corruption and repair causes loss of indexes,
and you get other messages when that happens. However, if
you have had corruption, you should import everything into
a new database. And make sure that you have all workstations
using the same (recent) version of Jet.

(david)

 

[Duane Hookom]

David is very knowledgeable. I would trust his direction. I would also not
spend any more time attempting to fix something that should be re-built.
Can't you use a combo box rather than a "single control subform". If I
understand you correctly, these are pretty much the same thing.

--
Duane Hookom
MS Access MVP

Thank you for your time Duane. I find single control subforms are very
difficulty to work with, and I'd need quite a few. Referencing up and down
parent/children to get all calculations to work would also be very complex,
and I am not much of a vb coder as it is.

Removing the first two tables from this form's query is a 4+ hour job I just
can't do at this point. I just completed it for another form, and that took
over a week.

I was hoping that there was a logical error in my relationships.


What do you think of David's suggestion:

"I think, if I understand you correctly, that you were thinking
that perhaps some of your queries were changing 'by themselves'
between updatable and non-updatable. Another possibility is
that you are
making a query that was non-updatable into updatable,
simultaneously making an updatable query non-updatable.
for example, if you have
many-many
many-1
1-many
and you change index design to make the first query updatable
many-1
1-many
many-many
the third query becomes non-updatable."

http://home.comcast.net/~nickmirro/images/Temporary/Relationships.pdf

tblBillingInfo and tblPayments are again 1:1 with tblVisits

Nick

I would recommend using subforms and normalizing your data. Also, some of
your tables might not be needed in the query if you use combo boxes.

next

in

have

so

many tables in the query?

--
Duane Hookom
MS Access MVP


Hi Duane. This sql follows my having switched tblPayments back to 1:1
with
its primary, tblVisits. Thanks again.

Nick


SELECT [Patient information].PatientID, [Patient
information].Patient_first_name, [Patient
information].Patient_last_name,
tblVisits.DateOfVisit, tblVisits.VisitID, [Insurance
information].[Carrier
name], tblPayments.DateOfPayment1, tblPayments.DateOfPayment2,
tblPayments.DateOfPayment3, tblPayments.[Check#1],
tblPayments.[Check#2],
tblPayments.[Check#3], tblPayments.TotalPayed,
tblPayments.CPT1PaymentTotal,
tblPayments.CPT1UnitsPaid1, tblPayments.CPT1UnitsPaid2,
tblPayments.CPT1UnitsPaid3, tblPayments.CPT1UnitPayment1,
tblPayments.CPT1UnitPayment2, tblPayments.CPT1UnitPayment3,
tblPayments.CPT2PaymentTotal, tblPayments.CPT2UnitsPaid1,
tblPayments.CPT2UnitsPaid2, tblPayments.CPT2UnitsPaid3,
tblPayments.CPT2UnitPayment1, tblPayments.CPT2UnitPayment2,
tblPayments.CPT2UnitPayment3, tblPayments.CPT3PaymentTotal,
tblPayments.CPT3UnitsPaid1, tblPayments.CPT3UnitsPaid2,
tblPayments.CPT3UnitsPaid3, tblPayments.CPT3UnitPayment1,
tblPayments.CPT3UnitPayment2, tblPayments.CPT3UnitPayment3,
tblPayments.CPT4PaymentTotal, tblPayments.CPT4UnitsPaid1,
tblPayments.CPT4UnitsPaid2, tblPayments.CPT4UnitsPaid3,
tblPayments.CPT4UnitPayment1, tblPayments.CPT4UnitPayment2,
tblPayments.CPT4UnitPayment3, tblPayments.CPT5PaymentTotal,
tblPayments.CPT5UnitsPaid1, tblPayments.CPT5UnitsPaid2,
tblPayments.CPT5UnitsPaid3, tblPayments.CPT5UnitPayment1,
tblPayments.CPT5UnitPayment2, tblPayments.CPT5UnitPayment3,
tblPayments.CPT6PaymentTotal, tblPayments.CPT6UnitsPaid1,
tblPayments.CPT6UnitsPaid2, tblPayments.CPT6UnitsPaid3,
tblPayments.CPT6UnitPayment1, tblPayments.CPT6UnitPayment2,
tblPayments.CPT6UnitPayment3, tblBillingInfo.CPT1,
tblBillingInfo.CPT1Units,
tblBillingInfo.CPT1UnitCharge, tblBillingInfo.CPT2,
tblBillingInfo.CPT2Units, tblBillingInfo.CPT2UnitCharge,
tblBillingInfo.CPT3, tblBillingInfo.CPT3Units,
tblBillingInfo.CPT3UnitCharge, tblBillingInfo.CPT4,
tblBillingInfo.CPT4Units, tblBillingInfo.CPT4UnitCharge,
tblBillingInfo.CPT5, tblBillingInfo.CPT5Units,
tblBillingInfo.CPT5UnitCharge, tblBillingInfo.CPT6,
tblBillingInfo.CPT6Units, tblBillingInfo.CPT6UnitCharge
FROM (((([Patient information] LEFT JOIN [Injury information] ON
[Patient
information].PatientID = [Injury information].PatientID) LEFT JOIN
tblVisits
ON [Injury information].InjuryID = tblVisits.InjuryID) LEFT JOIN
[Insurance
information] ON [Injury information].InjuryID = [Insurance
information].InjuryID) LEFT JOIN tblBillingInfo ON

tblVisits.VisitID