Select query non-updateable

[Nick Mirro]

About weekly, I have to toggle between 1:1 and 1:many between 2 tables to
keep form query updateable. The relationship is between patient visits and
billing ids. They are always 1:1 as there is only ever one bill generated
per visit.

I don't care if the relationship is 1:many but it won't seem to hold.
Periodically, the form that used the query becomes not updateable, and I
notice that the add record button in the query "datasheet view" becomes
dimmed out. So I toggle the relationship for a temporary fix.

What might I try to resolve this?

Nick

 

[TC]

If there truly is only one bill generated per patient visit, then, why not
store the billing details directly in the patient visit table?

Nick, toggling relationships so you can make things updatable, is really not
the way to go. It's like seeing that your engine is smoking, & fixing that
by throwing a blanket over it when the smoke gets too unpleasant! The proper
fix is to get the table structures & relationships right.

What are the tables (presumable Patient, Visit, Bill, or similar), & what is
the primary key of each table?

HTH,
TC

 

[Nick Mirro]

Thanks for your help with this. Here are some answers.

If there truly is only one bill generated per patient visit, then, why not
store the billing details directly in the patient visit table?

Because there are a huge number of fields in the billing table, the payments
table and others secondary to tblVisits (most fields are not visible in the
report). This breaks them into logical chunks.

The proper fix is to get the table structures & relationships right.

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 - max 6) need to be viewable/editable all at once,
intermingled with
calculating expressions. It is beyond my ability to go completely
normalized, display this ergonomically and get all calculations to work.

I understand that in medical billing, 1:1 is frequently used to break data
up.

What are the tables (presumable Patient, Visit, Bill, or similar), & what is
the primary key of each table?

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

Nick

 

[TC]

Hi Nick

Well done on having a decently documented data model

The key thing that I notice about it, is the number of repeating groups. For
example:
o dxcode1-7 in injury information;
o location1-3 in tg test variables
o CPT things in tblpayments & tblbillinginfo
o refdate1-4, symptom1-4 etc.

This must be a reason for the "lots of fields", no?

To take tblBilling as an example, say that table just had VisitID (no CPT1
fields). You could store the CPT values in another table, keyed by visit ID
plus the CPT number (1, 2, 3, 4):

VisitID ( composite
CPTNo primary key )
Value
Units
UnitCharge

So now, instead of 1 table with 13 fields, you have 1 with *one* field, and
another with 5 fields - an immediate reduction of 50% in the # of fields.

Of course, it easy to suggest changes from a million miles away. But maybe
removing these repeated groups, would help.

HTH,
TC

 

[Nick Mirro]

I agree with the extra overhead associated with denormalized data, but I
think the tradeoff of having these limited fields easily accessible is worth
the price, at least at the terminal portions of the table relationships.

Here is one slightly cramped form using some of this denormalized data, to
the benefit of the user.

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

Back to the original issue (updateability), in a different post, I was given
this explanation. A little cryptic to a beginner like me. With an enormous
exertion, I might be able to glean some new insight from it. May I ask your
thoughts?

Nick

"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."

 

[TC]

I agree with the extra overhead associated with denormalized data, but I
think the tradeoff of having these limited fields easily accessible is worth
the price, at least at the terminal portions of the table relationships.

Here is one slightly cramped form using some of this denormalized data, to
the benefit of the user.

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

I agree it is a handsome form. But you could easily get the same ffect, even
if the CPT fields were in a seperate table. You do not need to put all the
fields in the same *table*, just because you want them all on the same
*screen*. For example, each of the 6 CPT areas could be a subform control
(ie. 6 subform controls). Each subform would link to the CPT records for the
visit in question (using the LinkMasterFields & LinkChildFields properties
of the subform controls). Each subform would use a filter to select the
sappropriate CPT number (so subform control #1 would show the record for CPT
1, subform control #s would show the record for CPT 2, & so on). If you
ensured that the subform controls did not have any visible borders, you
could achieve a visual appearance identical to what you have at present.

Back to the original issue (updateability), in a different post, I was given
this explanation. A little cryptic to a beginner like me. With an enormous
exertion, I might be able to glean some new insight from it. May I ask your
thoughts?

I think it is a little misleading. Updatability is really a function of
primary keys, and how you write the SQL. Indexes are really not the issue.
Here is an actual example that I explained to someone a few years ago. This
is a google message-ID. Go to groups.google.com, click Advanced grioups
search, & copy the following line (including the <>s) into the Message ID
box:

<01beb0d5$91081060$d8fa98cb@pentium>#1/1

HTH,
TC

 

[TC]

I'm confused.

I'm getting a bit confused myself!

Let's take a step back, & regroup. Here is where we are at present, from my
viewpoint.

(1) Your database is definitely not normalized. This is due to the presence
of repeating groups. (See
http://support.microsoft.com/support/kb/articles/Q100139.ASP)
Denormalization is sometimes appropriate. (It's a bit of a "black art"
deciding when it is required.) But there does not seem to be any good reason
for it, here. The denormalized nature of your db design, is a definite
reason why your db has "lots of fields".

(2) You do not need all the fields to be in one table, just to have them
appear on one screen. You design your tables based on the natural
relationships between the data that they are meant to be storing; *not* on
the basis of how you want the data to look on the screen.

(3) Queries are updatable, or not, depending on various factors including
primary keys, relationships, & how the SQL is written.

Let's focus on the non-updatable query thing. Show me the text of one of
your queries that is not updatable. (Choose the simplest and shortest one,
please!) Also show, for each table referenced by that query, what is the
*primary key* of that table. Also tell me whether *all* fields of the query
are non-updatable, or only certain ones.

Nick, I'm off in a short time for 24 hours, so I won't see your reply until
then.

Cheers,
TC

 

[Nick Mirro]

I'm having difficulty seeing how that case relates to this. First, I am not
very knowledgeable in sql, or any coding for that matter. I don't
understand the ON part of the statement.

It seems that in your example, there is confusion as to which record to
update, but between my tables, there is a 1:1 relationship. For every one
VisitID in tblVisits, there is only one possible record in tblPayments as it
uses the same primary key as its foreign key.

I'm confused.

If you see an easy solution, would you mind suggesting it. Thanks again for
taking time here.

Nick