Need help in setting up a database

[CuriousGeo]

I am in the process of making a database to track performance of resident doctors in training in a pathology lab. So far, I have a table that includes cases (basically: Accession number, date rec'd, specimen source, and diagnosis) They are to look at each case and give their diagnosis. To grade performance, a doctor will render a final diagnosis to complete the case. What I want to do is compare the final diagnosis to the resident diagnosis. From there we can grade "step" discrepancies. A step is 1 level above or below a diagnostic category. So if resident diagnosis is negative, and the final is atypical=1 step difference; negative vs. suspicious=2 steps, negative vs. malignant=3 steps and so on.

My question is in setting up the table (or a new table) to input the final doctor's diagnosis. Do I put that in the same table, or do a make a separate table and join them?

In reading about relationships and normal database structure, I'm questioning whether to put it in same table or different table. The common denominator is the Case (Accession #). Another question is, I have a look-up table containing the diagnosis. If I have 2 separate tables, can I use that same diagnosis table as the look-up for 2 separate tables that I want to compare?
Thank you for any input.

Resident Diagnosis	Case (common to both diagnosis)	Final (Official) Diagnosis
(Compare w/ Final)	Accession #	(Compare w/ Resident)
Unsatisfactory	Rec'd Date	Unsatisfactory
Negative	Specimen Source	Negative
Atypical		Atypical
Other type (Neoplastic)		Other type (Neoplastic)
Suspicious		Suspicious
Positive		Positive

 

[The_Doc_Man]

You have a case with an accession number. You have some things that relate to the case like date, source, and maybe other factors you didn't show here. That set of factors warrants a "case table" (though don't call it just a CASE because that is an Access keyword.) You then have a resident diagnosis and an official diagnosis. I would have a Diagnosis table (and that is NOT an Access keyword so is safe to use in table names). Then have a code within the diagnosis table where you have either the resident's ID or a code for Final. The diagnosis table would probably need your "Accession #" (but don't put # in a table or field name because # is a special symbol) as the way to related the diagnosis table to the case table.

You could write a query to list the diagnoses grouped by Accession number and see them compared (visually), one and then the other. You would have a table with the resident IDs and you could have one slot that corresponds to the FINAL diagnosis. As long as you don't have a doctor named "Official" this should be easy, just set aside one ID number for "Official" and you have all you need.

Hope I didn't confuse you with that description.

 

[neuroman9999]

you also said something about lookup tables. there is nothing wrong with those, however there have been a million access professionals all over the web warn against looking up data in a table control. I assume you're not doing that. I personally find lookup tables very helpful for data that is, what I call sometimes, ""ancillary"". in other words, it is data that isn't really related to anything else. or, it is, but rather off in its own little world for one reason or another.

 

[CuriousGeo]

you also said something about lookup tables. there is nothing wrong with those, however there have been a million access professionals all over the web warn against looking up data in a table control. I assume you're not doing that. I personally find lookup tables very helpful for data that is, what I call sometimes, ""ancillary"". in other words, it is data that isn't really related to anything else. or, it is, but rather off in its own little world for one reason or another.

No, the lookup is not in a table control, it is referenced by foreign key.

 

[CuriousGeo]

You have a case with an accession number. You have some things that relate to the case like date, source, and maybe other factors you didn't show here. That set of factors warrants a "case table" (though don't call it just a CASE because that is an Access keyword.) You then have a resident diagnosis and an official diagnosis. I would have a Diagnosis table (and that is NOT an Access keyword so is safe to use in table names). Then have a code within the diagnosis table where you have either the resident's ID or a code for Final. The diagnosis table would probably need your "Accession #" (but don't put # in a table or field name because # is a special symbol) as the way to related the diagnosis table to the case table.

You could write a query to list the diagnoses grouped by Accession number and see them compared (visually), one and then the other. You would have a table with the resident IDs and you could have one slot that corresponds to the FINAL diagnosis. As long as you don't have a doctor named "Official" this should be easy, just set aside one ID number for "Official" and you have all you need.

Hope I didn't confuse you with that description.

Thank you, I was thinking along that same design, having a "Case" table linked to separate Resident diagnosis and separate Doctor (Final/official diagnosis). But, as I re-read your description, I think you are saying have both resident and official diagnosis in one table?

 

[neuroman9999]

No, the lookup is not in a table control, it is referenced by foreign key.

I would have to see what you have to warn you if it is of any danger. what I mentioned has to do with looking up data in a field, for which the data in the lookup is coming from another table. If you're doing that inside a table object (at the table level), that's a no-no, and every expert in this community agrees.

 

[The_Doc_Man]

I think you are saying have both resident and official diagnosis in one table?

Yes. Other than one being "official" and the other "not official" there appears to be no significant difference between the structure of the diagnoses. Therefore, one might reasonably conclude that (at a higher level) they are the same despite differences in specific content. This is one of the factors one might consider when trying to normalize tables.

If you are not familiar with that term, you should consider some reading on the topic. In this forum you can search for "Normalization" (And SEARCH is at the top right, close to where your login name appears). On the general Internet, search for "Database Normalization" because there are other kinds of normalization. At least initially, if you do a web search, limit your first couple of articles to .EDU sites because the .COM sites might nice articles but they often have something to sell you that is proprietary.

 

[CuriousGeo]

I would have to see what you have to warn you if it is of any danger. what I mentioned has to do with looking up data in a field, for which the data in the lookup is coming from another table. If you're doing that inside a table object (at the table level), that's a no-no, and every expert in this community agrees.

I understand what you're explaining, I have seen the explanations of not using a lookup at the table level. I assure you, I did not do that.

 

[Pat Hartman]

You need a table to hold ResultsDefinitions: This will be used to calculate the score. I would not use an autonumber as the ID. I would use a number you assign so that items can be added or rearranged as necessary. Then you calculate the absolute value of the difference between the resident's assessment and the doctor's assessment. If + or - is relevant, then just do a-b and decide if it is + or - and how that affects the result.

1		Unsatisfactory
2		Negative
3		Atypical
4		Other type (Neoplastic)
5		Suspicious
6		Positive

 

[CuriousGeo]

Thanks Pat Hartman, this is an excellent idea I was trying to figure out! How to grade the differences between the dx types.

 

[Pat Hartman]

You're welcome