If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Information retrieval based on non-unique fields across separate tables
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... |
#2
|
|||
|
|||
Information retrieval based on non-unique fields across separate tables
On Wed, 03 Mar 2010 05:05:57 GMT, "dyt811" u58535@uwe wrote:
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! -- John W. Vinson [MVP] |
#3
|
|||
|
|||
Information retrieval based on non-unique fields across separate tables
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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/201003/1 |
#4
|
|||
|
|||
Information retrieval based on non-unique fields across separate tables
|
#5
|
|||
|
|||
Information retrieval based on non-unique fields across separate tables
"Steve" wrote in message
... 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 |
#6
|
|||
|
|||
Information retrieval based on non-unique fields across separate tables
"Steve" schreef in bericht ... 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. -- Get lost $teve. Go away... far away.... Again... Get lost $teve. Go away... far away.... No-one wants you here... no-one needs you here... This newsgroup is meant for FREE help.. No-one wants you here... no-one needs you here... OP look at http://home.tiscali.nl/arracom/whoissteve.html (Website has been updated and has a new 'look'... we have passed 11.500 pageloads... it's a shame !!) Arno R |
#7
|
|||
|
|||
Information retrieval based on non-unique fields across separate tables
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! -- There's ALWAYS more than one way to skin a cat! Answers/posts based on Access 2000/2003 Message posted via http://www.accessmonster.com |
Thread Tools | |
Display Modes | |
|
|