Information retrieval based on non-unique fields across separate tables

[dyt811]

I am pretty new with Microsoft Access database trying to retrieve information
from a very user-unfriendly database build by some genius who never uses the
database himself. Most people have trouble retrieving data and just did
manual copy and paste from Access to Excel (imagine the pain not mentioning
the amount of potential errors). That should give you some idea how
unfriendly it is. However, before I start wasting my time doing manual copy
and paste, I would like to see if there is another way out (which I believe
there always should be). The situation should not be difficult but I lack the
expertise to solve it so I humbly ask anyone out there to point me in the
right direction.

This is a large scale clinical patient database which is somewhat poorly
organized. Basically, three tables contain three separate types of
psychological evaluations done on the patients respectively. This entire
database actually contains more than just three evaluations like these but if
we can work out two or three tables, it should be easy to work out the rest.
Here is the detail breakdown on the situation:

1. Three independent tables: Table A, B, C. No relationship is established
whatsoever at this point (no one-to-many or one-to-one). All they did when
they made the table is to dump data obtained from clinical interview forms
directly into the tables. Type A interview results go into Table A and
whatever field that is relevant to Type A interview is contained in Table
Aetc

2. The primary key is pretty much useless (autonumbered based on order of
entry… never used/referred in other table or anywhere else. Its sole purpose
is to be a unique identifier in that table... I didn't design it this way.
They did...).

3. The only potential link between the tables is a field called “SUBJECTâ€
number. The "subject" field is a numerical field containing the patient ID in
each of the tables. However, it is not unique in all the tables. For example,
in Table A, there might be multiple entries with the same subject number but
different entries in the field “date†in Table A (representing a pre-
treatment, post-treatment evaluation etc). Similar situation applies in other
two tables.

4. These duplications in the “Subject†field is causing problems when I
create queries since they are not unique and (indeterminate relationship,
like many to many) produce some very random mostly useless duplicated entries
(as you can imagine)

5. It goes without say thing that there are “Subject†entries in one table
that may not exist in the other table...

6. I do have access to the tables to tweak the column settings etc.

So, the question is, can I somehow pull ALL the information about ONE subject
together in a more centralized manageable fashion from all three tables
(hopefully, into a table or query or the like, use combo box to deal with
duplicate info? Maybe?.). I mean, any kind of orginization/relationship is
better than what is currently there right? What would you recommend?

Thanks for your help, I have been banging my head eight hours all five days
on this over a month now. I looked into queries, subdatasheet, and lookup
fields but don’t really know enough to utilize them efficiently to fit my
need here. It really shouldn’t be this hard, should it? I would find it kind
of funny if in the end I have to accept the copy and paste solution lol.

In case you are wondering why I am doing all this, here is what I will
ultimately do when the whole thing in a more manageable fashion: I will
filter the patient cases based on certain criterias (eg. Table A, field "Dx")
and then characterize the subpopulation based on their info from Table A, B,
C... etc etc. One piece at a time for now. I think getting over this
relationship issue is a key step to begin, just like in life. XD

My sincere apologies if this has been previously asked. I tried to search but
I don't even have clue what keywords would describe my type of situation
appropriately.

Thank you very much for reading this detailed thread. Karma +1...

 

[John W. Vinson]

So, the question is, can I somehow pull ALL the information about ONE subject
together in a more centralized manageable fashion from all three tables
(hopefully, into a table or query or the like, use combo box to deal with
duplicate info? Maybe?.). I mean, any kind of orginization/relationship is
better than what is currently there right? What would you recommend?

Fortunately, the answer is "almost certainly".

What you need is a Subjects table with the Subject being unique within the
table. You can start by creating such a table, with the Subject field
(matching the size and datatype of the existing SUBJECT field in the three
tables. I take it this field is numeric? If it's Text, then use a text field
of the largest size found in any of the three. The table could have other
fields if you have other personal information about the subject, or if you
anticipate obtaining such information.

Create a UNION query in the SQL design window (the query grid isn't able to do
this):

SELECT [Subject] FROM TableA
UNION
SELECT [Subject] FROM TableB
UNION
SELECT [Subject] FROM TableC

This will string together all the SUBJECT values from the three tables... *and
eliminate all duplicates".

Save this query as uniSubjects.

Then create an Append query based on uniSubjects, appending into your new
Subjects table. Run it to populate the SUBJECTS table.

You will then be able to create one-to-many queries by joining SUBJECTS to
TableA, or to TableB, or to TableC; use a Form based on SUBJECTS with subforms
for the three data tables; create reports grouped by subject, etc.

There's hope!

 

[PieterLinden via AccessMonster.com]

Wow. I feel your pain. Been there, done that - and dealing with clinical
data - which I didn't understand totally (and they didn't understand
databases... enough to say somewhere that Codd's paper on database theory
"really didn't apply" to their database problems... OUCH.

 

[Steve]

Hello,

I specialize in fixing problems in existing databases. I would like to offer
to work with you to fix your database so you can easily use it as you want.
My fee to help you would be very reasonable. If you want help, contact me.

Steve
(e-mail address removed)

 

[John... Visio MVP]

I specialize in causing problems in existing databases. I would like to
offer to work with you to sdestroy your database so you can not easily use
it as you want. My fee to help you would not be very reasonable. If you
want help, ignore me.

Steve

Stevie is our own personal pet troll who is the only one who does not
understand the concept of FREE peer to peer support!
He offers questionable results at unreasonable prices.

These newsgroups are provided by Microsoft for FREE peer to peer support.
There are many highly qualified individuals who gladly help for free. Stevie
is not one of them, but he is the only one who just does not get the idea of
"FREE" support. He offers questionable results at unreasonable prices. If he
was any good, the "thousands" of people he claims to have helped would be
flooding him with work, but there appears to be a continuous drought and he
needs to constantly grovel for work.

Please do not feed the trolls.

John... Visio MVP

 

[Linq Adams via AccessMonster.com]

Actually John is being kind when he says that Steve "offers questionable
results!" There is often no question at all; his answers are simply incorrect!