Design Question

[mfpoore]

Let me give the break-down.
multiple-lab situation, offer various diagnostic tests. We have panels that include tests for three different labs. So when I built the billing system, it is a couple of queries that go through repeated iif evaluations.

For example if panel1 is requested, then labA account number = x dollars, LabB account number = y dollars etc. Then for every test after panel it evaluates to add necassary amount into respective labs total (x+15, etc). Works great but... as we expand, the query gets longer until now if I add one more test it tells me "query too complex". I have built new queries on the original queries to keep it going, but I figure there is a better way to do it.

Any help would be greatly appreciated.

thanks,
mfpoore

 

[KenHigg]

When you say 'Panel', what exactly are you referring to?

Ken

 

[mfpoore]

panel

Just a combination of tests.

PanelA includes tests 1, 2 and 3. But the client has the option of choosing additional tests, so many different combinations are possible.

thanks,
mfpoore

 

[KenHigg]

Hum... A bit over my head - Maybe someone with health care / lab type db's will jump in....

Ken

 

[mfpoore]

Hum... A bit over my head - Maybe someone with health care / lab type db's will jump in....

Ken

I think I'm making it sound more complicated than it is. Really just need to know what I would do, if anything, to avoid a long series of iff statements that eventually get to the "query too complex" message.

Would a macro be restrictive in the same way? (Is there a limit to the number of conditions in your IF loop?)

thanks,
mfpoore

 

[KenHigg]

I'm thinking you should just somehow do a function.

Ken

 

[mfpoore]

function

Thanks, that will be the plan.

 

[Pat Hartman]

A user function is certainly better than complex IIf()s but both are inferior methods. Rather than creating code which will change everytime something new is added or a price changes, use a table to hold the prices. Then you can just join to the price table to obtain the current price. An example table might look like:

tblProduct
ProductID (autonumber primary key) - or whatever you use to uniquely identify each test
UnitPrice
AcctCode


Since prices change over time, you will want to copy the price from tblProduct at the time you do the billing and store it in the customer's record so you can produce historical reports with the correct price structure.

 

[The_Doc_Man]

First thing you need to know: Julius Caesar got it exactly right. "Divide and conquer." You are trying to make a humongous single query that will handle all sorts of tests and you are getting nowhere 'cause of string bloat. That puppy will be a nightmare to debug as time goes on, too.

For the others who don't understand the lingo, a panel is just a series of separate tests with some individual or combined test purpose. For example, a coroner might run a toxicology panel (series of tests) to determine if a foreign poison or narcotic substance was in a decedent's blood. An internal medicine MD might run an electrolytes panel (series of tests) to determine whether someone's blood chemistry was whacked. Sometimes the panel is a single test. The SMA-12 panel, one of the more common electrolyte panels, is 12 separate tests for 12 separate materials like sodium, potassium, etc. etc.

OK, there are ways to do this, but you need to start thinking parent/child tables and you need to understand many-to-many relationships.

Imagine you have a list of tests that you can perform. Break the panels down so that all elementary tests are separately identified. Now build a list of these elementary tests that comprise each panel.

Table: TestBasic
TBID: prime key, autonumber, a code for this particular elementary test, invariant across all panels that use the exact same test.
TBName: text, the name of the test written out in full.
TBAbbrev: text, any special abbriviations. (E.g. BUN = blood urea nitrogen)
TBCost: Currency or Single or Double, your choice... the cost of one test
TBSample: text or code for type of sample you need: Blood, urine, feces, skin, cerebro-spinal fluid, amniotic fluid, a pound of flesh, first-born child, an arm and a leg.... (Oh, wait, the last three should be in the COST field....)
TBSampleSize: how much of the sample is needed: 15 ml, 30 ml, 2 gallons
TBGoodLow: Lowest value considered GOOD.
TBGoodHigh: Highest value considered GOOD.

It is fair to have translation tables for the TBSample in case you need citrated blood, ordinary blood, acidulated blood, and to treat each one as different (which you should, of course). It is also fair to say that if you have two tests that require different amounts of the same sample, they are still not the same test and still have different data entries.

Table: TestPanel
TPID: prime key, autonumber, a code for this particular panel
TPName, TPAbbrev, etc etc.

The panels would be part of a drop-down so that you could choose a given panel just by selecting it from the list. The panels are just code names, really, because the REAL contents of each panel are in the underlying table that defines the many-to-many relationship among each test and each panel.

Table: PanelMembers
TBID: foreign key (into TestBasic), long
TPID: foreign key (into TestPanel), long
CanShare: Yes/No - can share results of test when another selected panel has the same TBID.

And you put one entry in PanelMembers for each elementary test that is part of your panel. So now, by grouping PanelMembers by TPID, you can get lists of all the tests that are part of a given panel. Or by grouping by TBID, you can find all the panels that use a given test.

Now, how do you track this? You need a "Subject" or "Patient" table (as a data "parent") and a child table of "Tests Ordered" - where you list a date, test ID (TBID, as obtained by the panels you selected). Date must be a part of this if you are dealing with living patients, since you might need to reschedule a given test after a few days of treatment. NOTE: The results of each test go in THIS table, not the TestBasic table.

The "tests ordered" table is another many-to-many table.

Table TestOrd
PatID, foreign key to Patient table
Date, date ordered
TBID, elementary test ordered as part of a panel
CanShare, yes/no, copy of field from panel table
LabResult, value that came back from the lab

Issues to decide would be whether, if two panels overlap at test xx, do you need to repeat the test twice (once for each panel) or can you share the results (take one sample, distribute the answer to two places)? If so, you build the TestOrd table but then remove duplicates for the combination of PatID, Date, and TBID. But if CanShare is NO, you cannot remove the test. On the other hand, if CanShare is NO on one test and YES on another, you can eliminate the test anyway.

It gets complex, but in a way it is much like an inventory system. Several cases of many-to-many floating around, but eventually you can get it done. I didn't say it would be easy, however.