On 2009 Jan 1, at 12:10, Gary Hunt wrote:
This application keeps track of psychiatric test results for children.
There are currently 20 some tests implemented with more possible
later.
Early on I thought about collecting common information in a single
table and
placing the unique information in their own tables for each test with
relations to the common table. However, my problem was that I
didn't know
yet what all the tests where and what would be common. Even if I
knew what
was common between all tests, the same question would come up again
between
different test class-types (mental, hearing, vision, etc.). Hence the
decision to treat each test as separate tables and solve the common
information problem(s) separately as the understanding of
commonality-groupings became apparent.
Does this help?
Geez, Gary, it sure looks like you've painted yourself into a corner
here.
I agree with Corn and Jonathan Fletcher that your best long-term
solution would be a database redesign. However, that doesn't help you
with your immediate problem. So, at the risk of starting you down a
track where you'll be spending time on this "union search" technique
rather than normalizing your data, here's a kludge you could try:
This assumes that each record in each table has a numeric identifying
code. If they don't currently have them, you can create them and fill
them with values using the "Replace" command. It doesn't matter if a
given record has its ID number duplicated in a different table, just
so it's unique within its OWN table. Let's assume these codes follow
the naming pattern "T1ID" for Table 1, "T2ID" for Table 2, etc.
Next create a brand-new table from which you will run all your
queries. This table, too, will have unique ID numbers assigned to
each record, and we'll call that field "T0ID". The trick here is to
make sure that EVERY ID value that's represented in ANY of your 20
tables has a counterpart in the search table.
Next, from your search table, create 20 relationships to the other
tables: T0ID to T1ID, T0ID to T2ID, etc.
Then, for EACH field you want to search on, create a counterpart in
the search table that will comprise a concatenation of the
corresponding fields from the other tables. For example, assume that
you're interested in querying the field "Vision" to see who all is
reported as "20/20". The field you'd create within your search table
would be a calculation (text result) that looks like this:
T0Vision =
T1::TableName & " | " & T1::TesteeName & " | " & T1::Vision &
"[paragraph symbol]" &
T2::TableName & " | " & T2::TesteeName & " | " & T2::Vision &
"[paragraph symbol]" &
T3::TableName & " | " & T3::TesteeName & " | " & T3::Vision &
"[paragraph symbol]" &
...
T20::TableName & " | " & T20::TesteeName & " | " & T20::Vision
You'd do your Find within "T0Vision", looking for "20/20". It would
take quite awhile, because "T0Vision" can't be indexed, due to its
use of related fields. And your results would be kind of jumbly,
depending on how much overlap of ID values you had in the related
tables. But you'd get a list of hits, which would be better than
nothing.
Sorry I can't come up with anything slicker. Good luck!
