Forms Conversion
- Thread starter danbl
- Start date
Well, I don't know what PAT at Amaass stands for so maybe you are familiar with that terminology. What I was getting at is to know which joins are one-to-one, one-to-many or many-to-many after referential integrity is checked so we can advise how best to layout the subforms.
I also have a suspicion that the DB isn't properly normalized. What do you think?
I also have a suspicion that the DB isn't properly normalized. What do you think?
OK, so what are are we trying to measure? Are there guidelines for the treatment of Venous Thromboembolism Prophylaxis or are you measure the procedure or the progress of the patient?
The Advanced_Directive and Perioperative Data have similar fields although I could not fanthom the Temperature _Monitoring.TQ fields.[FONT="]
[/FONT]
Simon[FONT="]
[/FONT]
The Advanced_Directive and Perioperative Data have similar fields although I could not fanthom the Temperature _Monitoring.TQ fields.[FONT="]
[/FONT]
Simon[FONT="]
[/FONT]
Okay .... twins could become an issue but very unlikely.
People with the same birthdate are not likely to have the same provider or be seen on the same day. It seems that I need a unique field to index but am unsure of how this would relate to the other three tables. (See screen shot). I am unable to EDIT RELATIONSHIPS, tick ENFORCE REFERENTIAL INTEGRITY as you can see.
With a few exceptions the fields in the tables are yes/no values (such as they either did or didn't receive treatment. "PAT from Amaass" may not apply to every person and infact only does about 50 percent of the time.
As for Zipping the screen shot .... when I do this directly I get a message the file size is to large.
People with the same birthdate are not likely to have the same provider or be seen on the same day. It seems that I need a unique field to index but am unsure of how this would relate to the other three tables. (See screen shot). I am unable to EDIT RELATIONSHIPS, tick ENFORCE REFERENTIAL INTEGRITY as you can see.
With a few exceptions the fields in the tables are yes/no values (such as they either did or didn't receive treatment. "PAT from Amaass" may not apply to every person and infact only does about 50 percent of the time.
As for Zipping the screen shot .... when I do this directly I get a message the file size is to large.
As suspected, your data isn't normalized. You need to restructure your table design, add some Primary Keys and Foreign Keys and create relationships. Here are some links that will help explain the process:
http://support.microsoft.com/kb/100139
http://r937.com/relational.html
http://support.microsoft.com/kb/100139
http://r937.com/relational.html
Whilst you're doing that drop off a stripped down version of your db with some data so we can have a look when we can.Could I suggest that you dummy up exactly what you are trying to achieve, just looking at the SQL it would seem that you need to do comparisons throughout the procedure. What happens if the patient doesn't go through to having a procedure despite the guidelines?
Simon
Simon
I have zipped a stripped version taking out the necessary identifiers.
Currently there are four data entry forms. These tables have evolved over time and now I want one form that can be used for data entry and one for data editing. The four tables have date of birth, date of service, and physician in common. There will be additional tables to be added later and at this point are not well defined yet as to the questions or data type to be used.
I have started on the articles and am still alittle confused as to how to incorporate the necessary change at this point. Maybe I can accomplish what I am trying to do now???
Currently there are four data entry forms. These tables have evolved over time and now I want one form that can be used for data entry and one for data editing. The four tables have date of birth, date of service, and physician in common. There will be additional tables to be added later and at this point are not well defined yet as to the questions or data type to be used.
I have started on the articles and am still alittle confused as to how to incorporate the necessary change at this point. Maybe I can accomplish what I am trying to do now???
Right, you've got too much going in your db that it will take a considerable amount of time to look at to be able to give pointers on normalizing.
So we can try to work with what you've already got (if it's possible). I will mention that tables like Anesthesia_Type should have an autonumber key or a three letter primary key. Linked up to the relevant table(s) in the Relationships view. It might be a good idea if you ask the question about normalizing your tables in the Theory and Practice of Database Design section of the forum.
Is this database supposed to be for statistical purposes or some sort of survey? Simply because all I see is Date Of Births. What happens if you have two or more patients with the same DOB? Or that doesn't matter since it's for statistical purposes?
So we can try to work with what you've already got (if it's possible). I will mention that tables like Anesthesia_Type should have an autonumber key or a three letter primary key. Linked up to the relevant table(s) in the Relationships view. It might be a good idea if you ask the question about normalizing your tables in the Theory and Practice of Database Design section of the forum.
Is this database supposed to be for statistical purposes or some sort of survey? Simply because all I see is Date Of Births. What happens if you have two or more patients with the same DOB? Or that doesn't matter since it's for statistical purposes?
The database is statistical in function at this point.
I can add primary keys to the other tables as you suggest and after checking out the other section of the forum maybe I will see how to establish the relationships. DOB works with Date fields and physician to compile the reports.
If I added an autonumber to the first table (Advance Directive) and then brought that into the other tables, would that help or hinder the fix??
I reallly appreciate your time and effort in guiding me to fix this problem!!
I can add primary keys to the other tables as you suggest and after checking out the other section of the forum maybe I will see how to establish the relationships. DOB works with Date fields and physician to compile the reports.
If I added an autonumber to the first table (Advance Directive) and then brought that into the other tables, would that help or hinder the fix??
I reallly appreciate your time and effort in guiding me to fix this problem!!
Adding a PK to a table won't solve your normalization problem. All the tables need to be looked at as a whole. Which was why I gave you those links to see what you can come up with since you understand your business logic than anyone else.
I think at this point, I will advise you look at getting your db normalized first. So create a new thread in that section of the forum. I believe once it's normalized you will begin to see how your form should be laid out. If you still don't get it, then post a reply here and we can work out the interface.
I think at this point, I will advise you look at getting your db normalized first. So create a new thread in that section of the forum. I believe once it's normalized you will begin to see how your form should be laid out. If you still don't get it, then post a reply here and we can work out the interface.
It would appear to me that there is a prescribed protocol and your application is trying to comply with this protocol. This may not necessarily an instance for normalisation as the application is tracking each phase peroperatively to post-operatively.
I've asked the question before but I will ask it again, if the patient has not reached or does not need an operative procedure that table may not be populated. To get a full overview tabs with subforms maybe the answer. This does not prevent allowing each stage to be entered on separately.
There seems to a Question and NA as well. Could you combine these using a Table with [Y] Yes [N] No [X] Not Applicable?
Simon
I've asked the question before but I will ask it again, if the patient has not reached or does not need an operative procedure that table may not be populated. To get a full overview tabs with subforms maybe the answer. This does not prevent allowing each stage to be entered on separately.
There seems to a Question and NA as well. Could you combine these using a Table with [Y] Yes [N] No [X] Not Applicable?
Simon
Simon
If a patient does not go to the OR then all data is discarded as no qualifing event occurred.
As to "There seems to a Question and NA", this would not be practicle as there are instances where the question is not applicable to a particular patient. While this may seem confusing it is actually well understood by staff who gather the data. We are looking to eliminate this senario in future track.
I have done some of the reading you suggested and will be making some changes. I would appreciate your review and comment.
Dan
If a patient does not go to the OR then all data is discarded as no qualifing event occurred.
As to "There seems to a Question and NA", this would not be practicle as there are instances where the question is not applicable to a particular patient. While this may seem confusing it is actually well understood by staff who gather the data. We are looking to eliminate this senario in future track.
I have done some of the reading you suggested and will be making some changes. I would appreciate your review and comment.
Dan
Ok here I am again. Please look at the relationships. When I create the primary and foreign keys I successfully get the data to align. Ideally theprimary key in the advance directive table should match with one the one in say the DVT table, but they do not. When I change the foreign key to number then nothing shows.
Also when I try to build one form for all data entry, I get the message which attached with the screen shot called "relationships11911.doc". I did set the index to no duplicates in the advance directive table.
Can you help me with this???
Also when I try to build one form for all data entry, I get the message which attached with the screen shot called "relationships11911.doc". I did set the index to no duplicates in the advance directive table.
Can you help me with this???
